INTERSECT and EXCEPT in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INTERSECT query?

Given two tables Employees_A and Employees_B with the following data:

Employees_A
id | name
1 | Alice
2 | Bob
3 | Carol

Employees_B
id | name
2 | Bob
3 | Carol
4 | Dave

What will be the result of this query?

SELECT id, name FROM Employees_A
INTERSECT
SELECT id, name FROM Employees_B;

A

id | name
2  | Bob
3  | Carol

B

id | name
4  | Dave

C

id | name
1  | Alice

D

id | name
1  | Alice
2  | Bob
3  | Carol
4  | Dave

💡 Hint

INTERSECT returns only rows that appear in both queries.

The INTERSECT operator returns only the rows that exist in both Employees_A and Employees_B. Here, Bob and Carol are common in both tables.

❓ query_result

intermediate

What rows does this EXCEPT query return?

Given two tables Products_2023 and Products_2024 with data:

Products_2023
id | product
1 | Chair
2 | Table
3 | Lamp

Products_2024
id | product
2 | Table
3 | Lamp
4 | Sofa

What is the output of this query?

SELECT id, product FROM Products_2023
EXCEPT
SELECT id, product FROM Products_2024;

A

id | product
2  | Table
3  | Lamp

B

id | product
4  | Sofa

C

id | product
1  | Chair

D

id | product
1  | Chair
4  | Sofa

💡 Hint

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

The EXCEPT operator returns rows from Products_2023 that do not appear in Products_2024. Only 'Chair' is unique to Products_2023.

🧠 Conceptual

advanced

Which statement about INTERSECT and EXCEPT is true?

Consider the following statements about INTERSECT and EXCEPT in SQL:

A) INTERSECT returns all rows from the first query plus rows from the second.
B) EXCEPT returns rows that appear in both queries.
C) INTERSECT returns only rows common to both queries.
D) EXCEPT returns all rows from the second query that are not in the first.

Which statement is correct?

A EXCEPT returns rows that appear in both queries.

B INTERSECT returns only rows common to both queries.

C INTERSECT returns all rows from the first query plus rows from the second.

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

💡 Hint

Think about what INTERSECT and EXCEPT do with sets of rows.

INTERSECT returns only the rows that exist in both queries. EXCEPT returns rows from the first query that do not appear in the second.

📝 Syntax

advanced

Which query will cause a syntax error?

Which of the following SQL queries using INTERSECT or EXCEPT will cause a syntax error in PostgreSQL?

A SELECT id, name FROM table1 EXCEPT SELECT id FROM table2;

B SELECT * FROM table1 EXCEPT SELECT * FROM table2;

C SELECT id FROM table1 INTERSECT SELECT id FROM table2;

D SELECT id FROM table1 INTERSECT SELECT id FROM table2 WHERE id > 10;

💡 Hint

Check if the number and order of columns match in both queries.

Option A selects two columns in the first query but only one column in the second, causing a syntax error because INTERSECT and EXCEPT require the same number of columns with compatible types.

❓ optimization

expert

How to optimize this EXCEPT query for large tables?

You have two large tables, Orders_2023 and Orders_2024, both with millions of rows and the same schema.

You want to find orders in 2023 that are not in 2024 using:

SELECT * FROM Orders_2023 EXCEPT SELECT * FROM Orders_2024;

Which optimization will most improve performance?

A Add indexes on all columns used in the SELECT * for both tables.

B Rewrite the query using LEFT JOIN and IS NULL to find missing rows.

C Use EXCEPT ALL instead of EXCEPT to avoid duplicate elimination.

D Increase the work_mem setting in PostgreSQL to allow more memory for sorting.

💡 Hint

Think about how EXCEPT works internally with sorting and memory.

EXCEPT requires sorting or hashing to remove duplicates. Increasing work_mem allows PostgreSQL to perform these operations in memory, reducing disk I/O and improving performance.

Practice

1. What does the INTERSECT operator do in PostgreSQL?

easy

A. Returns rows only in the second query

B. Returns all rows from the first query

C. Combines all rows from both queries without duplicates

D. Returns rows common to both queries

Solution

1. Step 1: Understand INTERSECT purpose

   INTERSECT returns only rows that appear in both query results.

2. Step 2: Compare with other set operations

   Unlike UNION or EXCEPT, INTERSECT finds common rows, not all or unique rows.

3. Final Answer:

   Returns rows common to both queries -> Option D

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT = find common rows between two queries [OK]

Common Mistakes:

• Confusing INTERSECT with UNION
• Thinking INTERSECT returns all rows from first query
• Mixing INTERSECT with EXCEPT

2. Which of the following is the correct syntax to find rows in table1 but not in table2 using EXCEPT?

easy

A. SELECT * FROM table1 INTERSECT SELECT * FROM table2;

B. SELECT * FROM table1 EXCEPT SELECT * FROM table2;

C. SELECT * FROM table1 UNION EXCEPT SELECT * FROM table2;

D. SELECT * FROM table1 EXCEPT ALL SELECT * FROM table2;

Solution

1. Step 1: Identify EXCEPT syntax

   The correct syntax is: SELECT ... EXCEPT SELECT ... to get rows in first query not in second.

2. Step 2: Check options for correct usage

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; matches correct syntax; others misuse INTERSECT, UNION, or EXCEPT ALL (which is valid but different).

3. Final Answer:

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; -> Option B

4. Quick Check:

   EXCEPT syntax = SELECT ... EXCEPT SELECT ... [OK]

Hint: Use EXCEPT between two SELECTs to find rows only in first [OK]

Common Mistakes:

• Using INTERSECT instead of EXCEPT
• Adding UNION incorrectly
• Using EXCEPT ALL when not needed

3. Given these tables:
table_a:
id
1
2
3

table_b:
id
2
3
4

What is the result of:
SELECT id FROM table_a INTERSECT SELECT id FROM table_b;

medium

A. [1, 2, 3]

B. [1]

C. [2, 3]

D. [4]

Solution

1. Step 1: List rows from both tables

   table_a has ids 1, 2, 3; table_b has ids 2, 3, 4.

2. Step 2: Find common ids using INTERSECT

   Common ids are 2 and 3 only.

3. Final Answer:

   [2, 3] -> Option C

4. Quick Check:

   INTERSECT = common rows = [2, 3] [OK]

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

Common Mistakes:

• Including ids only in one table
• Confusing INTERSECT with UNION
• Expecting all ids combined

4. What is wrong with this query?

SELECT id, name FROM table1 EXCEPT SELECT id FROM table2;

medium

A. The number of columns in both SELECTs differ

B. EXCEPT cannot be used with SELECT statements

C. The query is missing a WHERE clause

D. The table names are invalid

Solution

1. Step 1: Check column counts in both SELECTs

   First SELECT returns two columns (id, name), second returns one column (id).

2. Step 2: Understand EXCEPT requirements

   EXCEPT requires both SELECTs to have the same number and compatible types of columns.

3. Final Answer:

   The number of columns in both SELECTs differ -> Option A

4. Quick Check:

   EXCEPT needs equal columns [OK]

Hint: EXCEPT requires same number of columns in both SELECTs [OK]

Common Mistakes:

• Ignoring column count mismatch
• Thinking EXCEPT needs WHERE clause
• Assuming table names cause error

5. You have two tables:
employees with columns id, name
contractors with columns id, name
You want to find people who are either employees or contractors but not both.
Which query correctly returns this list?

hard

A. SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees;

B. SELECT id, name FROM employees INTERSECT SELECT id, name FROM contractors;

C. SELECT id, name FROM employees UNION SELECT id, name FROM contractors;

D. SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors;

Solution

1. Step 1: Understand the requirement

   We want people in employees or contractors but not in both (symmetric difference).

2. Step 2: Use EXCEPT and UNION to find symmetric difference

   EXCEPT finds those in one table but not the other; UNION combines both exclusive sets.

3. Step 3: Analyze options

   SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees; correctly combines EXCEPT in both directions with UNION to get symmetric difference.

4. Final Answer:

   SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees; -> Option A

5. Quick Check:

   Symmetric difference = EXCEPT + UNION [OK]

Hint: Symmetric difference = (A EXCEPT B) UNION (B EXCEPT A) [OK]

Common Mistakes:

• Using only INTERSECT or UNION
• Using EXCEPT one way only
• Confusing symmetric difference with intersection