Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

SET operations in SQL (UNION/INTERSECT/MINUS)

Set operations combine the results of two or more SELECT queries into a single result set — just like in mathematics.

📌 Rules:

  • Number of columns and their data types must match

  • Column order matters

  • By default, they remove duplicates

🤯 Base Setup for further examples:

-- 1. Create the first table
CREATE TABLE science_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 2. Create the second table
CREATE TABLE math_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- 3. Insert sample data
INSERT INTO science_students (name) VALUES
('Anya'),
('Ravi'),
('Sara');

INSERT INTO math_students (name) VALUES
('Ravi'),
('Tina'),
('Meena');

🔗 UNION – Combine & Remove Duplicates

Returns all unique rows from both queries.

📌 Example:

-- UNION query to get unique student names from both tables
SELECT name FROM science_students
UNION
SELECT name FROM math_students;

✔️ Combines names from both tables

✔️ Removes any duplicate names

➕ UNION ALL – Combine All (Including Duplicates)

Same as UNION but keeps duplicate rows

📌 Example:

-- Use UNION ALL to combine names (including duplicates)
SELECT name FROM science_students
UNION ALL
SELECT name FROM math_students;

✔️ Faster than UNION

✔️ Includes repeated names

🎯 INTERSECT – Common Rows Only

Returns only the rows that exist in both queries.

📌 Example:

-- ✅ INTERSECT equivalent using INNER JOIN
SELECT s.name
FROM science_students s
INNER JOIN math_students m ON s.name = m.name;
-- ✅ INTERSECT equivalent using IN
SELECT name
FROM science_students
WHERE name IN (SELECT name FROM math_students);

✔️ Shows students enrolled in both subjects

❗ Not supported in MySQL (can be emulated using INNER JOIN or IN)

➖ MINUS (or EXCEPT) – Rows in First, Not in Second

Returns rows from the first query that do not appear in the second.

📌 Example:

-- ✅ MINUS Equivalent using LEFT JOIN
SELECT s.name
FROM science_students s
LEFT JOIN math_students m ON s.name = m.name
WHERE m.name IS NULL;
-- ✅ MINUS Equivalent using NOT IN
SELECT name
FROM science_students
WHERE name NOT IN (SELECT name FROM math_students);

✔️ Shows students only in science, not in math

✅ MINUS → Oracle

✅ EXCEPT → PostgreSQL & SQL Server

❌ Not supported in MySQL directly

✅ Summary Table

Set Operation

Description

Removes Duplicates

MySQL Support

🔗 UNION

Combines results, removes duplicates

✅ Yes

✅ Yes

➕ UNION ALL

Combines results, keeps duplicates

❌ No

✅ Yes

🎯 INTERSECT

Common rows in both queries

✅ Yes

❌ No (use JOIN)

➖ MINUS/EXCEPT

Rows in 1st query but not in 2nd

✅ Yes

❌ No (use LEFT JOIN)