Suggest an edit

Improve this article

Refine the answer for “How does JOIN differ from a subquery?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

`JOIN` and a **subquery** are both ways to get data from several tables,
but they work **differently** and fit different situations.

### 1. JOIN combines tables "widthwise"

`JOIN` physically merges rows from two tables on a shared field and returns **one combined table**.
All the needed data shows up at once in a single query.

```sql
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
```

Each row contains data from both `users` and `orders`.

**When to use it:**

- you need to show related data together (e.g. a user + an order);
- speed matters, `JOIN` is usually faster;
- you need several fields from both tables.

### 2. A subquery pulls data "depthwise"

A subquery is **a query inside another query**.
It runs separately, and its result is used as a condition or a table.

```sql
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
```

First the subquery in the parentheses runs (finding user_id values with large orders),
then the outer query selects the names of those users.

**When to use it:**

- you need an intermediate result (e.g. a sum, a maximum, a list of ids);
- there's no need to join data row-to-row;
- readability or logical isolation of the query's parts matters.

### 3. The key difference

| Trait | JOIN | Subquery |
|---|---|---|
| What it does | Combines tables by fields | Inserts one query's result into another |
| How it works | "Widthwise", joins rows | "Depthwise", uses nested results |
| Returns | One combined table | An intermediate value or set |
| When to use | To display related data | To filter, aggregate, check conditions |

**Summary:**

- `JOIN` connects tables directly, showing data from both.
- A subquery works like a "built-in filter" or computation.
- Both tools solve similar problems, but `JOIN` is for combining rows,
while a subquery is for logical checks and intermediate computations.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts331 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.