SQL Joins Explained: INNER, LEFT, RIGHT, and FULL JOIN With Examples

A SQL join combines rows from two or more tables using a column they share, such as a customer ID. An INNER JOIN keeps only the rows that match in both tables. A LEFT JOIN keeps every row from the first table and fills in blanks (NULLs) where the second table has no match. A RIGHT JOIN does the same from the other side, and a FULL OUTER JOIN keeps everything from both tables. If you understand those four ideas and know how to check your row counts, you can handle most of the joins a data analyst writes at work.

This guide explains each join type with one small example, shows the mistakes that quietly break reports, and gives you a checklist to use before you trust a joined result.

Why joins matter for data analysts

Business data almost never lives in one table. Orders sit in one place, customers in another, products and refunds somewhere else. Almost every useful question, like "which customers bought twice last quarter?" or "what is revenue by product category?", needs at least one join. That is why joins show up in nearly every SQL interview for analyst roles and why they are one of the first things to practice after basic SELECT and WHERE.

Joins are also where many reporting errors start. A join that silently drops rows makes totals too low. A join that duplicates rows makes totals too high. Neither one throws an error, so the only defense is knowing what each join does.

The example tables

Imagine two small tables. customers has four people: Ana (id 1), Ben (id 2), Carla (id 3), and Dev (id 4). orders has four orders: two from Ana (customer_id 1), one from Carla (customer_id 3), and one with customer_id 9, a customer who no longer exists in the customers table. Ben and Dev have no orders.

We will join them on customers.id = orders.customer_id and see what each join type returns.

INNER JOIN: only the matches

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

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

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

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

Result: three rows. Two for Ana, one for Carla. Ben and Dev disappear because they have no orders, and the order from customer 9 disappears because there is no matching customer. Use INNER JOIN when you only care about records that exist on both sides, for example "orders with a valid customer."

LEFT JOIN: everything from the first table

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

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

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

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

Result: five rows. Ana twice, Carla once, plus Ben and Dev with NULL in the order columns. The orphan order from customer 9 is still missing because it is not in the left table. LEFT JOIN is the join analysts use most, because questions usually start from one main entity ("all customers," "all products") and add information when it exists.

A useful pattern: add WHERE o.order_id IS NULL to find customers with no orders. This is often called an anti-join, and it answers questions like "which leads never converted?"

RIGHT JOIN: everything from the second table

A RIGHT JOIN keeps every row from the second table. Here it would return all four orders, including the one from customer 9 with a NULL name. In practice most analysts rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, because queries are easier to read when the main table comes first. Some databases, such as older versions of SQLite, did not support RIGHT JOIN at all, which is one more reason the habit stuck.

FULL OUTER JOIN: everything from both

A FULL OUTER JOIN returns every row from both tables: matches, customers without orders, and orders without customers. Here that is six rows. It is less common in daily reporting, but very useful for reconciliation, such as comparing a CRM export with a billing export to see which records exist in only one system. The PostgreSQL documentation on joined tables describes each join type precisely if you want the formal definitions.

Other joins you will see

• CROSS JOIN returns every combination of rows from both tables. Four customers times four orders gives sixteen rows. It is useful for building a grid, like every store for every date, before filling in sales.

• SELF JOIN joins a table to itself, for example matching employees to their managers when both live in the same table.

• Joins on multiple columns, such as ON a.store_id = b.store_id AND a.date = b.date, are common with daily or regional data.

The mistakes that break reports

1. Duplicated rows from one-to-many joins

If you join customers to orders and then sum a customer-level field, like a credit limit, each customer's value is counted once per order. Ana's limit would be counted twice. Before joining, ask what one row means in each table (one customer, one order, one order line). If the grain changes, aggregate first, then join.

2. Filtering a LEFT JOIN in the WHERE clause

Writing LEFT JOIN orders o ON ... WHERE o.amount > 100 removes the NULL rows, so your LEFT JOIN quietly becomes an INNER JOIN. If you want to keep all customers and only attach large orders, move the condition into the ON clause: ON o.customer_id = c.id AND o.amount > 100.

3. Joining on dirty keys

IDs stored as text in one table and numbers in another, extra spaces, or different capitalization in email addresses all cause missed matches. Clean and standardize keys first. Our data cleaning guide with pandas covers the most common fixes.

4. Forgetting that NULL never equals NULL

Rows where the key is NULL will not match each other in a normal join. If missing keys matter, handle them on purpose instead of assuming they will line up.

A five-step checklist before you trust a join

1. Know the grain. Write down what one row means in each table.

2. Count before and after. Run COUNT(*) on the main table and on the joined result. A LEFT JOIN should never have fewer rows than its left table; more rows means a one-to-many relationship.

3. Check key uniqueness. SELECT id, COUNT(*) FROM customers GROUP BY id HAVING COUNT(*) > 1 finds duplicate keys that will multiply rows.

4. Look for unmatched rows. Use the anti-join pattern to see what did not match and why.

5. Spot-check one record. Pick one customer and confirm the joined numbers by hand.

How to practice joins

Use a free database like SQLite or PostgreSQL (the PostgreSQL joins tutorial is a good companion) with a small public dataset, and write the same question with each join type to see how the results change. Then move on to joins combined with GROUP BY and, later, SQL window functions. If you are planning your study time, our guide on how long it takes to learn SQL breaks it down by goal. Joins are also a staple of data analyst interview questions, so practicing them out loud pays off twice.

Frequently asked questions

What is the difference between LEFT JOIN and INNER JOIN?

INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the first table and adds matching data from the second, leaving NULLs where there is no match.

Is JOIN the same as INNER JOIN?

Yes. In standard SQL, writing just JOIN means INNER JOIN.

Why did my join return more rows than expected?

Usually because the key is not unique in one of the tables, so each row matches several rows on the other side. Check key uniqueness and aggregate before joining if needed.

Learn SQL with live feedback

If you want to practice joins and real analysis with instructors who do this work professionally, Coderhouse offers live online courses with one class per week:

• Data Analytics Course: 11 weeks for career switchers and data-driven professionals, with two weeks dedicated to SQL foundations and SQL for real analysis, plus Python, statistics, and AI-assisted analytics.

• AI Engineering Course: 8 weeks for experienced developers moving into AI engineering, covering retrieval, RAG, agents, and evaluation.