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 allow you to merge rows from different sources into a single 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 corresponding columns must have compatible data types
* 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: Using UNION to remove duplicates

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: Using UNION ALL to keep duplicates

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 should I use `UNION` vs `UNION ALL`?

* Use `UNION` when you want to eliminate duplicates
* Use `UNION ALL` when performance is important or duplicate rows carry meaning

> `UNION ALL` is typically faster since it avoids 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.

UNION and UNION ALL

Syntax

Example: CodeFriends Users in Two Years

Example: Using UNION to remove duplicates

Example: Using UNION ALL to keep duplicates

When should I use UNION vs UNION ALL?

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

When should I use `UNION` vs `UNION ALL`?