Why set operations are needed in SQL - See It in Action

Understanding Why Set Operations Are Needed in SQL

📖 Scenario: You work at a bookstore that has two separate tables: one for books currently in stock and another for books on order. You want to find combined lists of books for different purposes.

🎯 Goal: Build SQL queries using set operations to combine and compare book lists from two tables.

📋 What You'll Learn

Create two tables named in_stock and on_order with book titles.

Add a configuration variable to limit results to books starting with the letter 'A'.

Use the UNION operation to combine book lists without duplicates.

Use the INTERSECT operation to find books present in both tables.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses often have data in separate tables and need to combine or compare lists, like current stock and incoming orders.

💼 Career

Understanding set operations in SQL is essential for data analysts and database developers to write efficient queries that merge or compare data from different sources.

1

Create the in_stock and on_order tables

Write SQL statements to create two tables called in_stock and on_order. Each table should have a single column title of type VARCHAR(100). Insert these exact book titles into in_stock: 'Alice in Wonderland', 'Brave New World', 'Catch-22'. Insert these exact book titles into on_order: 'Animal Farm', 'Brave New World', 'Don Quixote'.

SQL

-- Create tables and insert data for in_stock and on_order
-- Your code here

Hint

Use CREATE TABLE to make tables and INSERT INTO to add rows.

2

Add a filter for book titles starting with 'A'

Create a variable or placeholder named filter_letter and set it to the string 'A'. This will be used to filter book titles starting with this letter in later queries.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Set filter_letter to 'A'
-- Your code here

Hint

Use a variable or session variable to hold the filter letter.

3

Use UNION to combine book lists without duplicates

Write a SQL query that selects title from in_stock where title starts with the value in @filter_letter. Use UNION to combine this with a selection of title from on_order where title also starts with @filter_letter. This query should return all unique book titles starting with 'A' from both tables.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Configuration variable for filtering
SET @filter_letter = 'A';

-- Query using UNION to combine titles starting with @filter_letter
-- Your code here

Hint

Use LIKE CONCAT(@filter_letter, '%') to filter titles starting with the letter.

4

Use INTERSECT to find books in both tables

Write a SQL query that selects title from in_stock where title starts with @filter_letter and use INTERSECT to find titles also present in on_order starting with @filter_letter. This query returns book titles starting with 'A' that are in both tables.

SQL

CREATE TABLE in_stock (title VARCHAR(100));
CREATE TABLE on_order (title VARCHAR(100));

INSERT INTO in_stock (title) VALUES
('Alice in Wonderland'),
('Brave New World'),
('Catch-22');

INSERT INTO on_order (title) VALUES
('Animal Farm'),
('Brave New World'),
('Don Quixote');

-- Configuration variable for filtering
SET @filter_letter = 'A';

SELECT title FROM in_stock WHERE title LIKE CONCAT(@filter_letter, '%')
UNION
SELECT title FROM on_order WHERE title LIKE CONCAT(@filter_letter, '%');

-- Query using INTERSECT to find common titles starting with @filter_letter
-- Your code here

Hint

Use INTERSECT to find common rows between two queries.

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