INTERSECT and EXCEPT in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find common customers in both tables.

PostgreSQL

SELECT customer_id FROM orders_2023 [1] SELECT customer_id FROM orders_2024;

Drag options to blanks, or click blank then click option'

AINTERSECT

BUNION

CEXCEPT

DJOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION returns all unique rows from both tables, not just common ones.

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

The INTERSECT keyword returns rows common to both queries.

2fill in blank

medium

Complete the code to find customers who ordered in 2023 but not in 2024.

PostgreSQL

SELECT customer_id FROM orders_2023 [1] SELECT customer_id FROM orders_2024;

Drag options to blanks, or click blank then click option'

AEXCEPT

BINTERSECT

CUNION

DJOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTERSECT returns only common rows, not unique ones.

Using UNION combines all unique rows from both queries.

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

3fill in blank

hard

Fix the error in the query to find customers who ordered in 2024 but not in 2023.

PostgreSQL

SELECT customer_id FROM orders_2024 [1] SELECT customer_id FROM orders_2023;

Drag options to blanks, or click blank then click option'

AINTERSECT

BJOIN

CEXCEPT

DUNION ALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNION ALL returns all rows including duplicates.

Using INTERSECT returns only common rows.

To find customers in 2024 but not in 2023, use EXCEPT with 2024 first.

4fill in blank

hard

Fill both blanks to find customers who ordered in 2023 or 2024 but not both.

PostgreSQL

SELECT customer_id FROM orders_2023 [1] SELECT customer_id FROM orders_2024
EXCEPT
SELECT customer_id FROM orders_2023 [2] SELECT customer_id FROM orders_2024;

Drag options to blanks, or click blank then click option'

AUNION

BINTERSECT

CEXCEPT

DJOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXCEPT instead of UNION in the first blank returns only unique to 2023.

Using UNION instead of INTERSECT in the second blank does not exclude common customers.

This query finds customers in either year but excludes those in both by subtracting the INTERSECT from the UNION.

5fill in blank

hard

Fill all three blanks to find customers who ordered in 2023 but not in 2024, showing their names and IDs.

PostgreSQL

SELECT [1], customer_id FROM customers WHERE customer_id IN (
  SELECT customer_id FROM orders_2023 [2] SELECT customer_id FROM orders_2024
) [3] ORDER BY customer_id;

Drag options to blanks, or click blank then click option'

Acustomer_name

BEXCEPT

CLIMIT 10

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of LIMIT causes syntax errors at the end.

Using INTERSECT instead of EXCEPT returns common customers, not unique ones.

This query selects customer names and IDs for those who ordered in 2023 but not 2024, limiting results to 10.

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