SQL Joins: Inner, Left, Self Joins, and Explicit Syntax

A join combines rows from more than one table. That is the plain version, and it is the only version worth starting with.

Relational data usually lives in separate tables because the facts are separate: users, orders, line items, products, and categories each describe a different part of the system. Joins let you ask a question that crosses those boundaries without copying the same data into every table.

The Basic Join

SELECT
    orders.id,
    users.email,
    orders.total_cents
FROM orders
JOIN users ON users.id = orders.user_id;

The ON clause describes the relationship between the tables. In this case, every order points at the user who owns it.

PostgreSQL's tutorial notes that older SQL can list tables in FROM and put the relationship in WHERE. The result can be the same, but explicit JOIN ... ON is easier to read because the relationship sits where the reader expects it.

Use the explicit form unless you have a very specific reason not to.

Inner Join

JOIN by itself means inner join in ordinary usage. It returns rows where the join condition matches on both sides.

SELECT
    users.email,
    orders.id AS order_id
FROM users
JOIN orders ON orders.user_id = users.id;

If a user has no orders, that user disappears from this result because the inner join only returns matched rows from both sides.

Left Join

Use a left join when you want every row from the left table, even if the right table has no match.

SELECT
    users.email,
    orders.id AS order_id
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

Now a user with no orders still appears, with NULL for the order columns. That distinction matters in reporting because "no matching row" and "matching row with a zero value" are not the same fact.

Left-join Filters

The fastest way to break a left join is to add a WHERE condition on the right-hand table without thinking through the NULL case. The query still runs, and that is part of the danger.

This query looks like it should list every user and show paid orders when they exist:

SELECT
    users.email,
    orders.id AS order_id
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.status = 'paid';

That query does not keep every user. The WHERE clause runs after the join result has been built, so users without matching orders have NULL in orders.status. The condition removes them, and the query behaves like an inner join for this report.

If the status filter belongs to the relationship, keep it in the ON clause:

SELECT
    users.email,
    orders.id AS paid_order_id
FROM users
LEFT JOIN orders
    ON orders.user_id = users.id
   AND orders.status = 'paid';

Now every user remains in the result. Users without a paid order get NULL for the order columns, which is the fact the report needs to preserve.

There are times when the WHERE version is correct. If the business question is "which users have a paid order," an inner join or a filtered left join can both make sense. If the question is "show me all users and whether they have a paid order," the filter belongs inside the join condition.

The Duplicate-row Trap

A join returns matched pairs, so it can multiply rows when one side has many matches. If one user has five orders, this query returns five rows for that user:

SELECT
    users.id,
    users.email,
    orders.id AS order_id
FROM users
JOIN orders ON orders.user_id = users.id;

That result is correct because the query asked for one row per matched pair.

The trouble starts when a report expects one row per user but joins into a table with many related rows. Counts inflate, totals duplicate, and someone spends an afternoon blaming the charting library.

Use aggregation when you want one row per parent:

SELECT
    users.id,
    users.email,
    COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.email;

That query still has one important choice hidden inside it: COUNT(orders.id) counts only matching order rows, while COUNT(*) would count the left-side user row even when no order exists. For a left-joined count, count a non-null column from the right table unless you deliberately want to count parent rows.

The same problem shows up with sums. If you join orders to order_items and then sum an order-level total, the order total appears once per line item. Sum the line-item values, or aggregate the child table first and join the smaller result back to the parent:

SELECT
    users.id,
    users.email,
    COALESCE(order_totals.total_cents, 0) AS total_cents
FROM users
LEFT JOIN (
    SELECT
        orders.user_id,
        SUM(orders.total_cents) AS total_cents
    FROM orders
    GROUP BY orders.user_id
) AS order_totals ON order_totals.user_id = users.id;

I like this pattern because it makes the grain of the result visible. The subquery has one row per user. The outer query also has one row per user. When those two statements disagree, the query is probably hiding a duplicate-row bug.

Qualify Columns

Once a query has more than one table, qualify the columns:

SELECT
    users.email,
    orders.created_at
FROM users
JOIN orders ON orders.user_id = users.id;

The version below asks the reader to infer too much:

SELECT email, created_at
FROM users
JOIN orders ON user_id = id;

It can also break later if another table gets a column with the same name.

Aliases are useful when the table names get longer, but keep them readable. orders AS o and order_items AS oi are fine in a short query. A six-table report full of a, b, c, and d makes review harder than it needs to be.

SELECT
    u.email,
    o.created_at,
    oi.sku
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
JOIN order_items AS oi ON oi.order_id = o.id;

The alias should reduce noise without hiding the relationship. If you have to scroll back up to remember what x1 means, the alias has stopped helping.

The USING Clause

SQL also supports USING when both joined tables have the same column name:

SELECT
    orders.id,
    order_items.sku
FROM orders
JOIN order_items USING (order_id);

That can be tidy when the schema uses consistent foreign-key names. It is also easier to abuse than ON, because it hides both sides of the equality. I prefer ON in teaching examples and code reviews because it says exactly which table owns each column.

Use USING when the relationship is obvious and the team already uses that convention. Use ON when the column names differ, when the query crosses several tables, or when a reviewer needs every relationship spelled out.

Self Joins

A self join joins a table to itself. The usual example is an employee table where each employee row can point at another employee as a manager:

SELECT
    employee.name AS employee_name,
    manager.name AS manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.id = employee.manager_id;

This is still an ordinary join with clearer labels. The aliases are what make it readable. One copy of the table is being used as the employee side, and the other copy is being used as the manager side.

Self joins also show up in category trees, threaded comments, referrals, and replacement records. They are useful when a row relates to another row of the same kind. They are painful when the relationship is really a hierarchy with arbitrary depth, because one join only walks one step. Recursive queries are a better fit once you need all descendants rather than the immediate parent.

Cross Joins

A cross join returns every combination of rows from the left and right tables. If one table has three sizes and another has four colors, the result has twelve rows:

SELECT
    sizes.name AS size_name,
    colors.name AS color_name
FROM sizes
CROSS JOIN colors;

Cross joins are legitimate for generating combinations, calendar grids, test data, or pricing matrices. They are also what you accidentally create when you forget a join condition.

This old-style query has no relationship between the tables:

SELECT
    users.email,
    orders.id
FROM users, orders;

If there are 10,000 users and 80,000 orders, that result starts at 800 million paired rows before any later filter rescues it. That kind of mistake is why explicit JOIN ... ON is more than style. It keeps the relationship visible at the point where the row multiplication begins.

Many-to-many Joins

A many-to-many relationship needs a join table. Products can have many tags, and tags can belong to many products, so neither table should store a comma-separated list of the other.

CREATE TABLE product_tags (
    product_id bigint NOT NULL,
    tag_id bigint NOT NULL,
    PRIMARY KEY (product_id, tag_id)
);

The query joins through that relationship table:

SELECT
    products.name,
    tags.name AS tag_name
FROM products
JOIN product_tags ON product_tags.product_id = products.id
JOIN tags ON tags.id = product_tags.tag_id;

The join table is part of the model. It is the fact that "this product has this tag." Once you need metadata on the relationship, such as who added the tag or when it was approved, the join table has an obvious home for those columns too.

This is where schema design basics and joins meet. A clean many-to-many table makes the query boring, which is usually the best outcome a database can give you.

Anti-joins and Missing Rows

Sometimes the useful result is the missing relationship. A left join plus IS NULL finds rows with no match on the right:

SELECT
    users.id,
    users.email
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

That reads as "users with no orders." It works because matched users have an order id, while unmatched users have NULL for the right-hand columns.

The same idea can often be written with NOT EXISTS:

SELECT
    users.id,
    users.email
FROM users
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = users.id
);

I reach for NOT EXISTS when the intent is absence, because it says the quiet part directly. The planner may produce a similar strategy either way, but the next developer reading the query gets a cleaner signal.

Join Order and Indexes

The database does not always execute the join in the same mental order you wrote it. The planner chooses a strategy based on statistics, indexes, and estimated row counts, which is exactly what you want from a mature database. It also means you should index columns that are regularly used to connect tables.

For this query, the planner has to find the user's orders and then load the matching line items:

SELECT
    orders.id,
    order_items.sku
FROM orders
JOIN order_items ON order_items.order_id = orders.id
WHERE orders.user_id = 42;

The likely supporting indexes would start with the relationship and filter columns:

CREATE INDEX orders_user_id_idx ON orders (user_id);
CREATE INDEX order_items_order_id_idx ON order_items (order_id);

Indexes are not free, so read indexes and query optimization before adding them everywhere.

For join performance, start with the columns in ON and the columns in WHERE. A foreign key column that is searched constantly usually deserves an index. A column that is only written and rarely searched may not.

Then look at the plan rather than guessing. PostgreSQL's EXPLAIN output will show whether the database scans a whole table, uses an index, builds a hash table, or loops through matches. Those words matter less than the shape: which table is being reduced first, how many rows are estimated, and whether the estimate is close to reality.

EXPLAIN
SELECT
    orders.id,
    order_items.sku
FROM orders
JOIN order_items ON order_items.order_id = orders.id
WHERE orders.user_id = 42;

If the estimate is wildly wrong, the issue may be stale statistics, a missing index, or a predicate the planner cannot reason about well. If the estimate is right and the query is still slow, the table shape or reporting requirement may need work. An index is a tool, not a confession booth for a confused schema.

Joins and Data Ownership

Every join says something concrete about ownership. orders.user_id says an order belongs to a user. order_items.order_id says a line item belongs to an order. A nullable foreign key says the relationship may be absent, and that absence has to mean something.

When a query feels awkward, check whether the schema is saying the truth plainly:

• If a value repeats across rows as text, it may need its own table.
• If a join requires matching on names, dates, or descriptions, the relationship probably lacks a real key.
• If a report needs one row per customer but every query explodes into children, aggregate at the child boundary before joining back.
• If PHP code keeps patching missing relationships after the query runs, the database may be missing a constraint.

That last point is where old LAMP habits can still teach something useful. We used to write more of this by hand, which meant bad relationships became visible fast. Modern frameworks can hide the join behind a relationship method, but the database still has to do the same work.

A Review Checklist for Joins

When I review a join-heavy query, I usually walk it in this order:

1. Identify the grain of the result: one row per user, order, line item, tag, or report period.
2. Check each ON clause and name the relationship in plain English.
3. Look for filters on right-hand tables after a left join.
4. Check whether any join can multiply rows before an aggregate.
5. Qualify columns and keep aliases readable.
6. Confirm the join columns have sensible indexes for the workload.
7. Run EXPLAIN once the query matters to performance.

That checklist catches most join bugs before they turn into application bugs. It also gives AI-generated SQL a fair review: the query may be syntactically valid and still join the wrong facts.

Joins in PHP Apps

In a PHP app, joins usually appear after the first version ships. The first screen lists notes, the next screen needs the note author's email, and then the admin table needs counts. Suddenly the application is relational whether the code wanted to admit it or not. I went through that progression long before ORMs existed to soften the landing, building queries directly in phpMyAdmin until I understood exactly which foreign key column connected the tables. There was no abstraction layer to guess on your behalf.

That is why PHP and MySQL with PDO should lead into joins quickly. Prepared statements protect the values entering SQL, while joins decide whether the data you read is actually the data you meant.

If the query changes more than one table, read transactions and ACID next. If every join feels like a workaround, go back to schema design basics. Joins are where the relational model stops being vocabulary and starts telling you whether the application has modeled the work honestly.

Frequently Asked Questions

What is the difference between a JOIN and a UNION?

A join combines columns from different tables into wider rows, matching them on a condition. A union stacks the rows of two result sets that already have the same column count and compatible types. Use a join to add related fields, and a union to append more rows.

Does MySQL support FULL OUTER JOIN?

MySQL does not. The usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION, which also removes the rows counted twice. PostgreSQL, SQL Server, Oracle, and SQLite from version 3.39 support FULL OUTER JOIN directly.

Is a LEFT JOIN slower than an INNER JOIN?

Not inherently. A left join can cost more because the database must keep unmatched left-hand rows, which sometimes narrows the join orders it can choose. On indexed join columns the difference is often small. Check EXPLAIN rather than assuming one is faster.

Should you use a subquery or a join?

Use a join when the result needs columns from both tables. Use a subquery when you only need to test membership or produce a single value. Optimisers frequently rewrite one form into the other, so choose the one that states the intent more clearly, then confirm with EXPLAIN.

Why is RIGHT JOIN used so rarely?

A right join keeps unmatched rows from the second table rather than the first. Every right join can be written as a left join by swapping the table order, and reading left to right matches how most queries are built, so teams standardise on LEFT JOIN.

How many tables can you join in a single query?

MySQL allows up to 61 tables in one join and other engines allow more, so the limit is rarely the problem. The practical constraint is the optimiser's ability to find a good plan. A query joining more than roughly eight tables usually deserves a second look.

Do you need a foreign key to join two tables?

No. A join matches values in the columns you name, and any comparable columns work. A foreign key constrains the data so those matches stay valid. Without one the join still runs, but nothing prevents orphaned rows from quietly changing what the query returns.

Self-Check

1. Which join keeps rows from the left table that have no match on the right?
2. What does a WHERE orders.status = 'paid' condition do to the unmatched rows of a LEFT JOIN?
3. Why can a join return more rows than the table you started from?
4. What does USING (customer_id) require that ON a.customer_id = b.customer_id does not?
5. Which join finds customers with no orders, and what condition makes it work?
6. Why does qualifying every column matter once a query joins three tables?

Answers

1. LEFT JOIN. Unmatched right-hand columns come back as NULL.
2. It removes them. A null-rejecting condition fails against the NULLs, so the query quietly behaves like an INNER JOIN. When the unmatched left rows must remain, put the filter in the ON clause.
3. One row can match many. A one-to-many relationship repeats the left-hand row once per match, which is the duplicate-row trap.
4. The same column name in both tables. USING also returns that column once rather than twice.
5. An anti-join. LEFT JOIN the orders table, then filter for IS NULL on a column that can never be null in a real match.
6. Ambiguity. Two tables can share a column name, and a reader cannot tell which table a bare name belongs to.