Skip to content

JOINs

Combine rows from two or more tables using INNER, LEFT, RIGHT, and FULL joins.

What you'll learn

  • Why joins exist and how they combine data from multiple tables
  • INNER JOIN — only matching rows from both tables
  • LEFT JOIN — all rows from the left table, matched or not
  • RIGHT JOIN and FULL OUTER JOIN
  • Joining on the correct keys and avoiding Cartesian products

Concept

Why Joins Exist

Data in a relational database is split across multiple tables to avoid redundancy. Joins reassemble that data for querying. A join combines rows from two tables based on a related column — usually a foreign key. The general syntax is SELECT ... FROM table_a JOIN table_b ON table_a.col = table_b.col.

INNER JOIN

INNER JOIN returns only rows that have matching values in both tables. If a user has no orders, that user does not appear in the result. This is the most common join type — if you write just JOIN, it defaults to INNER.

LEFT JOIN

LEFT JOIN (or LEFT OUTER JOIN) returns every row from the left table, and the matching rows from the right table. If there is no match, the right table's columns are filled with NULL. This is perfect for "list all users and their orders, if any" — you see users with zero orders too.

RIGHT and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table. FULL OUTER JOIN returns all rows from both tables; unmatched columns are NULL on either side. These are less common because you can usually rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.

Avoiding Cartesian Products

If you omit the ON clause (or write a condition that is always true), you get a Cartesian product — every row in table A joined with every row in table B. For two 1000-row tables that is 1,000,000 rows. Always specify an ON clause with the correct join key.

Example

              -- INNER JOIN: only users who have placed orders
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.name;

-- LEFT JOIN: all users, with NULL for those without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY order_count DESC;

-- Join three tables: users -> orders -> order_items
SELECT u.name, o.id, oi.product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id;
            

INNER JOIN for matching rows, LEFT JOIN to include users with no orders, and a three-table join chaining users → orders → order_items.

Try it

  • SQL Formatter

    Format multi-table JOIN queries for readability.

  • JSON Diff

    Compare query results from different join types.

Common mistakes

The mistake

Forgetting the ON clause

The fix

Without ON, you get a Cartesian product (every row × every row). Always specify the join condition, e.g. ON a.id = b.a_id.

The mistake

Using INNER JOIN when you need LEFT JOIN

The fix

If you need all rows from one table even when there is no match in the other, use LEFT JOIN. INNER JOIN silently drops unmatched rows.

Practice JOINs

13 exercises

Practice JOINs

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors