UNION and UNION ALL

SQL set operations combine the results of multiple SELECT statements.

Imagine two tables:

**Current employees**

| employee_name |
|---|
| Asha |
| Rahul |

**Former employees**

| employee_name |
|---|
| Rahul |
| Meera |

You want one result containing names from both tables.

SQL provides two common options:

- **UNION** combines the results and removes duplicate complete rows.
- **UNION ALL** combines the results and preserves every row, including duplicates.

Think of UNION as combining two lists and removing repeated entries. UNION ALL combines the lists without removing anything.

Both operations append rows vertically. They do not combine columns side by side; JOIN is used for that purpose.

### General syntax

```sql
SELECT column_name
FROM first_table

UNION

SELECT column_name
FROM second_table;
```

To preserve duplicates:

```sql
SELECT column_name
FROM first_table

UNION ALL

SELECT column_name
FROM second_table;
```

Use PostgreSQL or another compatible SQL database to run the examples in this lesson.

We will create two tables representing current and former employees.

If you already have these tables from previous practice, inspect them before running the setup statements.

### Create the tables

```sql
CREATE TABLE current_employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL
);

CREATE TABLE former_employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL
);
```

### Insert sample data

```sql
INSERT INTO current_employees
(employee_id, employee_name, department)
VALUES
(1, 'Asha', 'Engineering'),
(2, 'Rahul', 'Sales'),
(3, 'Neha', 'HR');

INSERT INTO former_employees
(employee_id, employee_name, department)
VALUES
(4, 'Rahul', 'Sales'),
(5, 'Meera', 'Marketing'),
(6, 'Kabir', 'Engineering');
```

Notice that Rahul appears in both tables with the same name and department.

### Inspect the data

```sql
SELECT employee_id, employee_name, department
FROM current_employees
ORDER BY employee_id;

SELECT employee_id, employee_name, department
FROM former_employees
ORDER BY employee_id;
```

We will use these records to understand how UNION and UNION ALL behave.

UNION returns distinct complete rows from the combined result.

### Example

```sql
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name, department
FROM former_employees

ORDER BY employee_name;
```

Expected output:

| employee_name | department |
|---|---|
| Asha | Engineering |
| Kabir | Engineering |
| Meera | Marketing |
| Neha | HR |
| Rahul | Sales |

Rahul appears in both source tables, but the combined row ('Rahul', 'Sales') appears only once.

### Important: duplicates mean complete rows

Consider these rows:

| employee_name | department |
|---|---|
| Rahul | Sales |
| Rahul | Engineering |

These are not duplicates because their department values differ.

UNION compares the entire selected row, not just the first column.

### What if you select employee_id too?

```sql
SELECT employee_id, employee_name, department
FROM current_employees

UNION

SELECT employee_id, employee_name, department
FROM former_employees;
```

The two Rahul rows now have different employee IDs: 2 and 4. Therefore, both complete rows remain.

**Key lesson:** The columns you select determine which rows count as duplicates.

UNION ALL appends the second result to the first without removing duplicate rows.

### Example

```sql
SELECT employee_name, department
FROM current_employees

UNION ALL

SELECT employee_name, department
FROM former_employees

ORDER BY employee_name;
```

Expected output:

| employee_name | department |
|---|---|
| Asha | Engineering |
| Kabir | Engineering |
| Meera | Marketing |
| Neha | HR |
| Rahul | Sales |
| Rahul | Sales |

Rahul appears twice because both source queries return the same name and department.

### Why use UNION ALL?

UNION ALL is useful when:

- You must preserve every source row.
- Duplicate rows represent separate events or transactions.
- You are combining historical records from multiple sources.
- You have established that duplicate elimination is unnecessary.
- You want to avoid the duplicate-elimination work required by UNION.

UNION ALL often requires less work because it does not need to remove duplicate rows. However, the actual runtime depends on the query, data volume, database engine, and execution plan.

Do not use UNION ALL merely because it is often faster. First decide whether duplicates should be retained.

| Feature | UNION | UNION ALL |
|---|---|---|
| Combines result sets | Yes | Yes |
| Removes duplicate complete rows | Yes | No |
| Preserves duplicate rows | No | Yes |
| Duplicate-elimination work | Required | Not required |
| Typical use | A distinct combined result | Preserve every result row |

### Example: Count the results

```sql
SELECT COUNT(*) AS total_rows
FROM (
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name, department
FROM former_employees
) AS combined;
```

Expected result:

| total_rows |
|---:|
| 5 |

Using UNION ALL instead:

```sql
SELECT COUNT(*) AS total_rows
FROM (
SELECT employee_name, department
FROM current_employees

UNION ALL

SELECT employee_name, department
FROM former_employees
) AS combined;
```

Expected result:

| total_rows |
|---:|
| 6 |

The first query returns five distinct complete rows. The second returns all six source rows.

### A practical rule

Ask yourself:

"Do I want one copy of each distinct complete row, or do I want every row returned by the source queries?"

- Choose UNION for the first requirement.
- Choose UNION ALL for the second.

The SELECT statements in a set operation must have compatible result structures.

### Rule 1: The number of columns must match

Incorrect:

```sql
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name
FROM former_employees;
```

The first query returns two columns and the second returns one. PostgreSQL rejects this query.

Correct:

```sql
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name, department
FROM former_employees;
```

### Rule 2: Corresponding columns must have compatible data types

For example, a text column should not be combined with an unrelated type unless the database can resolve a compatible common type.

A deliberate cast can make the intended type clear:

```sql
SELECT employee_id::TEXT AS source_id
FROM current_employees

UNION ALL

SELECT employee_id::TEXT AS source_id
FROM former_employees;
```

Here, both queries return text values.

### Rule 3: Column positions matter

The first column of one query is matched with the first column of the other query. The second is matched with the second, and so on.

Column names do not determine how values are matched.

### Rule 4: Column names in the result

The output column names normally come from the first SELECT.

Use aliases in the first query to define meaningful output names.

### Rule 5: ORDER BY applies to the combined result

```sql
SELECT employee_name AS name
FROM current_employees

UNION

SELECT employee_name
FROM former_employees

ORDER BY name;
```

This sorts the final result.

For readability and portability, use output column names instead of relying on positional ordering such as ORDER BY 1.

### Rule 6: Result order is not guaranteed without ORDER BY

Even if one query happens to return rows in a particular order, the combined result is not guaranteed to preserve that order.

Use an outer ORDER BY whenever a specific final order is required.

A common use of set operations is combining records from different tables that represent similar information.

Imagine one table contains current employees and another contains former employees. You want a unified employee list with a source label.

### Example

```sql
SELECT
employee_id,
employee_name,
department,
'Current' AS employment_status
FROM current_employees

UNION ALL

SELECT
employee_id,
employee_name,
department,
'Former' AS employment_status
FROM former_employees

ORDER BY employee_name;
```

Expected output:

| employee_id | employee_name | department | employment_status |
|---:|---|---|---|
| 1 | Asha | Engineering | Current |
| 6 | Kabir | Engineering | Former |
| 5 | Meera | Marketing | Former |
| 3 | Neha | HR | Current |
| 2 | Rahul | Sales | Current |
| 4 | Rahul | Sales | Former |

Rahul appears twice, with a different ID and status.

### Why UNION ALL here?

The two rows represent different employee records. Removing duplicate-looking names would be inappropriate if each row must be preserved.

### Other examples

- Combining completed and pending transaction records.
- Combining events from different application modules.
- Combining data from regional sales tables.
- Combining current and archived records.
- Building a single report from several compatible queries.

The SELECT statements should produce columns with consistent meanings, not just matching data types.

NULL represents a missing or unknown value. Set operations have specific rules for determining whether result rows are duplicates.

### Example

```sql
SELECT NULL::INTEGER AS optional_code

UNION

SELECT NULL::INTEGER AS optional_code;
```

Expected result:

| optional_code |
|---|
| NULL |

UNION returns one row rather than two.

With UNION ALL:

```sql
SELECT NULL::INTEGER AS optional_code

UNION ALL

SELECT NULL::INTEGER AS optional_code;
```

Expected result:

| optional_code |
|---|
| NULL |
| NULL |

### Important distinction

Ordinary SQL comparisons such as NULL = NULL evaluate to UNKNOWN. However, duplicate elimination in UNION treats rows with corresponding NULL values as duplicates for set-operation purposes.

Do not assume that duplicate elimination follows the same rules as writing an equality comparison in a WHERE clause.

You can combine more than two SELECT statements in a single set operation.

### Example

```sql
SELECT employee_name AS person_name
FROM current_employees

UNION ALL

SELECT employee_name
FROM former_employees

UNION ALL

SELECT 'External Contractor'
ORDER BY person_name;
```

The third SELECT returns a single text value without reading a table. PostgreSQL allows a SELECT without FROM when you want to return an expression.

All three queries must return compatible column structures.

### Mixing UNION and UNION ALL

When combining several set operations, use parentheses when necessary to make the intended grouping explicit.

For example, if you want to preserve duplicates within two sources but then remove duplicates from the overall result, express and test the grouping carefully:

```sql
SELECT employee_name
FROM current_employees

UNION

SELECT employee_name
FROM former_employees;
```

If you need a specific combination of operations, an outer query can make the intended result easier to read:

```sql
SELECT DISTINCT person_name
FROM (
SELECT employee_name AS person_name
FROM current_employees

UNION ALL

SELECT employee_name
FROM former_employees
) AS all_people;
```

This example first retains every source row in the subquery, then removes duplicate names in the outer query.

Choose the clearest expression of the required result rather than relying on readers to infer complicated grouping.

UNION generally requires duplicate elimination, which can involve sorting, hashing, or another plan selected by the database engine. UNION ALL does not need that duplicate-elimination step.

### Inspect a UNION plan

```sql
EXPLAIN
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name, department
FROM former_employees;
```

### Inspect a UNION ALL plan

```sql
EXPLAIN
SELECT employee_name, department
FROM current_employees

UNION ALL

SELECT employee_name, department
FROM former_employees;
```

The plans can differ because UNION must produce distinct complete rows.

### Best practices

- Use UNION ALL when preserving all rows is correct.
- Use UNION when duplicate complete rows must be removed.
- Select only the columns required by the report.
- Avoid unnecessary duplicate elimination.
- Use EXPLAIN to inspect the plan.
- Use EXPLAIN ANALYZE when you need actual execution measurements and can safely execute the query.
- Test with representative data volumes.

**Important:** UNION ALL is often faster, but it is not guaranteed to be faster in every situation. Correctness comes before optimization.

Use the current_employees and former_employees tables.

### Exercise 1: Basic UNION

Return distinct employee names from both tables.

### Exercise 2: UNION ALL

Return every employee name from both tables, preserving duplicates.

### Exercise 3: Select complete rows

Combine employee names and departments using UNION. Explain why Rahul appears once.

### Exercise 4: Count results

Calculate the number of rows returned by UNION ALL when selecting employee names and departments.

### Exercise 5: Add a source label

Combine the current and former employee records and add an employment_status column.

### Exercise 6: Data type compatibility

Convert employee_id to text in both SELECT statements and combine the results.

### Exercise 7: NULL handling

Combine two SELECT statements that each return a NULL integer using UNION. How many rows appear?

### Exercise 8: Sort the result

Combine employee names from both tables using UNION ALL and sort the final result alphabetically.

### Exercise 9: Find the error

Explain why UNION fails if the first SELECT returns two columns and the second SELECT returns three.

### Exercise 10: Choose the right operator

You are combining payment events from two systems. Each event must be retained even if two events have identical displayed values. Which operator should you choose, and why?

Try solving the problems before reviewing the solutions.

### Solution 1: Basic UNION

```sql
SELECT employee_name
FROM current_employees

UNION

SELECT employee_name
FROM former_employees

ORDER BY employee_name;
```

Expected: Asha, Kabir, Meera, Neha, Rahul.

### Solution 2: UNION ALL

```sql
SELECT employee_name
FROM current_employees

UNION ALL

SELECT employee_name
FROM former_employees

ORDER BY employee_name;
```

Expected: six rows, including Rahul twice.

### Solution 3: Names and departments

```sql
SELECT employee_name, department
FROM current_employees

UNION

SELECT employee_name, department
FROM former_employees

ORDER BY employee_name;
```

Expected: five rows. The complete row for Rahul in Sales is identical in both results, so UNION retains one copy.

### Solution 4: Count the rows

```sql
SELECT COUNT(*) AS total_rows
FROM (
SELECT employee_name, department
FROM current_employees

UNION ALL

SELECT employee_name, department
FROM former_employees
) AS combined;
```

Expected: 6.

### Solution 5: Add a source label

```sql
SELECT
employee_id,
employee_name,
department,
'Current' AS employment_status
FROM current_employees

UNION ALL

SELECT
employee_id,
employee_name,
department,
'Former' AS employment_status
FROM former_employees
ORDER BY employee_name;
```

Expected: six rows, each labeled Current or Former.

### Solution 6: Convert IDs to text

```sql
SELECT employee_id::TEXT AS source_id
FROM current_employees

UNION ALL

SELECT employee_id::TEXT
FROM former_employees;
```

Both SELECT statements return a text column.

### Solution 7: NULL handling

```sql
SELECT NULL::INTEGER AS optional_code

UNION

SELECT NULL::INTEGER AS optional_code;
```

Expected: one row containing NULL.

### Solution 8: Sort the combined result

```sql
SELECT employee_name
FROM current_employees

UNION ALL

SELECT employee_name
FROM former_employees

ORDER BY employee_name;
```

Expected: Asha, Kabir, Meera, Neha, Rahul, Rahul.

### Solution 9: Column count

Set operations require the same number of columns in every SELECT. Select matching columns or revise the query structure.

### Solution 10: Preserve payment events

Use UNION ALL because the requirement is to preserve every event. Identical displayed values do not necessarily mean two events are the same event.

Choose the best answer for each question.

### 1. What is UNION?

UNION combines the results of SELECT statements and removes duplicate complete rows.

### 2. What is UNION ALL?

UNION ALL combines SELECT results while preserving duplicate rows.

### 3. What is the main difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL does not.

### 4. Why can UNION ALL be faster?

It avoids the duplicate-elimination step required by UNION. Actual performance still depends on the query plan and workload.

### 5. What are the requirements for UNION?

The SELECT statements must return the same number of columns, and corresponding columns must have compatible data types.

### 6. Does UNION match columns by name?

No. It matches columns by their positions in the result.

### 7. How does UNION determine duplicates?

It compares the complete selected rows, not just one column.

### 8. Does UNION guarantee the output order?

No. Use ORDER BY when you need a particular order.

### 9. Can UNION combine results from different tables?

Yes, provided the SELECT results have compatible structures and the intended column meanings are consistent.

### 10. Can UNION combine queries with different WHERE clauses?

Yes. Each SELECT can have its own filtering conditions.

### 11. What happens if the SELECT statements return different numbers of columns?

The set operation fails because their result structures are incompatible.

### 12. How do NULL values affect UNION?

Rows with corresponding NULL values can be considered duplicates during duplicate elimination, even though NULL = NULL evaluates to UNKNOWN.

### 13. What happens if the first query returns an alias?

The first SELECT normally determines the output column names.

### 14. Can UNION ALL return the same row twice?

Yes. It preserves duplicates from the same source and across different sources.

### 15. Can UNION be used with more than two SELECT statements?

Yes. Multiple compatible SELECT statements can be combined.

### 16. How do you sort a UNION result?

Place ORDER BY at the end of the combined query.

### 17. When would you use UNION ALL in reporting?

Use it when all source records must be preserved, such as combining event or transaction records.

### 18. Does UNION ALL mean the database will never sort?

No. The optimizer may use sorting for other reasons, such as a final ORDER BY. UNION ALL simply does not require duplicate elimination.

### 19. How would you inspect the query plan?

Use EXPLAIN to inspect planned operations and EXPLAIN ANALYZE to execute the query and examine actual measurements.

### 20. Should you always replace UNION with UNION ALL for performance?

No. Doing so changes the result when duplicate rows exist. First determine whether duplicates should be retained.

## Summary

- UNION combines compatible result sets and removes duplicate complete rows.
- UNION ALL combines compatible result sets and preserves duplicates.
- Each SELECT must return the same number of columns.
- Corresponding data types must be compatible.
- Columns are matched by position, not by name.
- Duplicate elimination considers the complete output row.
- The first SELECT normally determines output column names.
- ORDER BY at the end sorts the combined result.
- Neither UNION nor UNION ALL guarantees a particular order without ORDER BY.
- UNION ALL often avoids duplicate-elimination work, but performance is not guaranteed.
- NULL values can be treated as duplicates by UNION.
- Choose the operator based on the intended result.

## Before moving to the next lesson

- [ ] Explain UNION and UNION ALL in simple language.
- [ ] Write a query using UNION.
- [ ] Write a query using UNION ALL.
- [ ] Explain why selected columns determine duplicate rows.
- [ ] Match the column count and data types across SELECT statements.
- [ ] Combine records from two tables with a source label.
- [ ] Sort the combined result using ORDER BY.
- [ ] Explain how UNION handles NULL rows.
- [ ] Explain why UNION ALL may avoid extra processing.
- [ ] Use EXPLAIN to inspect a query plan.

**Next:** Continue to the next lesson in Subqueries and Set Operations.

Avoid these mistakes when combining query results.