SQL: Set Operation with example

In SQL, there are several set operations that can be performed on tables or result sets. These set operations allow you to combine, compare, or exclude data from multiple tables or result sets. The common set operations in SQL are:

Union: The Union operation combines the result sets of two or more SELECT statements into a single result set, eliminating duplicate rows. The columns in the SELECT statements must have the same data types.

Example:
Let’s say we have two tables, “Customers” and “Suppliers,” with the same columns: “ID” and “Name.” We want to retrieve all distinct names from both tables.

SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;

Output:

+------------+
|    Name    |
+------------+
| John       |
| Mary       |
| Robert     |
| David      |
| Richard    |
| Michael    |
| Jennifer   |
| Samantha   |
| Elizabeth  |
| Emma       |
+------------+

Union All: The Union All operation is similar to the Union operation but does not eliminate duplicate rows. It combines the result sets of multiple SELECT statements into a single result set, including all rows.

Example:
Using the same “Customers” and “Suppliers” tables, we can retrieve all names from both tables, including duplicates.

SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Suppliers;

Output:

+------------+
|    Name    |
+------------+
| John       |
| Mary       |
| Robert     |
| David      |
| Richard    |
| Michael    |
| Jennifer   |
| Samantha   |
| Elizabeth  |
| Emma       |
| Mary       |
| Robert     |
+------------+

Intersect: The Intersect operation returns the common rows between two result sets, removing any duplicates. The columns in the SELECT statements must have the same data types.

Example:
Consider two tables, “Customers” and “Orders,” both containing the “ID” column. We want to retrieve the IDs that exist in both tables.

SELECT ID FROM Customers
INTERSECT
SELECT ID FROM Orders;

Output:

+----+
| ID |
+----+
|  1 |
|  3 |
|  5 |
+----+

Except or Minus: The Except operation (also known as Minus in some database systems) returns the rows that exist in the first result set but not in the second result set. It eliminates any duplicate rows.

Example:
Let’s say we have two tables, “Customers” and “VIPCustomers,” both with the “ID” column. We want to retrieve the IDs from the “Customers” table that are not present in the “VIPCustomers” table.

SELECT ID FROM Customers
EXCEPT
SELECT ID FROM VIPCustomers;

Output:

+----+
| ID |
+----+
|  3 |
|  5 |
+----+

These are the basic set operations in SQL that allow you to manipulate and combine data from multiple tables or result sets to achieve the desired results.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all

SQL: Set Operation with example