SQL Joins Demystified: A Beginner's Guide to Combining Data from Multiple Tables

A single table rarely has the whole answer. Your orders table knows which customer placed each order — but only by a customer_id. The customer’s name and city live in a separate customers table. Joins are how you bring them together: combine rows from two tables on a column they share, and suddenly you can ask “what did customers in London order?” in one query.

This guide is hands-on. The playground runs a real SQL engine in your browser against related customers and orders tables — run the joins, change them, and watch the rows line up.

What a join does

A join matches rows from two tables using a related column — here, customers.id and orders.customer_id. Every place those values line up, SQL stitches the two rows into one, giving you columns from both tables side by side.

Two ingredients make it work:

• Table aliases (customers c, orders o) give each table a short handle.
• The ON clause says how rows relate: ON c.id = o.customer_id.

Because both tables have an id column, you qualify columns with the alias (c.name, o.product) so SQL knows which table you mean. Skip the qualifier on a shared column and you’ll get an “ambiguous column” error — a helpful nudge, not a mystery.

INNER JOIN — only the matches

An INNER JOIN returns only rows that have a match in both tables:

SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Every order belongs to a real customer, so this returns one row per order, each carrying its customer’s name. A customer who never ordered anything simply doesn’t appear — no match, no row. (The keyword is INNER JOIN, but plain JOIN means the same thing.)

LEFT JOIN — keep everything on the left

A LEFT JOIN returns all rows from the left table, plus matches from the right. Where there’s no match, the right table’s columns come back NULL:

SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Now every customer appears — even one who has never placed an order shows up once, with NULL in the product column. That’s the whole point of a LEFT JOIN: don’t drop the left rows just because the right side has nothing to offer.

That makes LEFT JOIN the tool for “find the ones with nothing” questions. Add WHERE o.id IS NULL and you keep only the customers with no matching order:

Run the LEFT JOIN, then “Customers with NO orders” — the customer who never bought anything falls out the bottom. Switch to the INNER JOIN and notice the total row count drops: that customer is simply gone.

The other join types

INNER and LEFT cover the vast majority of real queries. The rest, briefly:

• RIGHT JOIN — the mirror of LEFT: all rows from the right table, matches from the left. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, which is why most people just use LEFT.
• FULL JOIN — everything from both sides, with NULLs wherever a match is missing.
• CROSS JOIN — every row of one table paired with every row of the other (no ON), producing all combinations. Rarely what you want.

The dedicated guide understanding SQL join types runs each of these live and shows exactly how the row counts differ.

Practice

Frequently asked questions

What is a join in SQL? A join combines rows from two (or more) tables based on a related column, letting a single query return columns from each table together.

What’s the difference between INNER JOIN and LEFT JOIN? INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, filling the right side with NULL where there’s no match.

How do I find rows in one table with no match in another? LEFT JOIN the second table and filter WHERE right_table.key IS NULL. Those are the left rows that found no partner.

Why do I get an “ambiguous column” error? Both tables have a column with that name, so SQL can’t tell which you mean. Qualify it with the table alias — c.id instead of just id.

From joining tables to joining systems

A join stitches two tables in one database. The harder version — stitching a CSV to a Postgres table to an API response — is what ET1 is built for: its Joiner node does INNER/LEFT/RIGHT joins visually across any sources, with a live preview of the combined rows.

Keep going: get the full tour of join types, revisit where joins live in the FROM clause, or filter your joined results with the WHERE clause.