SQL Set Operations
Set operations in SQL are used to combine results from two or more SELECT queries. The key
set operations are:
1. UNION – Combines results of two queries and removes duplicates.
2. UNION ALL – Combines results of two queries, including duplicates.
3. INTERSECT – Returns only the common records between two queries.
4. EXCEPT (or MINUS in some databases) – Returns records from the first query that are
not in the second query.
Example Schema
CREATE
CREATE TABLE EngineeringEmployees ( EmployeeID INT PRIMARY KEY, Name
VARCHAR(50), Salary DECIMAL(10,2) );
CREATE TABLE MarketingEmployees ( EmployeeID INT PRIMARY KEY, Name
VARCHAR(50), Salary DECIMAL(10,2) );
INSERT
INSERT INTO EngineeringEmployees (EmployeeID, Name, Salary) VALUES (1, 'Alice',
70000), (2, 'Bob', 80000), (3, 'Charlie', 90000);
INSERT INTO MarketingEmployees (EmployeeID, Name, Salary) VALUES (3, 'Charlie',
90000), (4, 'David', 75000), (5, 'Eve', 60000);
1. UNION (Removes Duplicates)
Combines the result sets of both queries but removes duplicate rows.
SELECT Name FROM EngineeringEmployees UNION SELECT Name FROM
MarketingEmployees;
2. UNION ALL (Keeps Duplicates)
Includes all records from both tables, including duplicates.
SELECT Name FROM EngineeringEmployees UNION ALL SELECT Name FROM
MarketingEmployees;
3. INTERSECT (Finds Common Records)
Returns only the records that are present in both queries.
SELECT Name FROM EngineeringEmployees INTERSECT SELECT Name FROM
MarketingEmployees;
4. EXCEPT (Finds Records in First Table but Not in Second)
Returns records from EngineeringEmployees that are not in MarketingEmployees.
SELECT Name FROM EngineeringEmployees EXCEPT SELECT Name FROM
MarketingEmployees;
(Or)
SELECT Name FROM EngineeringEmployees WHERE Name NOT IN (SELECT Name
FROM MarketingEmployees);
Try it out:
A company maintains two tables: SoftwareEngineers and DataScientists. The tables store
information about employees in different technical departments.
CREATE TABLE SoftwareEngineers ( EmployeeID INT PRIMARY KEY, Name
VARCHAR(50), Experience INT, -- in years Salary DECIMAL(10,2) );
CREATE TABLE DataScientists ( EmployeeID INT PRIMARY KEY, Name
VARCHAR(50), Experience INT, -- in years Salary DECIMAL(10,2) );
Tasks:
Basic Queries:
1. Write an SQL query to retrieve all employees who have more than 5 years of
experience from both tables.
2. Find the total number of employees in both tables.
DML (Data Manipulation Language):
1. Insert a new Software Engineer named 'Grace' with 2 years of experience and a salary
of $70,000.
2. Update Eve’s salary in the DataScientists table to $98,000.
3. Delete the employee 'Frank' from the DataScientists table.
Set Operations:
1. Use UNION to list all unique employee names from both tables.
2. Use INTERSECT to find employees who are present in both tables.
3. Use EXCEPT to find Software Engineers who are not Data Scientists.
DML commands
DML (Data Manipulation Language) commands are used to manipulate and interact
with data in a database. The primary DML commands are SELECT, INSERT,
UPDATE, and DELETE.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);