lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter2Title

sqlIntroAdvancedChapter1Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- Combine both user tables, removing duplicates
SELECT user_id, name FROM users_2023
UNION
SELECT user_id, name FROM users_2024;

-- Combine both user tables, keeping all rows (including duplicates)
SELECT user_id, name FROM users_2023
UNION ALL
SELECT user_id, name FROM users_2024;

# UNION and UNION ALL

`UNION` and `UNION ALL` are SQL set operations that combine the results of two or more `SELECT` queries. 

`UNION` removes duplicates, while `UNION ALL` includes all rows, even duplicates.

They let you merge rows from different sources into one unified result.

 

## Syntax

The basic syntax for `UNION` and `UNION ALL` is as follows:

```sql title="UNION syntax"
SELECT column1, column2
FROM table1

UNION [ALL]

SELECT column1, column2
FROM table2;
```

*Rules:*

* Each query must return the same number of columns
* The data types must be compatible
* The result set takes its column names from the first `SELECT`

 

## Example: CodeFriends Users in Two Years

Suppose we're analyzing registered users in 2023 and 2024.

We have two tables:

*users_2023*

| user_id | name |
| -------- | ----- |
| 1 | Sofia |
| 2 | Ethan |
| 3 | Aisha |

*users_2024*

| user_id | name |
| -------- | ----- |
| 1 | Sofia |
| 3 | Aisha |
| 4 | Noah |

 

## Example: UNION

The following query returns all users from both years.

```sql title="Using UNION"
SELECT user_id, name FROM users_2023
UNION
SELECT user_id, name FROM users_2024;
```

*Result:*

| user_id | name |
| -------- | ----- |
| 1 | Sofia |
| 2 | Ethan |
| 3 | Aisha |
| 4 | Noah |

`UNION` removes duplicates, so only one entry per unique row is returned.

 

## Example: UNION ALL

The following query returns all users from both years, including duplicates.

```sql title="Using UNION ALL"
SELECT user_id, name FROM users_2023
UNION ALL
SELECT user_id, name FROM users_2024;
```

*Result:*

| user_id | name |
| -------- | ----- |
| 1 | Sofia |
| 2 | Ethan |
| 3 | Aisha |
| 1 | Sofia |
| 3 | Aisha |
| 4 | Noah |

 

## When to Use

* Use `UNION` when you want to eliminate duplicates
* Use `UNION ALL` when performance matters or duplicate rows are meaningful

> `UNION ALL` is typically faster because it skips the deduplication step.

Both UNION and UNION ALL will return the same result if there are no duplicates in the combined datasets. UNION removes duplicates, while UNION ALL does not; however, if there are no duplicates to begin with, both operations yield the same outcome.

### UNION and UNION ALL return the same result when there are no duplicate rows in the combined datasets.