SQL Joins Explained: INNER, LEFT, RIGHT and FULL

Quick Answer

A SQL JOIN combines rows from two tables using a shared column, such as a customer id. INNER JOIN keeps only matching rows; LEFT JOIN keeps every row from the left table; RIGHT JOIN keeps every row from the right table; and FULL OUTER JOIN keeps everything from both, filling the gaps with NULL.

What is a SQL JOIN?

If your data lives in more than one table, sooner or later you need to pull it back together. That is exactly what a JOIN does. In this tutorial you get SQL joins explained in plain language, with two tiny tables and queries you can run yourself.

A JOIN combines rows from two tables based on a related column they share — usually an id. We split data into separate tables to avoid repeating ourselves (one row per customer, one row per order), and then use a JOIN to reconnect them when we ask a question like "which customer placed this order?"

There are four joins you will use almost every day: INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN. They differ in one thing only: which rows they keep when there is no match.

Our sample tables: customers and orders

Let's use two small tables: customers and orders. Each order points at a customer through the customer_id column. You can paste this into any SQL database (PostgreSQL, SQLite, MySQL) to follow along. If SQL is brand new to you, our free SQL course walks through tables and queries from zero.

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT,
  amount      INT
);

INSERT INTO customers (id, name) VALUES
  (1, 'Aarav'),
  (2, 'Diya'),
  (3, 'Rohan'),
  (4, 'Meera');

INSERT INTO orders (id, customer_id, amount) VALUES
  (101, 1, 500),
  (102, 1, 250),
  (103, 2, 999),
  (104, 5, 1200);

Notice two deliberate gaps: Rohan and Meera have no orders, and order 104 points at customer 5, who does not exist. These edge cases are where the different joins start to behave differently — so keep them in mind.

How a JOIN query is built

Every join query has the same four moving parts. Once you can name them, all four joins read the same way.

SELECT  columns you want                    -- what to show
FROM    left_table                          -- the first table
JOIN    right_table                         -- the second table
  ON    left_table.key = right_table.key;   -- how they match

The word before JOIN (INNER, LEFT, RIGHT, FULL) decides which unmatched rows survive. Everything else stays the same. The ON clause is the heart of the join: it tells the database which column links the two tables. In our example that link is always customers.id = orders.customer_id.

You can also give tables short aliases to save typing: FROM customers c JOIN orders o ON c.id = o.customer_id. We keep full names here for clarity.

INNER JOIN: only the matches

An INNER JOIN returns only the rows that have a match on both sides. If a customer has no order, or an order has no matching customer, that row is dropped.

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

Result:

name   | order_id | amount
-------+----------+-------
Aarav  |   101    |  500
Aarav  |   102    |  250
Diya   |   103    |  999

Rohan and Meera disappear (no orders), and order 104 disappears (no customer 5). Use INNER JOIN when you only care about records that exist on both sides — for example, "show every order together with the customer who placed it." Tip: plain JOIN means INNER JOIN in every major database, so you can drop the word "INNER" if you like.

LEFT JOIN: keep every left row

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table — customers here — and attaches order data where it matches. Where there is no match, the order columns come back as NULL.

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

Result:

name   | order_id | amount
-------+----------+-------
Aarav  |   101    |  500
Aarav  |   102    |  250
Diya   |   103    |  999
Rohan  |   NULL   |  NULL
Meera  |   NULL   |  NULL

Now Rohan and Meera appear with empty order fields. Order 104 is still gone, because customer 5 is not in the left table. LEFT JOIN is the one you reach for most often. It answers questions like "list all customers and their orders, including customers who have never ordered." To find only the customers with no orders, add WHERE orders.id IS NULL.

RIGHT JOIN: keep every right row

A RIGHT JOIN is the mirror image: it keeps every row from the right table — orders — and fills in NULL where no customer matches.

SELECT customers.name, orders.id AS order_id, orders.amount
FROM customers
RIGHT JOIN orders
  ON customers.id = orders.customer_id;

Result:

name   | order_id | amount
-------+----------+-------
Aarav  |   101    |  500
Aarav  |   102    |  250
Diya   |   103    |  999
NULL   |   104    | 1200

This time order 104 shows up (with no customer name), and Rohan and Meera are gone. In practice, RIGHT JOIN is rare: most people just swap the table order and write a LEFT JOIN instead, because it reads more naturally. A RIGHT JOIN B returns the same rows as B LEFT JOIN A.

FULL OUTER JOIN: keep everything

A FULL OUTER JOIN keeps everything from both tables. Matched rows line up; unmatched rows from either side appear with NULL on the missing side.

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

Result:

name   | order_id | amount
-------+----------+-------
Aarav  |   101    |  500
Aarav  |   102    |  250
Diya   |   103    |  999
Rohan  |   NULL   |  NULL
Meera  |   NULL   |  NULL
NULL   |   104    | 1200

You get all six rows: the three matches, the two customers with no orders, and the one orphan order. Use FULL OUTER JOIN to spot mismatches on both sides at once — for example, auditing which customers have no orders and which orders point at a missing customer.

Gotcha: MySQL does not support FULL OUTER JOIN. You emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION:

SELECT customers.name, orders.id, orders.amount
FROM customers LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT customers.name, orders.id, orders.amount
FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;

PostgreSQL, SQL Server and modern SQLite support FULL OUTER JOIN directly.

The four joins at a glance

Here is the whole family in one place. Read "left rows" as customers and "right rows" as orders.

A join keeps unmatched rows from a side only when that side is fully preserved. INNER keeps neither, FULL keeps both, and LEFT and RIGHT each keep exactly one side.

Common mistakes and our recommendation

Watch out for these

• Forgetting the ON clause. Without it you get a cross join — every row paired with every other row. Always specify how the tables relate.
• Filtering an outer join in the wrong place. Putting a condition on the right table in WHERE can quietly turn a LEFT JOIN back into an INNER JOIN, because NULL fails the test. If the condition is part of the match, keep it in ON.
• Unexpected duplicate rows. Aarav appears twice above because he has two orders. That is correct — a join produces one row per matching pair, not one row per customer.
• Ambiguous column names. When both tables have an id, always qualify it (customers.id) or the database will complain.

Our recommendation: start with INNER JOIN when you only want matched data, and reach for LEFT JOIN whenever you must not lose rows from your main table. You will rarely need RIGHT or FULL — but knowing they exist makes the whole family click. Practice these four on real tables in the free Priodemy SQL course, and joins will quickly become second nature.

Frequently Asked Questions