> For the complete documentation index, see [llms.txt](https://marcylabschool.gitbook.io/swe/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://marcylabschool.gitbook.io/swe/mod-6-databases/6-join-queries.md).

# 6. JOIN Queries

{% hint style="info" %}
Follow along with code examples [here](https://github.com/The-Marcy-Lab-School/6-6-join-queries)!
{% endhint %}

In the previous lesson, you designed multi-table schemas and used foreign keys to connect related data. At the very end, there was a teaser:

```sql
SELECT students.first_name, classes.title
FROM students
  INNER JOIN enrollments ON students.student_id = enrollments.student_id
  INNER JOIN classes     ON enrollments.class_id = classes.class_id
WHERE students.first_name = 'Alice';
```

That query pulls data from three tables in a single statement to answer "What classes is Alice enrolled in?" This lesson is about understanding exactly how that works.

A **JOIN** combines rows from two tables based on a matching column — typically a foreign key equal to a primary key. The `REFERENCES` constraints you wrote in your schema aren't just for data integrity — they define the connections that JOINs traverse.

**Table of Contents**

* [Essential Questions](#essential-questions)
* [Key Concepts](#key-concepts)
* [Setup](#setup)
* [One-to-Many: Users and Posts](#one-to-many-users-and-posts)
* [JOINs](#joins)
  * [INNER JOIN — Only Matching Rows](#inner-join--only-matching-rows)
  * [INNER JOIN Practice](#inner-join-practice)
  * [LEFT JOIN — All Rows](#left-join--all-rows)
  * [LEFT JOIN Practice](#left-join-practice)
* [GROUP BY with JOINs](#group-by-with-joins)
  * [Smart GROUP BY with Primary Keys](#smart-group-by-with-primary-keys)
* [Many-to-Many Relationships](#many-to-many-relationships)
  * [Association Tables and Three-Way JOINs](#association-tables-and-three-way-joins)
* [Challenge: JOIN Queries](#challenge-join-queries)

## Essential Questions

By the end of this lesson, you should be able to answer these questions:

1. In SQL, why would a developer use a JOIN clause?
2. What does `INNER JOIN` do? What rows does it include, and what does it exclude?
3. What is the difference between `INNER JOIN` and `LEFT JOIN`? When do you use each?
4. How do you use `GROUP BY` in a JOIN query? Why do you only need to `GROUP BY` the primary key in Postgres?
5. What is a many-to-many relationship? How do you query one using an association table?

## Key Concepts

* **`INNER JOIN`** — returns only rows where both tables have a matching value on the join condition. Rows with no match on either side are excluded.
* **`LEFT JOIN`** — returns all rows from the *left* table. Rows with no match in the right table have `NULL` for all right-side columns.
* **Join condition** — the `ON` clause specifying which columns must match, usually a foreign key equaling a primary key.
* **Many-to-many** — a relationship where each row in table A can relate to many rows in table B, and vice versa. Represented in SQL with an association table.
* **Association/junction table** — a table that represents a many-to-many relationship by holding (at minimum) two foreign keys, one for each side.

## Setup

The follow-along repo includes a `setup.sql` file. Create a `social_db` database and then run `setup.sql` to create the `users`, `posts`, `tags`, and `post_tags` tables pre-loaded with sample data.

{% tabs %}
{% tab title="Mac" %}

```sh
# Create the database
createdb social_db

# Run the setup SQL file
psql -f setup.sql

# Connect to social_db
psql social_db
```

{% endtab %}

{% tab title="Windows + WSL" %}

```sh
# Create the database
sudo -u postgres createdb social_db

# Run the setup SQL file
sudo -u postgres psql -f setup.sql

# Connect to social_db
sudo -u postgres psql social_db
```

{% endtab %}
{% endtabs %}

## One-to-Many: Users and Posts

Consider the "flat" table of users and the posts they've made:

| post\_id | title             | user\_id | username   | email                |
| -------- | ----------------- | -------- | ---------- | -------------------- |
| 1        | My First Post     | 1        | ann\_duong | <ann@example.com>    |
| 2        | Learning SQL      | 1        | ann\_duong | <ann@example.com>    |
| 3        | Postgres Tips     | 2        | reuben\_o  | <reuben@example.com> |
| 4        | Why I Love Coding | 3        | carmen\_s  | <carmen@example.com> |
| 5        | Advanced Joins    | 2        | reuben\_o  | <reuben@example.com> |

<details>

<summary><strong>Q: What is the problem with storing all of this data in a single flat table instead of two separate tables?</strong></summary>

A flat table like this breaks the normalization rule requiring every column to be dependent on the primary key. `username` and `email` are dependent on `user_id`, not on `post_id`:

* `username` and `email` are duplicated on every post row for the same user
* If a user changes their email, you'd need to update every post they've ever written
* A user with no posts would have no row at all

Splitting into two tables eliminates all of this. The user's info lives in one row in `users`. Posts reference it via `user_id`. Change the email once — all posts see the update automatically through the foreign key.

</details>

To create a normalized schema, we split the data into two tables: `users` and `posts`. These tables have a **one-to-many** relationship: one user can author many posts. The `posts` table holds a `user_id` foreign key that references `users.user_id`.

**`users` table:**

| user\_id | username   | email                |
| -------- | ---------- | -------------------- |
| 1        | ann\_duong | <ann@example.com>    |
| 2        | reuben\_o  | <reuben@example.com> |
| 3        | carmen\_s  | <carmen@example.com> |
| 4        | ben\_s     | <ben@example.com>    |

**`posts` table:**

| post\_id | title             | user\_id |
| -------- | ----------------- | -------- |
| 1        | My First Post     | 1        |
| 2        | Learning SQL      | 1        |
| 3        | Postgres Tips     | 2        |
| 4        | Why I Love Coding | 3        |
| 5        | Advanced Joins    | 2        |

Notice: `ben_s` (user\_id 4) has no posts. This will matter when we compare INNER JOIN and LEFT JOIN.

With two separate tables, you can answer questions about each one independently:

```sql
SELECT * FROM users;
SELECT * FROM posts;
```

But what about questions that cross the boundary between tables?

* What posts did `ann_duong` write?
* Who wrote the post titled `'Advanced Joins'`?
* How many posts has each user written?

None of these can be answered from a single table. `posts` knows the `user_id` of each post's author, but not their username — that lives in `users`. To answer them, you need a JOIN.

## JOINs

JOIN statements in SQL use data across multiple tables. There are multiple types of JOINs that combine tables in slightly different ways but the two that are used most often are `LEFT JOIN` and `INNER JOIN`.

![Copyright C.L. Moffat 2008](/files/7iOWSsY7QqthSbhhhWlN)

### INNER JOIN — Only Matching Rows

Suppose we wanted to see all users who made a post, excluding those who did not.

`INNER JOIN` combines rows from two tables where the join condition is true. Rows with no match on either side are excluded from the result.

```sql
SELECT *
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id;
```

The `ON` keyword defines the join condition.

Result:

| user\_id | username   | email                | post\_id | title             | user\_id |
| -------- | ---------- | -------------------- | -------- | ----------------- | -------- |
| 1        | ann\_duong | <ann@example.com>    | 1        | My First Post     | 1        |
| 1        | ann\_duong | <ann@example.com>    | 2        | Learning SQL      | 1        |
| 2        | reuben\_o  | <reuben@example.com> | 3        | Postgres Tips     | 2        |
| 3        | carmen\_s  | <carmen@example.com> | 4        | Why I Love Coding | 3        |
| 2        | reuben\_o  | <reuben@example.com> | 5        | Advanced Joins    | 2        |

`ben_s` does not appear — they have no posts, so there is no matching row in `posts`.

Select specific columns and qualify names with `table.column` when both tables share a column name:

```sql
SELECT
  users.username,
  posts.post_id,
  posts.title
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id;
```

Result:

| username   | post\_id | title             |
| ---------- | -------- | ----------------- |
| ann\_duong | 1        | My First Post     |
| ann\_duong | 2        | Learning SQL      |
| reuben\_o  | 3        | Postgres Tips     |
| carmen\_s  | 4        | Why I Love Coding |
| reuben\_o  | 5        | Advanced Joins    |

{% hint style="info" %}
`JOIN` without a type is an alias for `INNER JOIN`. Always write `INNER JOIN` explicitly — the type is part of the meaning. When you learn `LEFT JOIN`, `RIGHT JOIN`, and `FULL JOIN`, you'll already be in the habit of naming the type on every join.
{% endhint %}

### INNER JOIN Practice

For each of the questions below, use a SQL query with `INNER JOIN` and specific column names to find the answer:

<details>

<summary><strong>Q: What are the titles of all posts written by <code>ann_duong</code>?</strong></summary>

```sql
SELECT posts.title
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id
WHERE users.username = 'ann_duong';
```

</details>

<details>

<summary><strong>Q: Which user wrote the post titled <code>'Advanced Joins'</code>?</strong></summary>

```sql
SELECT users.username
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id
WHERE posts.title = 'Advanced Joins';
```

</details>

<details>

<summary><strong>Q: How many posts has <code>reuben_o</code> written?</strong></summary>

```sql
SELECT COUNT(*) AS post_count
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id
WHERE users.username = 'reuben_o';
```

</details>

### LEFT JOIN — All Rows

`INNER JOIN` only returns rows where both tables have a match. Users with no posts are silently excluded. Sometimes that's what you want — but when you need to include everyone regardless of whether they have related rows, use `LEFT JOIN`.

`LEFT JOIN` returns **all rows from the left table**. For rows with no match in the right table, the right-side columns come back as `NULL`.

```sql
SELECT
  users.username,
  posts.post_id,
  posts.title
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id;
```

Result:

| username   | post\_id | title             |
| ---------- | -------- | ----------------- |
| ann\_duong | 1        | My First Post     |
| ann\_duong | 2        | Learning SQL      |
| reuben\_o  | 3        | Postgres Tips     |
| carmen\_s  | 4        | Why I Love Coding |
| reuben\_o  | 5        | Advanced Joins    |
| ben\_s     | NULL     | NULL              |

`ben_s` now appears with `NULL` for all `posts` columns.

### LEFT JOIN Practice

<details>

<summary><strong>Q: How would you use a <code>LEFT JOIN</code> to find all users who have </strong><em><strong>not</strong></em><strong> written any posts?</strong></summary>

When a `LEFT JOIN` finds no match in the right table, the right-side columns are `NULL`. Filter for exactly those rows:

```sql
SELECT users.username
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
WHERE posts.post_id IS NULL;
```

`posts.post_id IS NULL` is true only for users with no matching post row.

</details>

## GROUP BY with JOINs

You already know `GROUP BY` from the aggregates lesson. If we wanted to see the post count for each `user_id`, we could write:

```sql
SELECT
  user_id,
  COUNT(posts.post_id) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC;
```

But if we wanted to also include the `username` which lives in the `users` table, we need to use a JOIN:

```sql
SELECT
  users.user_id,
  users.username,
  COUNT(posts.post_id) AS post_count
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id, users.username
ORDER BY post_count DESC;
```

Here are some things to note:

* We are grouping by both `users.user_id` and `users.username`
* `COUNT(posts.post_id)` counts non-NULL values, so users with no posts get `0`. Using `COUNT(*)` instead would count the NULL row as 1, giving the wrong answer for users with no posts.

### Smart GROUP BY with Primary Keys

In standard SQL, every non-aggregate column in `SELECT` must appear in `GROUP BY`. That would mean listing both `users.user_id` and `users.username` in the clause.

Postgres relaxes this rule: **when you `GROUP BY` a primary key, you can include other columns from that same table in `SELECT` without listing them in `GROUP BY`**. Postgres knows that if you've grouped by `user_id`, `username` is uniquely determined by it — there can only be one username per group.

```sql
-- Standard SQL: must list every selected column
SELECT users.user_id, users.username, COUNT(posts.post_id) AS post_count
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id, users.username;

-- Postgres smart GROUP BY: listing only the primary key is enough
SELECT users.user_id, users.username, COUNT(posts.post_id) AS post_count
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id;
```

{% hint style="info" %}
The smart `GROUP BY` optimization applies to the table whose primary key you're grouping by. Columns from the *joined* table (e.g., `posts`) still need to be inside an aggregate function or explicitly listed in `GROUP BY`.
{% endhint %}

<details>

<summary><strong>Q: Write a query that returns each user's <code>user_id</code>, <code>username</code>, and <code>email</code> along with the number of posts they've written. Include users with zero posts, ordered by post count descending.</strong></summary>

```sql
SELECT
  users.user_id,
  users.username,
  users.email,
  COUNT(posts.post_id) AS post_count
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id
ORDER BY post_count DESC;
```

Grouping by `users.user_id` (the primary key) allows `users.username` and `users.email` in `SELECT` without listing them in `GROUP BY`.

</details>

## Many-to-Many Relationships

### Association Tables and Three-Way JOINs

Some relationships are many-to-many. In `social_db`, a post can have many tags, and a tag can appear on many posts. This is the same pattern as `enrollments` connecting `students` and `classes` in the previous lesson — a many-to-many relationship needs an association table because there's no place to put a single foreign key that would capture both sides.

The four tables in `social_db` and how they connect:

* `users` — one row per user; independent table
* `posts` — one row per post; references `users` via `user_id` (the one-to-many you've been working with)
* `tags` — one row per tag (`sql`, `databases`, `beginner`, etc.); independent table
* `post_tags` — one row per post-tag pairing; holds `post_id` and `tag_id` as foreign keys (the association table)

**`tags` table:**

| tag\_id | name       |
| ------- | ---------- |
| 1       | javascript |
| 2       | sql        |
| 3       | databases  |
| 4       | beginner   |

**`post_tags` table:**

| post\_tag\_id | post\_id | tag\_id |
| ------------- | -------- | ------- |
| 1             | 1        | 4       |
| 2             | 2        | 2       |
| 3             | 2        | 3       |
| 4             | 3        | 2       |
| 5             | 3        | 3       |
| 6             | 5        | 2       |

To query across a many-to-many relationship, JOIN through the association table:

```sql
-- All tags on a specific post
SELECT posts.title, tags.name AS tag
FROM posts
  INNER JOIN post_tags ON posts.post_id = post_tags.post_id
  INNER JOIN tags      ON post_tags.tag_id = tags.tag_id
WHERE posts.title = 'Learning SQL';
```

```sql
-- All posts with a given tag, including the author's username
SELECT posts.title, users.username
FROM posts
  INNER JOIN post_tags ON posts.post_id = post_tags.post_id
  INNER JOIN tags      ON post_tags.tag_id = tags.tag_id
  INNER JOIN users     ON posts.user_id = users.user_id
WHERE tags.name = 'sql';
```

<details>

<summary><strong>Q: Why can't you represent a many-to-many relationship with just a foreign key on one of the two main tables?</strong></summary>

A foreign key column holds one value per row — it can reference exactly one row in the other table. If you put `tag_id` on `posts`, a post could only have one tag. If you put `post_id` on `tags`, a tag could only belong to one post.

Neither captures "many on both sides." The association table solves this by giving each post-tag pairing its own row, allowing any post to have any number of tags and any tag to appear on any number of posts.

</details>

<details>

<summary><strong>Q: How many posts use the <code>'sql'</code> tag? Write the query.</strong></summary>

```sql
SELECT COUNT(DISTINCT post_tags.post_id) AS post_count
FROM tags
  INNER JOIN post_tags ON tags.tag_id = post_tags.tag_id
WHERE tags.name = 'sql';
```

</details>

## Challenge: JOIN Queries

Write each query yourself before opening the solution.

<details>

<summary><strong>Q: List every user's <code>username</code> alongside the titles of their posts. Users with no posts should not appear.</strong></summary>

```sql
SELECT users.username, posts.title
FROM users
  INNER JOIN posts ON users.user_id = posts.user_id;
```

</details>

<details>

<summary><strong>Q: List every user's <code>username</code> and their post count. Include users with zero posts. Order by post count descending.</strong></summary>

```sql
SELECT users.username, COUNT(posts.post_id) AS post_count
FROM users
  LEFT JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id
ORDER BY post_count DESC;
```

</details>

<details>

<summary><strong>Q: Which posts are tagged <code>'databases'</code>? Return the post title and the author's <code>username</code>.</strong></summary>

```sql
SELECT posts.title, users.username
FROM posts
  INNER JOIN post_tags ON posts.post_id = post_tags.post_id
  INNER JOIN tags      ON post_tags.tag_id = tags.tag_id
  INNER JOIN users     ON posts.user_id = users.user_id
WHERE tags.name = 'databases';
```

</details>