Union, intersection, difference in DBMS Theory - Mini Project: Build & Apply

Union, Intersection, and Difference in SQL

📖 Scenario: You work in a company database where you have two tables: Employees_USA and Employees_Canada. Each table lists employees working in that country.You want to learn how to combine and compare these tables using SQL operations: union, intersection, and difference.

🎯 Goal: Build SQL queries to find:All employees working in either USA or Canada (union)Employees working in both USA and Canada (intersection)Employees working in USA but not in Canada (difference)

📋 What You'll Learn

Create two tables Employees_USA and Employees_Canada with exact employee names

Write a SQL query using UNION to combine all employees from both tables

Write a SQL query using INTERSECT to find employees common to both tables

Write a SQL query using EXCEPT to find employees in USA but not in Canada

💡 Why This Matters

🌍 Real World

Combining and comparing data from different sources or departments is common in business databases.

💼 Career

Knowing how to use UNION, INTERSECT, and EXCEPT helps database analysts and developers write efficient queries for data analysis and reporting.

1

Create the Employees Tables

Create two tables called Employees_USA and Employees_Canada with a single column EmployeeName of type VARCHAR. Insert these exact names into Employees_USA: 'Alice', 'Bob', 'Charlie'. Insert these exact names into Employees_Canada: 'Bob', 'Diana', 'Eve'.

DBMS Theory

-- Create Employees_USA table and insert data
-- Create Employees_Canada table and insert data

Hint

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

2

Write the UNION Query

Write a SQL query called AllEmployees that selects EmployeeName from Employees_USA and Employees_Canada combined using UNION to get all unique employee names from both tables.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

-- Write a UNION query to get all unique employees
-- Your code here

Hint

Use UNION between two SELECT statements to combine unique rows.

3

Write the INTERSECT Query

Write a SQL query that selects EmployeeName from Employees_USA and Employees_Canada using INTERSECT to find employees who work in both USA and Canada.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

SELECT EmployeeName FROM Employees_USA
UNION
SELECT EmployeeName FROM Employees_Canada;

-- Write an INTERSECT query to find employees in both tables
-- Your code here

Hint

Use INTERSECT between two SELECT statements to find common rows.

4

Write the EXCEPT Query

Write a SQL query that selects EmployeeName from Employees_USA but excludes those also in Employees_Canada using EXCEPT to find employees who work only in USA.

DBMS Theory

CREATE TABLE Employees_USA (EmployeeName VARCHAR(50));
INSERT INTO Employees_USA (EmployeeName) VALUES ('Alice'), ('Bob'), ('Charlie');

CREATE TABLE Employees_Canada (EmployeeName VARCHAR(50));
INSERT INTO Employees_Canada (EmployeeName) VALUES ('Bob'), ('Diana'), ('Eve');

SELECT EmployeeName FROM Employees_USA
UNION
SELECT EmployeeName FROM Employees_Canada;

SELECT EmployeeName FROM Employees_USA
INTERSECT
SELECT EmployeeName FROM Employees_Canada;

-- Write an EXCEPT query to find employees only in USA
-- Your code here

Hint

Use EXCEPT to find rows in the first query that are not in the second.

Practice

1. Which of the following best describes the union operation in databases?

easy

A. Finds only the common records between two tables

B. Combines all unique records from two tables

C. Returns records from the first table that are not in the second

D. Deletes duplicate records from a table

Solution

1. Step 1: Understand the union operation

   The union operation combines all unique records from two sets or tables, removing duplicates.

2. Step 2: Compare with other operations

   Intersection finds common records, difference finds records in one set but not the other, and deletion removes records.

3. Final Answer:

   Combines all unique records from two tables -> Option B

4. Quick Check:

   Union = Combine unique records [OK]

Hint: Union = all unique items combined [OK]

Common Mistakes:

• Confusing union with intersection
• Thinking union includes duplicates
• Mixing difference with union

2. Which SQL keyword is used to find common records between two tables?

easy

A. UNION

B. DIFFERENCE

C. JOIN

D. INTERSECT

Solution

1. Step 1: Identify the keyword for common records

   The SQL keyword INTERSECT returns only the records common to both tables.

2. Step 2: Differentiate from other keywords

   UNION combines unique records, DIFFERENCE is not a standard SQL keyword, and JOIN combines rows based on a condition.

3. Final Answer:

   INTERSECT -> Option D

4. Quick Check:

   Common records = INTERSECT [OK]

Hint: Common records use INTERSECT keyword [OK]

Common Mistakes:

• Using UNION instead of INTERSECT
• Confusing JOIN with INTERSECT
• Assuming DIFFERENCE is a valid SQL keyword

3. Given two sets A = {1, 2, 3, 4} and B = {3, 4, 5, 6}, what is the result of A INTERSECT B?

medium

A. {3, 4}

B. {1, 2, 3, 4, 5, 6}

C. {1, 2}

D. {5, 6}

Solution

1. Step 1: Identify common elements in A and B

   The common elements between A and B are 3 and 4.

2. Step 2: Understand INTERSECT operation

   INTERSECT returns only the common elements, so the result is {3, 4}.

3. Final Answer:

   {3, 4} -> Option A

4. Quick Check:

   Intersection = common elements [OK]

Hint: Intersection = only common items [OK]

Common Mistakes:

• Listing all elements instead of common ones
• Confusing intersection with union
• Including elements only in one set

4. Consider the SQL query:
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2;
What is the purpose of the EXCEPT keyword?

medium

A. Returns records in Table1 but not in Table2

B. Returns records in Table2 but not in Table1

C. Returns records common to both tables

D. Returns all records from both tables

Solution

1. Step 1: Understand the EXCEPT keyword

   EXCEPT returns records from the first query that are not present in the second query.

2. Step 2: Apply to given tables

   So, it returns records in Table1 but not in Table2.

3. Final Answer:

   Returns records in Table1 but not in Table2 -> Option A

4. Quick Check:

   Difference = records in first not in second [OK]

Hint: EXCEPT = first table minus second table [OK]

Common Mistakes:

• Confusing EXCEPT with UNION
• Thinking EXCEPT returns common records
• Mixing order of tables in EXCEPT

5. You have two tables:
Employees_A with IDs {101, 102, 103, 104} and Employees_B with IDs {103, 104, 105, 106}.
You want to find IDs that are in either table but not in both. Which SQL operation gives this result?

hard

A. FULL OUTER JOIN with WHERE condition

B. INTERSECT

C. SYMMETRIC DIFFERENCE using UNION and EXCEPT

D. UNION

Solution

1. Step 1: Understand the requirement

   We want IDs in either table but not in both, which is called symmetric difference.

2. Step 2: Identify SQL approach

   SQL does not have a direct symmetric difference keyword, but it can be done by combining UNION and EXCEPT operations.

3. Step 3: Explain why other options are incorrect

   UNION returns all unique IDs including common ones, INTERSECT returns only common IDs, FULL OUTER JOIN with WHERE can be complex but not direct symmetric difference.

4. Final Answer:

   SYMMETRIC DIFFERENCE using UNION and EXCEPT -> Option C

5. Quick Check:

   Symmetric difference = union minus intersection [OK]

Hint: Symmetric difference = union minus intersection [OK]

Common Mistakes:

• Using UNION alone includes common IDs
• Using INTERSECT returns only common IDs
• Assuming FULL OUTER JOIN alone solves it