Lecture 31 – Combining Results with UNION

This lecture covers the UNION and UNION ALL operators. So far, we have only worked with the results from a single SELECT statement at a time (even if it involved multiple tables through joins). The UNION operator is different. It is used to combine the result sets of two or more separate SELECT statements into a single result set.

The `UNION` Operator

Imagine you have two different tables of contacts: one for `LocalContacts` and one for `InternationalContacts`. If you wanted to get a single, combined list of all contact names, you could use `UNION`.

The UNION operator, by default, only returns distinct (unique) rows. If a contact name appears in both tables, it will only be listed once in the final result.

Rules for `UNION`

To use `UNION`, the `SELECT` statements must follow these rules:

1. They must have the same number of columns.
2. The columns must have similar, compatible data types.
3. The columns in each `SELECT` statement must be in the same order.

`UNION` vs. `UNION ALL`

There is another version of the operator: UNION ALL.

• UNION: Selects only distinct values (removes duplicates).
• UNION ALL: Selects all values, including any duplicates.

Because UNION ALL does not have to do the extra work of checking for and removing duplicates, it is significantly faster than UNION. If you know that your combined results won’t have duplicates, or if you don’t care about having duplicates in the output, it is always better to use UNION ALL for performance.

Example

CREATE TABLE Staff (Name TEXT, Role TEXT);
INSERT INTO Staff VALUES ('Alice', 'Developer'), ('Bob', 'Designer');

CREATE TABLE Contractors (Name TEXT, Role TEXT);
INSERT INTO Contractors VALUES ('Charlie', 'Developer'), ('David', 'SysAdmin');

-- Now, let's get a single list of all roles in the company.
SELECT Role FROM Staff
UNION
SELECT Role FROM Contractors;

The result of the UNION query will be: Developer, Designer, SysAdmin. The role ‘Developer’ appears only once, because UNION removes the duplicate.

If we used UNION ALL, the result would be: Developer, Designer, Developer, SysAdmin.

In the Next Lecture…

We will dive into one of the most powerful and sometimes complex concepts in SQL: subqueries. A subquery is a query that is nested inside another query, allowing you to perform multi-step logic in a single statement.

Lecture 30: Mini-Project – Querying the Blog