INTERSECT for common rows in SQL - Step-by-Step Execution

Concept Flow - INTERSECT for common rows

Start with Query1 Result Set

↓

Start with Query2 Result Set

↓

Compare Rows from Both Sets

↓

Keep Only Rows Present in Both

↓

Return Common Rows as Result

The INTERSECT operation compares two query results and returns only the rows that appear in both.

Execution Sample

SQL

SELECT name FROM Employees
INTERSECT
SELECT name FROM Managers;

This query returns the names that appear in both Employees and Managers tables.

Execution Table

Step	Query1 Result	Query2 Result	Comparison	Output Rows
1	Alice, Bob, Carol, Dave	Bob, Carol, Eve	Compare each name	Bob, Carol
2	No more rows to compare	No more rows to compare	Finished comparing	Final output: Bob, Carol

💡 All rows compared; only common rows Bob and Carol remain.

Variable Tracker

Variable	Start	After Step 1	Final
Query1 Result	Empty	Alice, Bob, Carol, Dave	Alice, Bob, Carol, Dave
Query2 Result	Empty	Bob, Carol, Eve	Bob, Carol, Eve
Output Rows	Empty	Bob, Carol	Bob, Carol

Key Moments - 2 Insights

Why does the output only include 'Bob' and 'Carol'?

Because INTERSECT returns only rows that appear in both Query1 and Query2 results, as shown in execution_table row 1.

What happens if a name appears only in one query result?

It is excluded from the output since INTERSECT keeps only common rows, demonstrated by 'Alice', 'Dave', and 'Eve' not appearing in the output.

Visual Quiz - 3 Questions

Look at the execution table, what is the output after step 1?

A Alice, Bob

B Carol, Eve

C Bob, Carol

D Alice, Dave

💡 Hint

Check the 'Output Rows' column in execution_table row 1.

At which step does the comparison finish?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Step' column and 'Comparison' description in execution_table.

If 'Eve' was also in Employees, how would the output change?

A Output would include Eve

B Output would exclude Eve

C Output would be empty

D Output would include only Eve

💡 Hint

INTERSECT returns rows common to both queries; see variable_tracker for output changes.

Concept Snapshot

INTERSECT returns rows common to two queries.
Syntax: SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;
Only rows in both results appear.
Duplicates removed automatically.
Useful to find shared data.

Full Transcript

The INTERSECT operation in SQL compares two query results and returns only the rows that appear in both. For example, if we select names from Employees and Managers tables, INTERSECT returns only the names present in both. The execution table shows the first query returns Alice, Bob, Carol, Dave; the second returns Bob, Carol, Eve. Comparing these, only Bob and Carol appear in both, so they are the output. Rows unique to one query are excluded. This process finishes after all rows are compared. If a name like Eve was also in Employees, it would appear in the output because it is common to both. INTERSECT is useful to find shared data between two sets.