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.