Advanced SQL - Set Operations

SQL set operations combine the results of two or more SELECT statements into a single result set. These operators are similar to mathematical set theory concepts and are used to merge, find commonalities, or identify differences between data sets.

For these operations to work, two main rules must be followed:

1. The SELECT statements must have the same number of columns.

2. The data types of the corresponding columns must be compatible (e.g., you can't combine a number column with a text column).

---

UNION

The UNION operator combines the result sets of two or more SELECT statements and removes duplicate rows. It effectively appends one result set to another and then filters out any identical records.

Analogy: Think of combining two guest lists for a party and removing anyone who appears on both lists, so you only have a single entry for each unique guest.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Example:

If you have a table of Employees and a table of Consultants, you can get a single list of all unique people working for the company.

Query Result:

SELECT Name FROM Employees
UNION
SELECT Name FROM Consultants;

Notice that "Charlie," who was in both tables, appears only once.

---

UNION ALL

The UNION ALL operator also combines the result sets of two or more SELECT statements, but it includes all rows, including duplicates. It is faster than UNION because it doesn't need to perform the extra step of checking for and removing duplicates.

Analogy: This is like simply stapling two guest lists together without checking for any overlapping names. 🖇️

Syntax:

SQL

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Example:

Using the same Employees and Consultants tables:

Query Result:

SQL

SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Consultants;

Here, "Charlie" appears twice because UNION ALL keeps all records from both queries.

---

INTERSECT

The INTERSECT operator returns only the rows that are common to both SELECT statements. It finds the intersection, or overlap, between the two result sets.

Analogy: This is like looking at two guest lists and finding only the people who were invited to both parties.

Syntax:

SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

Example:

To find who is both an employee and a consultant:

Query Result:

SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Consultants;

Only "Charlie" is returned because he is the only person present in both tables.

---

EXCEPT (or MINUS in Oracle)

The EXCEPT operator returns the rows from the first SELECT statement that are not present in the second SELECT statement. The order of the queries matters.

Analogy: You have a list of all invited guests and a second list of guests who have RSVP'd 'Yes'. EXCEPT would give you the list of people who were invited but have not yet replied. 🤔

Syntax:

SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2;

Example:

To find people who are employees but not consultants:

Query Result:

SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Consultants;

This query returns Alice and Bob because they are in the Employees table but not in the Consultants table. "Charlie" is excluded because he is present in the second result set.