Set Operations in SQL

 

Set Operations in SQL (MySQL)

---

1. Aim

To understand and implement SQL set operations such as UNION, UNION ALL, INTERSECT (simulated), and EXCEPT (simulated) using MySQL.

---

2. Theory

Set operations in SQL allow combining results from multiple queries into a single result set. They are useful when comparing datasets, finding common or unique values, and merging information from different sources.

2.1 Types of Set Operations

1. UNION

   • Combines results of two SELECT queries.

   • Removes duplicates.

2. UNION ALL

   • Combines results of two SELECT queries.

   • Retains duplicates.

3. INTERSECT

   • Returns rows common to both queries.

   • MySQL does not support INTERSECT directly, but it can be simulated using INNER JOIN or IN.

4. EXCEPT (MINUS)

   • Returns rows from the first query that are not present in the second query.

   • MySQL does not support EXCEPT directly, but it can be simulated using LEFT JOIN … WHERE NULL or NOT IN.

---

3. Problem Statement

Consider two tables:

• employees: contains employee IDs and names.

• contractors: contains contractor IDs and names.

Perform SQL set operations to compare data between the two tables.

---

4. Table Schema

4.1 Create Tables

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE contractors (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

4.2 Insert Sample Data

INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO contractors (id, name) VALUES
(2, 'Bob'),
(4, 'Dave'),
(5, 'Eve');

---

5. SQL Set Operations

5.1 UNION

Removes duplicates from combined result sets.

SELECT name FROM employees
UNION
SELECT name FROM contractors;

Output:

name
Alice
Bob
Charlie
Dave
Eve

---

5.2 UNION ALL

Includes duplicates.

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

Output:

name
Alice
Bob
Charlie
Bob
Dave
Eve

---

5.3 INTERSECT (Simulated using INNER JOIN)

Finds rows common to both tables.

SELECT e.name 
FROM employees e
INNER JOIN contractors c ON e.id = c.id;

Output:

name
Bob

---

5.4 EXCEPT (Simulated using LEFT JOIN)

Finds rows in employees but not in contractors.

SELECT e.name
FROM employees e
LEFT JOIN contractors c ON e.id = c.id
WHERE c.id IS NULL;

Output:

name
Alice
Charlie

Alternative using NOT IN:

SELECT name
FROM employees
WHERE id NOT IN (SELECT id FROM contractors);

---

6. Results

• Successfully implemented UNION, UNION ALL, and simulated INTERSECT and EXCEPT in MySQL.

• Observed how MySQL handles set operations and alternatives for unsupported ones.

• Analyzed differences between removing and retaining duplicates in query outputs.

---

7. Conclusion

Set operations in SQL allow effective comparison and merging of datasets.
Although MySQL does not directly support INTERSECT and EXCEPT, equivalent results can be achieved using JOINs and subqueries. Understanding these operations is essential for efficient database querying and data analysis.