INTERSECT for common rows in SQL - Deep Dive

Overview - INTERSECT for common rows

What is it?

INTERSECT is a SQL command that finds rows common to two or more query results. It returns only the rows that appear in every query involved. This helps you quickly see shared data between tables or queries without duplicates. It works like a filter that keeps only the matching rows.

Why it matters

Without INTERSECT, finding common rows between datasets would require complex joins or manual comparisons. This command simplifies the process, saving time and reducing errors. It helps in data analysis, reporting, and ensuring data consistency across sources. Without it, comparing datasets would be slower and more error-prone.

Where it fits

Before learning INTERSECT, you should understand basic SQL SELECT queries and how to combine results using UNION. After mastering INTERSECT, you can explore more advanced set operations like EXCEPT and complex joins. It fits into the broader topic of SQL set operations and data comparison.

Mental Model

Core Idea

INTERSECT returns only the rows that appear in all query results, showing their common ground.

Think of it like...

Imagine two friends each have a list of favorite movies. INTERSECT is like finding the movies both friends like, ignoring any movies only one friend chose.

┌───────────────┐   ┌───────────────┐
│   Query A     │   │   Query B     │
│  (Rows Set)   │   │  (Rows Set)   │
└──────┬────────┘   └──────┬────────┘
       │                   │
       │                   │
       │    ┌──────────────┴─────────────┐
       └────►       INTERSECT RESULT      │
            │ Rows common to both queries │
            └─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Basic SELECT queries review

Concept: Understanding how to write simple SELECT queries to retrieve data from tables.

A SELECT query asks the database to give you rows from a table. For example, SELECT * FROM Employees; returns all rows and columns from the Employees table. You can also select specific columns or filter rows with WHERE.

Result

You get a list of rows from the table you asked for.

Knowing how to write SELECT queries is the foundation for using INTERSECT because INTERSECT works on the results of SELECT queries.

2

Foundation

Understanding UNION for combining rows

Concept: Learning how UNION combines rows from two queries, including duplicates removal.

UNION takes the results of two SELECT queries and combines them into one list, removing duplicates. For example, SELECT Name FROM Employees UNION SELECT Name FROM Managers; gives all unique names from both tables.

Result

A combined list of unique rows from both queries.

Understanding UNION helps because INTERSECT is a similar set operation but focuses on common rows instead of all rows.

3

Intermediate

Using INTERSECT to find common rows

🤔Before reading on: do you think INTERSECT returns rows from the first query only, or only rows present in both queries? Commit to your answer.

Concept: Introducing INTERSECT to return only rows that appear in both query results.

INTERSECT takes two SELECT queries and returns only the rows that appear in both. For example: SELECT Name FROM Employees INTERSECT SELECT Name FROM Managers; This returns names that are in both Employees and Managers tables.

Result

A list of rows common to both queries, with duplicates removed.

Understanding that INTERSECT filters to shared rows helps you quickly find overlaps between datasets without manual comparison.

4

Intermediate

Rules for INTERSECT queries

🤔Before reading on: do you think the two queries in INTERSECT can select different columns or data types? Commit to your answer.

Concept: Learning the requirements for queries used with INTERSECT.

Both queries must select the same number of columns, and corresponding columns must have compatible data types. Column names do not have to match. For example, SELECT Name, Age FROM A INTERSECT SELECT EmployeeName, EmployeeAge FROM B; works if data types match.

Result

Queries run successfully only if these rules are followed; otherwise, errors occur.

Knowing these rules prevents errors and ensures INTERSECT works as expected.

5

Intermediate

Handling duplicates and NULLs in INTERSECT

Concept: Understanding how INTERSECT treats duplicate rows and NULL values.

INTERSECT removes duplicate rows in the final result, showing each common row once. NULL values are treated as equal if they appear in the same position in both queries. For example, if both queries have a row with NULL in a column, that row can appear in the result.

Result

A clean list of unique common rows, including those with NULLs matched.

Knowing how duplicates and NULLs behave helps avoid surprises in query results.

6

Advanced

Using INTERSECT with complex queries

🤔Before reading on: do you think INTERSECT works only with simple SELECT * queries or also with queries having WHERE, JOIN, and ORDER BY? Commit to your answer.

Concept: Applying INTERSECT to queries with filters, joins, and sorting.

You can use INTERSECT with any valid SELECT queries, including those with WHERE filters, JOINs, and ORDER BY clauses. For example: SELECT EmployeeID FROM Employees WHERE Department = 'Sales' INTERSECT SELECT EmployeeID FROM ProjectTeam WHERE Role = 'Lead'; This finds employees who are salespeople and project leads.

Result

The result shows common rows after applying all filters and joins in each query.

Understanding that INTERSECT works on final query results allows powerful data comparisons.

7

Expert

Performance considerations and query optimization

🤔Before reading on: do you think INTERSECT is always faster than JOINs for finding common rows? Commit to your answer.

Concept: Exploring how databases execute INTERSECT and when it is efficient or costly.

INTERSECT is implemented internally as a set operation, often using hashing or sorting to find common rows. For large datasets, performance depends on indexes and query complexity. Sometimes JOINs with EXISTS or INNER JOIN can be faster. Understanding execution plans helps optimize queries using INTERSECT.

Result

Better query performance by choosing the right method for finding common rows.

Knowing how INTERSECT works internally helps you write efficient queries and avoid slowdowns in production.

Under the Hood

INTERSECT works by executing both SELECT queries separately, then comparing their result sets to find rows present in both. The database engine often uses hashing or sorting algorithms to efficiently identify common rows. It removes duplicates from each result before comparison to ensure unique rows in the final output.

Why designed this way?

INTERSECT was designed to simplify finding common data without complex joins or manual filtering. Using set theory concepts, it provides a clear, declarative way to express intersection of datasets. Alternatives like joins are more flexible but can be more complex and less intuitive for this specific task.

┌───────────────┐       ┌───────────────┐
│   Query A     │       │   Query B     │
│  (Result Set) │       │  (Result Set) │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │                       │
       │   ┌───────────────────┴───────────────────┐
       └──►│  Hashing/Sorting to find common rows   │
           └───────────────────┬───────────────────┘
                               │
                       ┌───────┴───────┐
                       │ INTERSECT SET │
                       │  Unique rows  │
                       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does INTERSECT return all rows from the first query or only those also in the second? Commit to your answer.

Common Belief: INTERSECT returns all rows from the first query and adds rows from the second.

Reality: INTERSECT returns only rows that appear in both queries, excluding any unique to one.

Why it matters: Misunderstanding this leads to expecting more rows than returned, causing confusion and incorrect data interpretation.

Quick: Can INTERSECT be used if the two queries select different numbers of columns? Commit to yes or no.

Common Belief: You can INTERSECT queries with different numbers of columns as long as the data types match.

Reality: Both queries must select the same number of columns; otherwise, the database returns an error.

Why it matters: Trying to run INTERSECT on mismatched queries causes errors and wastes time debugging.

Quick: Does INTERSECT keep duplicate rows if they appear multiple times in both queries? Commit to yes or no.

Common Belief: INTERSECT keeps duplicates if they appear multiple times in both queries.

Reality: INTERSECT removes duplicates and returns each common row only once.

Why it matters: Expecting duplicates can lead to wrong assumptions about data volume and quality.

Quick: Is INTERSECT always faster than using JOINs to find common rows? Commit to your answer.

Common Belief: INTERSECT is always the fastest way to find common rows between tables.

Reality: Performance depends on data size, indexes, and query structure; sometimes JOINs are faster.

Why it matters: Blindly using INTERSECT without checking performance can cause slow queries in production.

Expert Zone

1

INTERSECT treats NULL values as equal only if they appear in the same column positions in both queries, which can affect results unexpectedly.

2

The order of columns matters for matching rows, even if column names differ; data types and positions must align exactly.

3

Some database systems optimize INTERSECT differently; knowing your DBMS's implementation can guide better query design.

When NOT to use

Avoid INTERSECT when you need to find rows present in one dataset but not another; use EXCEPT or LEFT JOIN instead. Also, for very large datasets with complex conditions, JOINs with EXISTS may perform better.

Production Patterns

In real systems, INTERSECT is used for data validation, finding overlapping user lists, or syncing datasets. It is often combined with filters and subqueries to narrow down comparisons efficiently.

Connections

Set Theory

INTERSECT directly implements the intersection operation from set theory.

Understanding set intersection in math clarifies how INTERSECT filters data to common elements.

Data Deduplication

INTERSECT inherently removes duplicates, similar to deduplication processes in data cleaning.

Knowing deduplication helps understand why INTERSECT results contain unique rows only.

Venn Diagrams

INTERSECT corresponds to the overlapping area in Venn diagrams representing shared data.

Visualizing data sets as circles helps grasp how INTERSECT finds common rows.

Common Pitfalls

#1 Using INTERSECT on queries with different column counts.

Wrong approach:

SELECT Name FROM Employees INTERSECT SELECT Name, Age FROM Managers;

Correct approach:

SELECT Name, Age FROM Employees INTERSECT SELECT Name, Age FROM Managers;

Root cause:

Misunderstanding that INTERSECT requires the same number of columns in both queries.

#2 Expecting INTERSECT to return duplicates if they appear multiple times.

Wrong approach:

SELECT Name FROM Employees INTERSECT SELECT Name FROM Managers; -- expecting duplicates

Correct approach:

SELECT DISTINCT Name FROM Employees INTERSECT SELECT DISTINCT Name FROM Managers; -- duplicates removed automatically

Root cause:

Not knowing INTERSECT automatically removes duplicates in the result.

#3 Using INTERSECT when needing rows unique to one table.

Wrong approach:

SELECT Name FROM Employees INTERSECT SELECT Name FROM Managers; -- to find employees not managers

Correct approach:

SELECT Name FROM Employees EXCEPT SELECT Name FROM Managers; -- finds employees only

Root cause:

Confusing INTERSECT with EXCEPT or other set operations.

Key Takeaways

INTERSECT returns only rows that appear in all involved queries, showing their common data.

Both queries must select the same number of columns with compatible data types for INTERSECT to work.

INTERSECT removes duplicates and treats NULLs as equal if they appear in the same positions.

It works with any valid SELECT queries, including those with filters and joins.

Understanding INTERSECT's internal set operation helps optimize queries and avoid common mistakes.

Practice

1. What does the SQL INTERSECT operator do?

easy

A. Combines rows from both queries including duplicates.

B. Returns all rows from the first SELECT query.

C. Returns all rows from the second SELECT query.

D. Returns only the rows common to both SELECT queries.

Solution

1. Step 1: Understand the purpose of INTERSECT

   The INTERSECT operator compares two SELECT queries and returns only the rows that appear in both results.

2. Step 2: Compare with other set operators

   Unlike UNION or UNION ALL, INTERSECT excludes rows not common to both queries.

3. Final Answer:

   Returns only the rows common to both SELECT queries. -> Option D

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT keeps only shared rows between two queries [OK]

Common Mistakes:

• Confusing INTERSECT with UNION which combines all rows
• Thinking INTERSECT returns rows from only one query
• Assuming INTERSECT includes duplicates

2. Which of the following is the correct syntax to find common rows between two tables TableA and TableB with the same columns id and name?

easy

A. SELECT id, name FROM TableA JOIN TableB ON TableA.id = TableB.id;

B. SELECT id, name FROM TableA UNION SELECT id, name FROM TableB;

C. SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB;

D. SELECT id, name FROM TableA WHERE id IN TableB;

Solution

1. Step 1: Identify correct INTERSECT syntax

   The INTERSECT operator is used between two SELECT statements with matching columns and types.

2. Step 2: Check each option

   SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; uses INTERSECT correctly. SELECT id, name FROM TableA UNION SELECT id, name FROM TableB; uses UNION which combines rows. SELECT id, name FROM TableA JOIN TableB ON TableA.id = TableB.id; uses JOIN which is different. SELECT id, name FROM TableA WHERE id IN TableB; has incorrect WHERE syntax.

3. Final Answer:

   SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; -> Option C

4. Quick Check:

   Correct INTERSECT syntax = SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; [OK]

Hint: Use INTERSECT between two SELECTs with matching columns [OK]

Common Mistakes:

• Using UNION instead of INTERSECT
• Trying to use WHERE with IN incorrectly
• Confusing JOIN with INTERSECT

3. Given two tables:
Employees1:
id | name
1 | Alice
2 | Bob
3 | Carol

Employees2:
id | name
2 | Bob
3 | Carol
4 | Dave

What is the result of this query?

SELECT id, name FROM Employees1 INTERSECT SELECT id, name FROM Employees2;

medium

A. Rows with Bob and Carol only

B. Rows with Alice, Bob, Carol, and Dave

C. Rows with Alice only

D. Rows with Dave only

Solution

1. Step 1: List rows from both tables

   Employees1 has (1, Alice), (2, Bob), (3, Carol). Employees2 has (2, Bob), (3, Carol), (4, Dave).

2. Step 2: Find common rows

   Common rows are those present in both: (2, Bob) and (3, Carol).

3. Final Answer:

   Rows with Bob and Carol only -> Option A

4. Quick Check:

   INTERSECT returns common rows = Bob, Carol [OK]

Hint: INTERSECT returns only rows present in both tables [OK]

Common Mistakes:

• Including rows unique to one table
• Confusing UNION with INTERSECT
• Ignoring column matching in rows

4. Consider this SQL query:

SELECT id, name FROM Customers INTERSECT SELECT id, name FROM Orders;

It returns an error. What is the most likely cause?

medium

A. The columns selected have different data types in Customers and Orders.

B. INTERSECT cannot be used with SELECT statements.

C. The tables Customers and Orders do not exist.

D. INTERSECT requires a WHERE clause.

Solution

1. Step 1: Understand INTERSECT requirements

   INTERSECT requires both SELECT queries to have the same number of columns with compatible data types.

2. Step 2: Analyze error causes

   If columns differ in type (e.g., id is integer in one table and string in another), the query errors. Other options are incorrect because INTERSECT is valid syntax, tables must exist to run, and WHERE is optional.

3. Final Answer:

   The columns selected have different data types in Customers and Orders. -> Option A

4. Quick Check:

   Matching column types required for INTERSECT [OK]

Hint: Check column types match for INTERSECT queries [OK]

Common Mistakes:

• Assuming INTERSECT needs WHERE clause
• Thinking INTERSECT is invalid syntax
• Ignoring data type mismatches

5. You have two tables:
ProductsA with columns product_id, name, price
ProductsB with columns product_id, name, price

You want to find products that exist in both tables with the same product_id and name, ignoring price differences.

Which query correctly uses INTERSECT to achieve this?

hard

A. SELECT product_id, name, price FROM ProductsA INTERSECT SELECT product_id, name, price FROM ProductsB;

B. SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB;

C. SELECT product_id FROM ProductsA INTERSECT SELECT product_id FROM ProductsB;

D. SELECT * FROM ProductsA INTERSECT SELECT * FROM ProductsB;

Solution

1. Step 1: Identify columns to compare

   You want to compare only product_id and name, ignoring price differences.

2. Step 2: Use INTERSECT on matching columns

   SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB; selects product_id and name from both tables and intersects them, returning only products common by those two columns.

3. Step 3: Analyze other options

   SELECT product_id, name, price FROM ProductsA INTERSECT SELECT product_id, name, price FROM ProductsB; includes price, so products with different prices won't match. SELECT product_id FROM ProductsA INTERSECT SELECT product_id FROM ProductsB; compares only product_id, ignoring name. SELECT * FROM ProductsA INTERSECT SELECT * FROM ProductsB; compares all columns, including price, which is not desired.

4. Final Answer:

   SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB; -> Option B

5. Quick Check:

   INTERSECT on selected columns matches desired fields [OK]

Hint: Select only columns to compare before INTERSECT [OK]

Common Mistakes:

• Including extra columns that cause mismatches
• Selecting * when columns differ
• Comparing only one column when two are needed