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 used to compare the results of two `SELECT` queries.

- `INTERSECT` returns only rows that exist in both result sets.
- `EXCEPT` returns rows that appear 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 appear in the first result set 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, and their data types must be compatible.

 

## 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 |

This result shows 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 result shows users who were active *in 2023 only* and did not return in 2024.

 

## Why Use `INTERSECT` and `EXCEPT`?

* To find *shared records* between datasets use `INTERSECT`
* To identify *removed or missing entries* use `EXCEPT`
* Helpful 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?

INTERSECT and EXCEPT

Syntax

Example: CodeFriends Users in Two Years

INTERSECT Example

EXCEPT Example

Why Use INTERSECT and EXCEPT?

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

Why Use `INTERSECT` and `EXCEPT`?