SQL JOINs Explained Simply: Connecting Tables for Beginners

For most people learning SQL, JOINs are the first real wall. Up to now everything happened in one table. Then suddenly the data you need is spread over two or three tables, and the queries start to look scary.

They don’t need to be. A JOIN does one simple thing: it puts matching rows from two tables side by side. In this guide we’ll build that idea up with three tiny tables, the real result of every query, and the mistakes I see beginners (and some experienced developers) make most often.

Quick answer: what is a SQL JOIN?
A JOIN combines rows from two tables using a column they share, such as orders.customer_id = customers.id.
INNER JOIN keeps only rows that match in both tables.
LEFT JOIN keeps every row from the first table, with empty (NULL) values where there’s no match.
RIGHT JOIN and FULL JOIN are rarer variations of the same idea.

Venn diagrams showing INNER, LEFT, RIGHT and FULL JOIN results

Why Do We Need JOINs?

A well-designed database doesn’t keep everything in one huge table. It splits the data so each fact is stored once: customers in one table, products in another, orders in a third. (Database Normalization explains why.) Here’s a tiny online shop:

customers

idnamecountry
1Sara AhmedEgypt
2John SmithUK
3Mei LinChina

products

idproduct_namecategory
1SQL HandbookBooks
2DB Cheat SheetDownloads
3SQL CourseCourses

orders

idcustomer_idproduct_idamount
11119.00
2129.00
32119.00
4NULL349.00

Look at the orders table. It doesn’t store the customer’s name, only customer_id, a number that points to a row in customers. Order 1 belongs to customer 1, Sara. That pointer is called a foreign key (Primary Keys vs Foreign Keys explains it properly).

Two details to notice, because they’ll matter later: Mei Lin hasn’t ordered anything yet, and order 4 was a guest checkout, so it has no customer at all.

Now the question: “show me each order with the customer’s name”. Neither table can answer it alone. We need to join them.

Set it up yourself

Run this once in SQL Server, MySQL or PostgreSQL, then type each example as you read:

CREATE TABLE customers (
    id      INT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    country VARCHAR(50)  NOT NULL
);

CREATE TABLE products (
    id           INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category     VARCHAR(50)  NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,               -- empty for guest orders
    product_id  INT NOT NULL,
    amount      DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (id),
    CONSTRAINT fk_orders_products  FOREIGN KEY (product_id)  REFERENCES products (id)
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Sara Ahmed', 'Egypt'),
    (2, 'John Smith', 'UK'),
    (3, 'Mei Lin',    'China');

INSERT INTO products (id, product_name, category) VALUES
    (1, 'SQL Handbook',   'Books'),
    (2, 'DB Cheat Sheet', 'Downloads'),
    (3, 'SQL Course',     'Courses');

INSERT INTO orders (id, customer_id, product_id, amount) VALUES
    (1, 1,    1, 19),
    (2, 1,    2,  9),
    (3, 2,    1, 19),
    (4, NULL, 3, 49);

How a JOIN Works

Every join has the same shape:

SELECT columns
FROM table1
JOIN table2 ON table1.some_column = table2.some_column;

The ON part is the heart of it. It tells the database how to match rows: “take each order, and find the customer whose id equals the order’s customer_id“. Think of it as the database looking up each order’s customer number in the customers list, the way you’d look up a name in a phone book.

INNER JOIN: Only the Rows That Match

INNER JOIN is the one you’ll use most. It returns a row only when a match exists in both tables:

SELECT orders.id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
idnameamount
1Sara Ahmed19.00
2Sara Ahmed9.00
3John Smith19.00

Sara appears twice because she placed two orders. Two rows are missing, and both are missing for a reason:

  • Mei Lin isn’t there, because no order points to her.
  • Order 4 isn’t there, because it has no customer to match.

That’s the INNER JOIN rule: no match, no row. Writing just JOIN without a word in front means exactly the same thing.

LEFT JOIN: Keep Everything From the First Table

Often you don’t want rows to disappear. “Show all customers, with their orders if they have any” needs a LEFT JOIN. It keeps every row from the table on the left (the one after FROM), and fills in empty values when there’s no match:

SELECT customers.name, orders.id AS order_id, orders.amount
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;
nameorder_idamount
Sara Ahmed119.00
Sara Ahmed29.00
John Smith319.00
Mei LinNULLNULL

Now Mei Lin is in the list, with NULL (empty) where her order would be. That empty value is very useful, because it lets you find things that are missing:

-- Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Mei Lin

This “LEFT JOIN, then look for NULL” pattern answers a whole family of business questions: customers who never ordered, products that never sold, employees with no tasks, invoices with no payment.

RIGHT JOIN and FULL JOIN

A RIGHT JOIN is a LEFT JOIN facing the other way: it keeps every row from the table on the right.

SELECT customers.name, orders.id AS order_id, orders.amount
FROM customers
RIGHT JOIN orders ON orders.customer_id = customers.id;
nameorder_idamount
Sara Ahmed119.00
Sara Ahmed29.00
John Smith319.00
NULL449.00

All four orders are there, including the guest order with no name. In practice, most developers simply swap the table order and write a LEFT JOIN instead. It reads more naturally, and you’ll rarely see RIGHT JOIN in real code.

A FULL JOIN (or FULL OUTER JOIN) keeps everything from both sides:

SELECT customers.name, orders.id AS order_id, orders.amount
FROM customers
FULL JOIN orders ON orders.customer_id = customers.id;
nameorder_idamount
Sara Ahmed119.00
Sara Ahmed29.00
John Smith319.00
NULL449.00
Mei LinNULLNULL

Both the customer with no orders and the order with no customer appear. It’s handy for comparing two lists, for example matching bank statement lines against payments in your system. Note: MySQL doesn’t support FULL JOIN. There you combine a LEFT JOIN and a RIGHT JOIN with UNION (see UNION vs UNION ALL).

The Four JOINs at a Glance

JOINKeepsIn our example
INNER JOINOnly rows that match in both tables3 orders with a known customer
LEFT JOINEvery row from the left tableAll 3 customers, Mei with empty order
RIGHT JOINEvery row from the right tableAll 4 orders, guest order with empty name
FULL JOINEvery row from both tablesEverything: 5 rows

For most everyday work you’ll only need INNER JOIN and LEFT JOIN. Get comfortable with those two first.

Table Aliases: Shorter, Cleaner Joins

Writing customers.name and orders.amount again and again gets tiring. A table alias gives each table a short nickname right after its name:

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

c means customers and o means orders. The result is exactly the same. Once you join three or more tables, aliases stop being a nice extra and become essential. They also make it clear which table each column comes from, which avoids “ambiguous column name” errors when two tables both have an id.

Joining Three Tables

Real reports often need more than two tables. You just add one more JOIN for each table. Here’s every order with the customer’s name and the product’s name:

SELECT o.id, c.name, p.product_name, p.category, o.amount
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
INNER JOIN products  p ON p.id = o.product_id;
idnameproduct_namecategoryamount
1Sara AhmedSQL HandbookBooks19.00
2Sara AhmedDB Cheat SheetDownloads9.00
3John SmithSQL HandbookBooks19.00

Read it from the top: start with the orders, look up each order’s customer, then look up each order’s product. The guest order is missing again, because the INNER JOIN to customers found no match for it. Change that join to a LEFT JOIN and it comes back.

JOIN and GROUP BY Together

Joins become really powerful when you combine them with GROUP BY. Here’s a small customer report: how many orders each customer placed and how much they spent, including customers who haven’t bought anything yet:

SELECT c.name,
       COUNT(o.id)                 AS orders,
       COALESCE(SUM(o.amount), 0)  AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
nameorderstotal_spent
Sara Ahmed228.00
John Smith119.00
Mei Lin00

Two small things make this correct. COUNT(o.id) counts only real orders, so Mei gets 0 instead of 1 (COUNT(*) would count her empty row). And COALESCE(..., 0) turns her empty total into a 0.

Common JOIN Mistakes

1. A WHERE condition that quietly cancels your LEFT JOIN

This is the most common JOIN bug I see in real projects. You want all customers, and their orders over 10:

-- Looks right, but Mei Lin disappears
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 10;

Mei’s row has an empty amount, and “empty is greater than 10” isn’t true, so WHERE throws her out. Your LEFT JOIN has silently become an INNER JOIN. The fix is to put the condition on the joined table inside ON:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.amount > 10;
-- Sara Ahmed 19.00, John Smith 19.00, Mei Lin (empty)

2. Joining on the wrong column

ON o.customer_id = c.id is right. ON o.id = c.id runs without any error, but it matches order 1 with customer 1 and order 2 with customer 2, which is nonsense. Always join a foreign key to the key it points to.

3. Totals that are suddenly too big

Joining a customer to their orders gives one row per order. If you also join a second list, like their payments, the rows multiply: 3 orders and 2 payments become 6 rows, and every SUM is wrong. When totals look too large after a join, count the rows first. The usual fix is to total each table separately (in a subquery or CTE) and then join the totals.

4. Using DISTINCT to hide duplicates

If a join returns duplicate rows you didn’t expect, adding DISTINCT makes them disappear, but the real problem (usually a missing join condition) is still there. Fix the join instead.

Practice: Students and Grades

Time to try it yourself. Create these two tables. Notice that Lina has no grades yet.

CREATE TABLE students (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE grades (
    id         INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject    VARCHAR(50) NOT NULL,
    score      INT NOT NULL,
    CONSTRAINT fk_grades_students FOREIGN KEY (student_id) REFERENCES students (id)
);

INSERT INTO students (id, name, city) VALUES
    (1, 'Ali',    'Manama'),
    (2, 'Noor',   'Riffa'),
    (3, 'Hassan', 'Muharraq'),
    (4, 'Lina',   'Manama');

INSERT INTO grades (id, student_id, subject, score) VALUES
    (1, 1, 'Math',    85),
    (2, 1, 'English', 78),
    (3, 2, 'Math',    92),
    (4, 3, 'English', 64),
    (5, 3, 'Science', 71);
  1. Show each student’s name with their subjects and scores (only students who have grades).
  2. Show all students, including those with no grades.
  3. Which students have no grades at all?
  4. What is each student’s average score?
  5. How many grades does each student have? Lina should show 0.

Answers

-- 1. 5 rows: Ali (English 78, Math 85), Hassan (English 64, Science 71), Noor (Math 92)
SELECT s.name, g.subject, g.score
FROM students s
INNER JOIN grades g ON g.student_id = s.id
ORDER BY s.name, g.subject;

-- 2. The same 5 rows, plus Lina with empty subject and score
SELECT s.name, g.subject, g.score
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
ORDER BY s.name, g.subject;

-- 3. Lina
SELECT s.name
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
WHERE g.id IS NULL;

-- 4. Ali 81.5, Noor 92.0, Hassan 67.5
SELECT s.name, ROUND(AVG(g.score * 1.0), 1) AS avg_score
FROM students s
INNER JOIN grades g ON g.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

-- 5. Ali 2, Noor 1, Hassan 2, Lina 0
SELECT s.name, COUNT(g.id) AS number_of_grades
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

In question 4, score * 1.0 stops SQL Server from rounding the average to a whole number, because score is an INT. In question 5, try COUNT(*) instead of COUNT(g.id) and see what happens to Lina. Understanding why she gets 1 is a big step.

Quick Recap

You wantUse
Only rows that match in both tablesINNER JOIN
Everything from the first table, matched or notLEFT JOIN
Rows in A that have no match in BLEFT JOIN + WHERE b.id IS NULL
Everything from both tablesFULL JOIN (not in MySQL)
Data from three tablesOne extra JOIN ... ON per table
A filter on the right table that keeps all left rowsPut it in ON, not WHERE

Frequently Asked Questions

Is JOIN the same as INNER JOIN?

Yes. If you write JOIN on its own, the database treats it as INNER JOIN. Many people write INNER anyway, because it makes the intention clear.

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

There is none. OUTER is an optional word. LEFT JOIN and LEFT OUTER JOIN do exactly the same thing, and the same goes for RIGHT and FULL.

Can I join a table to itself?

Yes. It’s called a self join, and it’s useful when a table points to itself, like employees and their managers. SQL SELF JOIN Explained Simply shows how.

Do JOINs make queries slow?

Not when the join columns are indexed. Primary keys are indexed automatically. Foreign key columns usually need an index added by you (MySQL does it for you, SQL Server and PostgreSQL don’t). SQL Indexes Explained Simply explains how.

What to Learn Next

Related articles

Scroll to Top