Skip to main content
Blog

Understanding SQL Join Types: Unlocking the Power of Data Integration

INNER, LEFT, RIGHT, and FULL joins, explained by the rows they keep. Run each one live in your browser on related tables and watch the row counts change.

· Dev3lop Team

Every SQL join combines two tables on a related column. What separates the join types is a single question: what happens to rows that have no match on the other side? Keep them, or drop them — that choice is the difference between INNER, LEFT, RIGHT, and FULL.

The clearest way to feel the difference is to run all four on the same two tables and watch the row count move. That’s exactly what the playgrounds below do — a real SQL engine in your browser over related customers and orders tables. One customer in the data has never placed an order, which is what makes the join types diverge.

INNER JOIN — matches only

INNER JOIN — keep only the matches

INNER JOIN returns a row only when the join condition finds a match in both tables. A customer with no orders, or an order with no customer, is dropped.

Run the INNER JOIN above: 18 rows, one per order. The customer who never ordered anything is nowhere to be seen — no match, no row. This is the join you want when you only care about the connected data.

LEFT JOIN — keep every left row

LEFT JOIN keeps all rows from the left table and fills the right side with NULL when there’s no match:

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

Now the result is 19 rows — the same 18, plus one more: the customer with no orders, appearing once with NULL for product. That extra row is the difference between INNER and LEFT. Use LEFT JOIN whenever “the left table’s rows must all survive” — every customer, whether or not they’ve ordered.

LEFT JOIN — 19 rows, one with NULL

RIGHT JOIN — keep every right row

RIGHT JOIN is the mirror image: all rows from the right table, matches from the left. It’s most useful for reading a query the way the data flows. This keeps every customer by putting customers on the right:

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

That’s 19 rows again — every customer, including the one without orders. Notice you can always turn a RIGHT JOIN into a LEFT JOIN by swapping the two tables, which is why many teams standardize on LEFT and never write RIGHT at all.

RIGHT JOIN — the mirror of LEFT

FULL JOIN — keep everything from both

FULL OUTER JOIN (or just FULL JOIN) keeps all rows from both tables, filling in NULL on whichever side has no match:

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

It’s the union of LEFT and RIGHT. In this dataset every order belongs to a real customer, so the only unmatched row is the orderless customer — FULL returns the same 19 rows as the LEFT JOIN here. The difference shows up when both tables have orphans (say, an order whose customer was deleted): only FULL would keep both the customerless order and the orderless customer.

FULL JOIN — both sides, matched or not

Note: MySQL doesn’t support FULL JOIN directly — you emulate it with a LEFT JOIN UNION a RIGHT JOIN. PostgreSQL, SQL Server, Oracle, and SQLite (3.39+) support it natively.

The row counts, side by side

Same two tables, same ON condition — only the type changes:

Join typeKeepsRows here
INNER JOINrows matched in both18
LEFT JOINall customers + matched orders19
RIGHT JOIN (customers on right)all customers + matched orders19
FULL JOINeverything from both19

There’s also CROSS JOIN — every row of one table paired with every row of the other, with no ON at all. It produces all combinations (12 customers × 18 orders = 216 rows) and is rarely what you want; it’s mostly used to generate grids or test data.

Practice

Your turn

Return every customer's name alongside their order product — INCLUDING customers who have never ordered (their product should come back NULL). Select c.name and o.product.

Frequently asked questions

What are the types of SQL joins? INNER JOIN (matches only), LEFT JOIN (all left rows), RIGHT JOIN (all right rows), FULL OUTER JOIN (all rows from both), and CROSS JOIN (all combinations).

What’s the difference between INNER JOIN and LEFT JOIN? INNER returns only rows that match in both tables; LEFT also returns the unmatched rows from the left table, with NULL on the right.

When would I use a FULL OUTER JOIN? When you need every row from both tables, matched or not — for example, reconciling two lists to find records that exist in only one of them.

Does MySQL support FULL JOIN? Not directly. Emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. PostgreSQL, SQL Server, Oracle, and modern SQLite support FULL JOIN natively.

From join types to a join canvas

Choosing the right join is a modeling decision you make over and over. ET1 turns it into a visual Joiner node — pick INNER, LEFT, or RIGHT from a dropdown, wire two sources together, and watch the combined rows preview live across a CSV, a database, and an API at once.

Keep learning: start with the beginner joins guide, see where joins live in the FROM clause, or filter joined results with the WHERE clause.

← All posts