INTERSECT and EXCEPT in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL INTERSECT operator do?

The INTERSECT operator returns only the rows that appear in both query results. It shows common rows between two SELECT statements.

beginner

What is the purpose of the EXCEPT operator in SQL?

The EXCEPT operator returns rows from the first query that are not present in the second query. It shows what is unique to the first SELECT statement.

intermediate

Can INTERSECT and EXCEPT be used with different numbers of columns in the SELECT statements?

No. Both SELECT statements must have the same number of columns with compatible data types for INTERSECT and EXCEPT to work.

intermediate

How does INTERSECT differ from JOIN in SQL?

INTERSECT returns rows common to both queries as whole rows, while JOIN combines rows based on matching column values, often producing more columns.

beginner

If you want to find customers who bought product A but not product B, which operator would you use?

You would use the EXCEPT operator to find customers in the product A list who are not in the product B list.

What does the INTERSECT operator return?

A Rows unique to the first query

B All rows from the first query

C Rows common to both SELECT queries

D All rows from the second query

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

Which operator returns rows from the first query that are not in the second?

A EXCEPT

B INTERSECT

C UNION

D JOIN

EXCEPT returns rows from the first query that do not appear in the second query.

Can INTERSECT be used if the two SELECT statements have different numbers of columns?

A No, they must have the same number of columns

B Yes, always

C Only if columns have the same names

D Only if columns are numeric

Both SELECT statements must have the same number of columns with compatible data types for INTERSECT.

Which SQL operator would you use to find rows unique to the first query?

A INTERSECT

B EXCEPT

C UNION

D CROSS JOIN

EXCEPT returns rows from the first query that are not in the second query.

What is a key difference between INTERSECT and JOIN?

A INTERSECT combines columns, JOIN returns common rows

B Both do the same thing

C JOIN returns unique rows, INTERSECT returns all rows

D INTERSECT returns common rows, JOIN combines rows based on matching columns

INTERSECT returns rows common to both queries as whole rows, JOIN combines rows based on matching column values.

Explain how the INTERSECT operator works in SQL and give a simple example.

Think about finding common friends in two friend lists.

Describe the EXCEPT operator and when you would use it.

Imagine subtracting one list from another.