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

Joins are where most people learning SQL get stuck, because the syntax is easy but the results are not always what you expect. This guide has SQL joins explained with one tiny dataset, so you can see exactly which rows each join keeps and which it drops. It is the same approach we use in Omebly Academy’s SQL and Databases course: small tables first, then real business questions.

Quick answer: A join combines rows from two tables using a matching column, usually a key such as customer_id. INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table and fills missing matches with NULL. RIGHT JOIN does the same for the right table. FULL OUTER JOIN keeps every row from both. Most analyst work uses INNER and LEFT.

The sample tables

We will use two small tables from an imaginary online shop. Keep them in front of you while you read.

customers

customer_id	name	city
1	Asha	Kolkata
2	Rahul	Pune
3	Meera	Chennai
4	Imran	Dubai

orders

order_id	customer_id	amount
101	1	1200
102	1	450
103	2	900
104	5	300

Notice three things before we start. Asha (1) has two orders. Meera (3) and Imran (4) have no orders. Order 104 belongs to customer 5, who is not in the customers table (perhaps a deleted account or a data-entry error). Those three details are what make the joins behave differently.

INNER JOIN: only rows that match

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

name	order_id	amount
Asha	101	1200
Asha	102	450
Rahul	103	900

Three rows. Meera and Imran disappear because they have no orders. Order 104 disappears because there is no customer 5. Asha appears twice, once per order.

When to use it

Use INNER JOIN when a row only makes sense with a match on both sides, for example “revenue by customer city”, where an order without a known customer has no city to report. Writing just JOIN means the same thing as INNER JOIN.

LEFT JOIN: keep everything on the left

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

name	order_id	amount
Asha	101	1200
Asha	102	450
Rahul	103	900
Meera	NULL	NULL
Imran	NULL	NULL

Five rows. Every customer is kept. Where there is no order, the order columns are NULL. Order 104 is still missing, because it has no customer on the left side.

When to use it

LEFT JOIN is the workhorse of analysis. Use it whenever the left table is your list of “things that must all appear”: every customer, every product, every day of the month. A sales report that silently drops products with zero sales is a common and costly mistake, and a LEFT JOIN from the products table prevents it.

Turning NULLs into zeros

SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Meera and Imran now show 0 instead of NULL, which is what a manager reading the report expects.

RIGHT JOIN: keep everything on the right

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

name	order_id	amount
Asha	101	1200
Asha	102	450
Rahul	103	900
NULL	104	300

Every order is kept, including 104, which has no customer name. Meera and Imran are gone.

Why you rarely see it

A RIGHT JOIN B gives the same rows as B LEFT JOIN A. Most teams write everything as LEFT JOIN so queries read the same way: start from the main table and add details. Learn RIGHT JOIN so you can read other people’s code and answer interview questions, then mostly stick to LEFT.

FULL OUTER JOIN: keep everything from both

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

name	order_id	amount
Asha	101	1200
Asha	102	450
Rahul	103	900
Meera	NULL	NULL
Imran	NULL	NULL
NULL	104	300

Six rows: every match, plus unmatched rows from both sides.

When to use it

FULL OUTER JOIN is mainly a reconciliation tool. Comparing this month’s customer list with last month’s, or a bank statement with an invoice table, are classic uses: you want to see what is in one, the other, or both. PostgreSQL and SQL Server support it; MySQL does not, so there you combine a LEFT and a RIGHT JOIN with UNION. The PostgreSQL tutorial on joins covers the standard syntax.

All four joins side by side

Join	Rows in our example	Keeps unmatched left rows?	Keeps unmatched right rows?	Typical use
INNER	3	No	No	Facts that need both sides
LEFT	5	Yes	No	Reports that must list everything
RIGHT	4	No	Yes	Reading older code
FULL OUTER	6	Yes	Yes	Reconciliation, data checks

Two more joins worth knowing

Anti-join: “who has no match?”

Business questions often ask for what is missing: customers who never ordered, products never sold, students who never logged in. Use a LEFT JOIN and keep only the NULLs:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Result: Meera and Imran. NOT EXISTS gives the same answer and is often clearer:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Self-join: a table joined to itself

If an employees table has a manager_id column, joining the table to itself lists each employee next to their manager’s name. Give each copy a different alias (e and m) and it works like any other join.

Mistakes that quietly break your results

1. Duplicate rows from a one-to-many join

Asha has two orders, so any join from customers to orders gives her two rows. If you then join a third table, such as customer addresses with two rows each, Asha becomes four rows and her revenue doubles. Count rows before and after every join. If you need one row per customer, aggregate orders first in a CTE, then join.

2. A WHERE clause that cancels your LEFT JOIN

-- Looks like a LEFT JOIN, behaves like an INNER JOIN
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 500;

Meera and Imran have NULL amounts, and NULL > 500 is not true, so they vanish. If you want every customer plus only their large orders, move the condition into the join: ON o.customer_id = c.customer_id AND o.amount > 500.

3. Joining on NULL keys

NULL = NULL is not true in SQL, so rows with a NULL key never match anything. If a column has missing IDs, fix or flag them before you join. Our data cleaning checklist covers this step.

4. Joining on the wrong data type

Joining a text '001' to a number 1 either fails or matches nothing, depending on the database. Check that both columns have the same type, and trim stray spaces from text keys.

5. Forgetting the ON clause

Leave out the join condition (or write a comma between tables with no WHERE) and you get a cross join: every row paired with every row. With 4 customers and 4 orders that is 16 rows; with real tables it can be millions.

Practice questions

Use the tables above (or load your own into PostgreSQL) and try these before checking your answers:

1. List every customer with their number of orders, including zeros.
2. Find orders whose customer does not exist.
3. Show total revenue by city, but only for cities with at least one order.
4. List customers who have spent more than 1,000 in total.
5. Reconcile both tables: show every customer and every order, with a column saying “matched”, “no orders” or “unknown customer”.

If you can explain why each query returns the number of rows it does, you understand joins. For where joins fit in the bigger picture, read our guide on how to learn SQL for data analysis.

Joins checklist

• I know which table is my “must keep everything” table
• I counted rows before and after each join
• Filters on the right table sit in ON, not WHERE, when I need a LEFT JOIN
• Join keys have the same type and no unexpected NULLs
• I aggregate before joining when I need one row per entity

Learn it with Omebly

Joins are covered early in Omebly Academy’s SQL and Databases course, alongside subqueries, CTEs and window functions, using PostgreSQL as the main database with MySQL differences explained. Classes are live with a mentor, with recordings and weekly one-to-one reviews, so someone checks not just that your query runs, but that its row counts are right.

• Core (3 months, $699): querying, joins, subqueries, CTEs, window functions, schema design, indexes and views, with two capstone projects.
• Professional (6 months, $1,199): adds query optimisation, stored procedures, transactions and admin basics, plus mock interviews and portfolio review.
• Career (12 months, $1,999): adds data warehousing and dbt basics, a live client-style project with the Omebly team and placement assistance (CV help, mock interviews, portfolio review and referrals where possible, not a job guarantee).

If you want SQL as part of a wider analyst skill set with Excel and Power BI, see the Data Analysis course.

Questions about which track fits you? Message us on WhatsApp or contact us to book a free first call.