INTERSECT for common rows in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL INTERSECT operator do?

It returns only the rows that appear in both SELECT query results, showing common rows between two datasets.

beginner

How is INTERSECT different from UNION in SQL?

INTERSECT returns only rows common to both queries, while UNION returns all unique rows from both queries combined.

intermediate

Can INTERSECT be used with queries that select different columns?

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

beginner

Write a simple SQL query using INTERSECT to find common employee IDs from two tables: Employees2023 and Employees2024.

SELECT EmployeeID FROM Employees2023 INTERSECT SELECT EmployeeID FROM Employees2024;

intermediate

What happens if there are duplicate rows in the results of the SELECT queries used with INTERSECT?

INTERSECT automatically removes duplicates and returns only distinct common rows.

What does the INTERSECT operator return in SQL?

A All rows from the first SELECT query

B Rows common to both SELECT queries

C All rows from both SELECT queries combined

D Rows unique to the first SELECT query

INTERSECT returns only the rows that appear in both SELECT query results.

Which condition must be true for two SELECT queries to be used with INTERSECT?

A They must select the same number of columns with compatible data types

B They must select different columns

C They must use different tables

D They must have WHERE clauses

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

If one SELECT query returns duplicate rows, how does INTERSECT handle them?

A Duplicates cause an error

B Duplicates are kept as is

C Duplicates are removed; only distinct common rows are returned

D Duplicates are returned only from the first query

INTERSECT removes duplicates and returns only distinct common rows.

Which SQL operator would you use to find rows that appear in both tables?

A INTERSECT

B UNION

C EXCEPT

D JOIN

INTERSECT returns rows common to both tables.

What is the result of this query? SELECT id FROM A INTERSECT SELECT id FROM B;

A IDs present only in table A

B All IDs from table A

C All IDs from table B

D IDs present in both tables A and B

The query returns IDs that exist in both tables A and B.

Explain in your own words what the INTERSECT operator does in SQL and when you might use it.

Think about comparing two lists and finding what they share.

Describe the difference between INTERSECT and UNION in SQL with an example scenario for each.

Consider when you want only shared data vs all data combined.