Why set operations are needed in SQL - Visual Breakdown

Concept Flow - Why set operations are needed

Two Tables with Data

↓

Apply Set Operation

↓

Resulting Table with Combined Data

↓

Use Result for Further Queries or Display

Set operations combine results from two tables into one result, allowing easy merging, comparison, or exclusion of data.

Execution Sample

SQL

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

This query combines city names from customers and suppliers into one list without duplicates.

Execution Table

Step	Action	Input Data	Operation	Output Data
1	Select cities from customers	customers table cities: [New York, Boston, Miami]	SELECT city FROM customers	[New York, Boston, Miami]
2	Select cities from suppliers	suppliers table cities: [Boston, Dallas, Miami]	SELECT city FROM suppliers	[Boston, Dallas, Miami]
3	Combine both city lists	[New York, Boston, Miami], [Boston, Dallas, Miami]	UNION removes duplicates	[New York, Boston, Miami, Dallas]
4	Return combined unique city list	[New York, Boston, Miami, Dallas]	Final output	[New York, Boston, Miami, Dallas]

💡 All cities combined with duplicates removed by UNION operation

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
customers_cities	empty	[New York, Boston, Miami]	[New York, Boston, Miami]	[New York, Boston, Miami]	[New York, Boston, Miami]
suppliers_cities	empty	empty	[Boston, Dallas, Miami]	[Boston, Dallas, Miami]	[Boston, Dallas, Miami]
combined_cities	empty	empty	empty	[New York, Boston, Miami, Dallas]	[New York, Boston, Miami, Dallas]

Key Moments - 3 Insights

Why does UNION remove duplicates in the combined result?

UNION is designed to return only distinct rows from both queries, as shown in step 3 of the execution_table where duplicate cities like Boston and Miami appear only once.

What happens if we use UNION ALL instead of UNION?

UNION ALL keeps all rows including duplicates. So if UNION ALL was used, the output would include repeated cities like Boston and Miami twice, unlike the distinct list in step 3.

Why do we need set operations instead of just joining tables?

Set operations combine rows vertically (adding rows), while joins combine tables horizontally (adding columns). Here, we want a single list of cities from two tables, so set operations are needed.

Visual Quiz - 3 Questions

Look at the execution_table, what is the combined city list after step 3?

A [New York, Boston, Miami, Dallas, Boston, Miami]

B [Boston, Dallas, Miami]

C [New York, Boston, Miami, Dallas]

D [New York, Boston, Miami]

💡 Hint

Check the Output Data column at step 3 in the execution_table

At which step does the UNION operation remove duplicates?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at the Operation column in the execution_table where UNION is applied

If we replaced UNION with UNION ALL, how would the final output change?

A Only cities from customers would appear

B Duplicates would be included in the final list

C Duplicates would still be removed

D Only cities from suppliers would appear

💡 Hint

Refer to the key_moments explanation about UNION ALL behavior

Concept Snapshot

Set operations combine results from two queries.
UNION merges rows and removes duplicates.
UNION ALL merges rows and keeps duplicates.
INTERSECT returns common rows.
EXCEPT returns rows in first query not in second.
Use set operations to combine or compare data lists.

Full Transcript

Set operations in SQL let you combine results from two queries into one list. For example, UNION takes city names from customers and suppliers tables and merges them into one list without duplicates. The process starts by selecting cities from each table separately, then combining them with UNION which removes repeated cities. This is different from joins, which combine columns side-by-side. Set operations add rows vertically. UNION ALL keeps duplicates, while UNION removes them. INTERSECT and EXCEPT help find common or unique rows between queries. These operations are useful when you want to merge or compare data from different tables easily.

Practice

1. Why do we use set operations like UNION and INTERSECT in SQL?

easy

A. To create new tables permanently

B. To delete rows based on conditions

C. To update values in a single table

D. To combine or compare rows from two or more tables easily

Solution

1. Step 1: Understand the purpose of set operations

   Set operations like UNION and INTERSECT are designed to combine or compare rows from multiple tables.

2. Step 2: Identify what set operations do not do

   They do not create tables, update, or delete rows; those are different SQL commands.

3. Final Answer:

   To combine or compare rows from two or more tables easily -> Option D

4. Quick Check:

   Set operations combine or compare data [OK]

Hint: Set operations combine or compare rows from tables [OK]

Common Mistakes:

• Confusing set operations with data modification commands
• Thinking UNION creates a new permanent table
• Assuming INTERSECT deletes rows

2. Which of the following is the correct syntax to combine two SELECT queries using UNION in SQL?

easy

A. UNION SELECT * FROM table1, SELECT * FROM table2;

B. SELECT * FROM table1 JOIN UNION SELECT * FROM table2;

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

D. SELECT * FROM table1 AND SELECT * FROM table2 UNION;

Solution

1. Step 1: Recall correct UNION syntax

   The correct syntax is to write one SELECT query, then UNION, then another SELECT query.

2. Step 2: Check each option for syntax errors

   SELECT * FROM table1 UNION SELECT * FROM table2; follows the correct syntax. Options A, B, and D have incorrect keywords or order.

3. Final Answer:

   SELECT * FROM table1 UNION SELECT * FROM table2; -> Option C

4. Quick Check:

   Correct UNION syntax = SELECT * FROM table1 UNION SELECT * FROM table2; [OK]

Hint: UNION joins two SELECT queries directly [OK]

Common Mistakes:

• Adding JOIN keyword with UNION
• Using commas instead of UNION
• Placing UNION at the end incorrectly

3. Given two tables:
Table A: {1, 2, 3}
Table B: {2, 3, 4}
What is the result of SELECT * FROM A INTERSECT SELECT * FROM B;?

medium

A. {2, 3}

B. {1, 2, 3}

C. {1, 4}

D. {1, 2, 3, 4}

Solution

1. Step 1: Understand INTERSECT operation

   INTERSECT returns only rows present in both tables.

2. Step 2: Find common elements in Table A and Table B

   Common elements are 2 and 3.

3. Final Answer:

   {2, 3} -> Option A

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT returns only common rows [OK]

Common Mistakes:

• Confusing INTERSECT with UNION
• Including all rows from both tables
• Mixing up EXCEPT with INTERSECT

4. You wrote this SQL query:
SELECT * FROM table1 UNION table2;
What is the error and how to fix it?

medium

A. Missing SELECT before table2; fix by adding SELECT * FROM table2

B. UNION cannot be used with tables; use JOIN instead

C. UNION requires parentheses around queries

D. No error; query runs fine

Solution

1. Step 1: Identify syntax error in UNION usage

   UNION requires two complete SELECT statements, but second query lacks SELECT.

2. Step 2: Correct the query syntax

   Add SELECT * FROM before table2 to fix the error.

3. Final Answer:

   Missing SELECT before table2; fix by adding SELECT * FROM table2 -> Option A

4. Quick Check:

   UNION needs two SELECTs [OK]

Hint: UNION needs two full SELECT queries [OK]

Common Mistakes:

• Omitting SELECT in second query
• Using UNION with tables directly
• Adding unnecessary parentheses

5. You have two customer lists:
List A: customers who bought product X
List B: customers who bought product Y
How do you find customers who bought either product X or Y but not both using set operations?

hard

A. SELECT * FROM A UNION SELECT * FROM B

B. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)

C. SELECT * FROM A INTERSECT SELECT * FROM B

D. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B)

Solution

1. Step 1: Understand the problem

   We want customers who bought product X or Y but not both (exclusive customers).

2. Step 2: Use EXCEPT and UNION to find exclusive customers

   Find customers in A but not in B, and customers in B but not in A, then combine them with UNION.

3. Step 3: Check other options

   SELECT * FROM A UNION SELECT * FROM B gives all customers who bought either product (including both). SELECT * FROM A INTERSECT SELECT * FROM B gives only those who bought both. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B) gives all customers who bought product X.

4. Final Answer:

   (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A) -> Option B

5. Quick Check:

   Exclusive customers = (A EXCEPT B) UNION (B EXCEPT A) [OK]

Hint: Use EXCEPT both ways, then UNION results [OK]

Common Mistakes:

• Using UNION alone includes both customers
• Using INTERSECT returns only common customers
• Confusing EXCEPT with INTERSECT