lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter2Title

sqlIntroAdvancedChapter1Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- Users who appeared in both 2023 and 2024
SELECT user_id, name
FROM users_2023
INTERSECT
SELECT user_id, name
FROM users_2024;

-- Users who appeared in 2023 but not in 2024
SELECT user_id, name
FROM users_2023
EXCEPT
SELECT user_id, name
FROM users_2024;

# INTERSECT and EXCEPT

`INTERSECT` and `EXCEPT` are SQL set operations that let you compare the results of two `SELECT` queries.

- `INTERSECT` returns only rows that exist in both result sets.
- `EXCEPT` returns rows that exist in the first query but not in the second.

 

## Syntax

You can use `INTERSECT` to compare the results of two queries like this:

```sql title="INTERSECT syntax"
SELECT column1, column2
FROM tableA
INTERSECT
SELECT column1, column2
FROM tableB;
```

The syntax for `EXCEPT` is similar, but it returns rows that exist in the first query but not in the second.

```sql title="EXCEPT syntax"
SELECT column1, column2
FROM tableA
EXCEPT
SELECT column1, column2
FROM tableB;
```

> Like `UNION`, both queries must return the same number of columns with compatible data types.

 

## Example: CodeFriends Users in Two Years

Suppose we want to compare users from 2023 and 2024.

*users_2023*

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

*users_2024*

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

 

## INTERSECT Example

```sql title="INTERSECT query"
SELECT user_id, name
FROM users_2023
INTERSECT
SELECT user_id, name
FROM users_2024;
```

*Result:*

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

These are users who were active in *both years*.

 

## EXCEPT Example

```sql title="EXCEPT query"
SELECT user_id, name
FROM users_2023
EXCEPT
SELECT user_id, name
FROM users_2024;
```

*Result:*

| user_id | name |
| -------- | ----- |
| 2 | Ethan |

This returns users who were active in *2023 only* and didn't return in 2024.

 

## Why Use INTERSECT and EXCEPT?

* To find *shared records* between datasets (`INTERSECT`)
* To identify *removed or missing entries* (`EXCEPT`)
* Useful for *audits, tracking changes*, and *membership comparisons*

The EXCEPT operation is used in SQL to find rows that appear in the first query's result set but not in the second. It's particularly useful for identifying differences between datasets, such as records that are present in one year but not the next. This operation can help in audits and tracking changes across time periods.

### What SQL operation would you use to find rows that are present in the first query but not in the second?