SQL LEFT JOIN vs INNER JOIN Explained for Beginners

What is the difference between INNER JOIN and LEFT JOIN in SQL? Both combine rows from two tables using a shared column. An INNER JOIN keeps only the rows that have a match in both tables. A LEFT JOIN keeps every row from the first (left) table, and fills in NULL when there is no match on the right. That is the whole idea, and this guide shows it step by step.

You do not need any experience. We will define each word the first time we use it and use a tiny example you can copy and run. If you have never joined tables before, read our basic guide to SQL joins first, then come back here.

What is a join, in plain words?

A database stores data in tables. A table is like a spreadsheet with columns and rows. Good databases split data into several tables, for example one table for customers and one for orders. A join is how you put the pieces back together in one result.

To join two tables, you need a column that links them. Here, each order stores the id of the customer who made it. That linking column is often called a foreign key.

Create the example tables

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

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  item VARCHAR(50)
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ana'), (2, 'Ben'), (3, 'Chloe');

INSERT INTO orders (id, customer_id, item) VALUES
  (101, 1, 'Book'),
  (102, 1, 'Pen'),
  (103, 2, 'Lamp');

The first two statements create the tables. The two INSERT statements add rows. We have three customers and three orders. Ana made two orders, Ben made one, and Chloe made none. Chloe is the key to understanding the difference.

INNER JOIN: only the matches

SELECT customers.name, orders.item
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id;

Line 1 picks the two columns we want to see. Line 2 says the left table is customers. Line 3 joins the orders table. Line 4 is the rule: a customer matches an order when customers.id equals orders.customer_id. Writing JOIN alone means the same as INNER JOIN.

Chloe is missing. She has no orders, so she has no match, and an INNER JOIN drops her. Use it when you only care about rows that exist on both sides.

LEFT JOIN: keep everything on the left

SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id;

Only one word changed: INNER became LEFT. The left table is the one written after FROM, so customers is the left table. Every customer is kept, even without orders.

Chloe is back. Her item is NULL, which is SQL's word for 'no value'. NULL means the right table had nothing to give.

Side-by-side comparison

Find rows with no match

A very common trick: use a LEFT JOIN and then keep only the rows where the right side is NULL. This finds things that have no partner, such as customers who never ordered.

SELECT customers.name
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
WHERE orders.id IS NULL;

The join runs first and produces Chloe with NULL columns. Then WHERE orders.id IS NULL keeps only that row. Notice we write IS NULL, not = NULL, because NULL is never equal to anything. Our guide on SQL NULL explains why.

Count orders per customer

SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.name;

GROUP BY makes one result row per customer. COUNT(orders.id) counts only non-NULL ids, so Chloe gets 0 instead of 1. With an INNER JOIN, Chloe would not appear at all. Count the right-table column, not COUNT(*), or Chloe would show 1.

Common beginner mistakes

• Forgetting the ON rule. Without it you may get every possible pairing, which is a huge, wrong result.
• Mixing up table order. In a LEFT JOIN, the table after FROM is the one that is fully kept.
• Using = NULL instead of IS NULL.
• Putting a filter on the right table in WHERE. It removes the NULL rows and quietly turns your LEFT JOIN into an INNER JOIN. Put that condition in the ON part instead.

Which one should you choose?

Ask yourself one question: should rows with no match still appear? If no, use INNER JOIN. If yes, use LEFT JOIN. These examples work in PostgreSQL, MySQL, and most other SQL databases.