SQL JOIN Explained for Beginners: INNER, LEFT & More

If you searched for "SQL JOIN explained for beginners," here is the short answer: a JOIN is a way to combine rows from two or more tables based on a column they share, so you can pull related data together in one query. This guide walks through exactly how that works, with small examples you can copy, paste, and run yourself.

You do not need any prior SQL knowledge beyond the basics of SELECT and WHERE. If those are new to you, it may help to read our guide on SQL SELECT and WHERE first, then come back here.

Why do databases need JOIN at all?

In a real database, information is usually split across several tables instead of crammed into one giant table. For example, a shop might keep customer details in a customers table and order details in a separate orders table. Splitting data like this avoids repeating the same customer name on every single order row, which saves space and avoids mistakes.

The two tables are linked by an ID column. The customers table has a primary key, usually called id, which uniquely identifies each customer. The orders table has a foreign key, often called customer_id, which stores the id of the customer who placed that order. A JOIN uses this shared value to stitch the two tables back together when you need both pieces of information at once.

The example tables we will use

To keep things concrete, imagine a tiny shop database with these two tables.

That is the customers table. Now here is the orders table, where customer_id points back to a row in customers.

Notice that Noor (customer id 3) has no orders, and order 104 has a customer_id of 5, which does not exist in the customers table. This is realistic and messy on purpose, so you can see exactly how each JOIN type handles it.

INNER JOIN: only the rows that match in both tables

INNER JOIN is the most common join. It returns only the rows where the shared value exists in both tables, and drops everything else.

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

Line by line: SELECT customers.name, orders.product picks the two columns we want, one from each table. FROM customers sets the starting table. INNER JOIN orders adds the second table we want to combine with it. ON customers.id = orders.customer_id tells SQL which columns to match up, this is the rule that connects the two tables.

The result only includes customers who have at least one order, and only orders whose customer_id matches a real customer. So Noor (no orders) and order 104 (unknown customer) are both left out.

LEFT JOIN: keep every row from the first table

Sometimes you want every row from your main table even when there is no match in the other table, for example, to see every customer including ones who never ordered anything. LEFT JOIN does exactly that.

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

This looks almost identical to the INNER JOIN query, only the word LEFT is new. The FROM table (customers) is the left side, and every row from it is kept no matter what. When there is no matching order, SQL fills the orders.product column with NULL, which simply means empty or unknown.

This time Noor still shows up, with NULL in place of a product, because she matched no rows in orders. The unmatched order 104 is still dropped, because RIGHT-side rows without a match are not kept in a LEFT JOIN.

RIGHT JOIN and FULL JOIN in one paragraph

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row from the second (right) table and fills in NULL for the first table when there is no match. FULL JOIN keeps every row from both tables, matched or not, filling NULL on whichever side is missing. RIGHT JOIN and FULL JOIN are less common in practice, because most people just rewrite the table order and use LEFT JOIN instead. One thing to note: MySQL does not support FULL JOIN directly, while PostgreSQL does.

A quick way to choose the right JOIN

• Use INNER JOIN when you only care about rows that exist in both tables, such as "customers who have placed an order."
• Use LEFT JOIN when you want every row from your main table, whether or not it has a match, such as "every customer, plus their orders if any."
• Use RIGHT JOIN when it is easier to keep the second table complete instead of rewriting the query with LEFT JOIN.
• Use FULL JOIN when you want to see mismatches on both sides at once, such as orphaned records in either table.

Common mistakes beginners make with JOIN

• Forgetting the ON clause. Without it, SQL pairs every row in the first table with every row in the second table, which is called a cross join and usually produces a huge, useless result.
• Joining on the wrong columns, such as matching customers.id to orders.id instead of orders.customer_id. Always double check which column is the foreign key.
• Not using table aliases when column names repeat, which can cause an "ambiguous column" error. Writing customers AS c and orders AS o, then using c.name and o.product, keeps queries shorter and clearer.
• Assuming LEFT JOIN and INNER JOIN always return the same rows. They only match when every row in the left table has a matching row in the right table.

Using table aliases

SELECT c.name, o.product
FROM customers AS c
LEFT JOIN orders AS o
  ON c.id = o.customer_id
WHERE c.city = 'Lisbon';

AS c and AS o give each table a short nickname, so c.name and o.product are quicker to type and read than customers.name and orders.product. The WHERE clause still works normally after a JOIN, here it filters the combined result down to customers based in Lisbon.

Try it yourself

You can practice the exact examples from this guide in a free online SQL sandbox, or on your own computer using SQLite, PostgreSQL, or MySQL. Here is the full setup so you can run everything above yourself.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product TEXT
);

INSERT INTO customers VALUES
  (1, 'Maria', 'Lisbon'),
  (2, 'Sam', 'Austin'),
  (3, 'Noor', 'Cairo');

INSERT INTO orders VALUES
  (101, 1, 'Headphones'),
  (102, 1, 'Phone Case'),
  (103, 2, 'Laptop Stand'),
  (104, 5, 'Keyboard');

CREATE TABLE defines each table's columns and their data types, INTEGER for whole numbers and TEXT for words. PRIMARY KEY marks the column that uniquely identifies each row. INSERT INTO adds the sample rows used throughout this guide, so once you run this, every JOIN query above will work exactly as shown.