SQL Joins Explained with Practical Examples

A report shows 42 customers when the dashboard says there should be 45. Or an admin page lists every order twice. Nine times out of ten, the bug is a JOIN: the wrong type, a missing condition, or a filter in the wrong place that quietly drops or duplicates rows.

This post walks through each JOIN type (INNER, LEFT, RIGHT, FULL OUTER, CROSS and SELF) using one small dataset, so you can see exactly which rows each one keeps and which it throws away. Every query runs as is in MySQL.

Setting Up Our Sample Data

All the examples use two tables, employees and departments. It's the classic example for a reason: it has exactly the kind of gaps that make JOIN types behave differently.

-- Create departments table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

-- Create employees table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- Insert sample data
INSERT INTO departments VALUES
    (1, 'Engineering', 'Building A'),
    (2, 'Marketing', 'Building B'),
    (3, 'Sales', 'Building C'),
    (4, 'HR', 'Building D');

INSERT INTO employees VALUES
    (101, 'Alice Johnson', 1, 85000, '2022-03-15'),
    (102, 'Bob Smith', 1, 75000, '2023-01-10'),
    (103, 'Carol Davis', 2, 65000, '2021-06-20'),
    (104, 'David Wilson', 3, 70000, '2020-09-05'),
    (105, 'Eve Martinez', NULL, 60000, '2024-02-01'),
    (106, 'Frank Brown', 1, 90000, '2019-11-30');

Three details in this data matter:

• Eve (emp_id 105) has NULL for dept_id, so she isn't in any department
• HR department (dept_id 4) has no employees assigned to it
• Engineering (dept_id 1) has multiple employees (Alice, Bob, Frank)

Those edge cases are where the JOIN types differ. With perfectly matched data, most of them would return the same thing.

The JOIN Types Overview

Here's the whole set on one diagram. Each section below goes through one of them with real output.

INNER JOIN: The One You'll Use Most

Use this when you only care about rows that exist on both sides. If an employee has no matching department, or a department has no employees, that row is dropped from the result. It's also what you get when you write plain JOIN without a keyword in front.

SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

LEFT JOIN (LEFT OUTER JOIN)

Reach for LEFT JOIN when you need every row from the left table, matched or not. Rows from the right table come along when they match; when they don't, their columns are filled with NULL.

SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Eve Martinez shows up now, with NULL in the department columns because she has no department.

-- Find employees without a department
SELECT e.emp_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is LEFT JOIN with the sides flipped: you keep every row from the right table, and left table columns are NULL where nothing matches.

SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    d.location
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

This time the HR department appears (with NULL employee columns), and Eve is gone because she doesn't match any department.

-- These produce identical results:
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
SELECT * FROM B LEFT JOIN A ON B.id = A.id;

FULL OUTER JOIN

FULL OUTER JOIN keeps all rows from both tables. Matching rows are combined; unmatched rows from either side come through with NULL on the other side. PostgreSQL and SQL Server support it directly. MySQL doesn't, so you have to emulate it.

-- MySQL doesn't support FULL OUTER JOIN directly
-- Use UNION of LEFT and RIGHT JOINs:

SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    d.location
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION

SELECT 
    e.emp_id,
    e.emp_name,
    d.dept_name,
    d.location
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

One thing to watch with the UNION trick: UNION removes duplicate rows. Here that's what we want, since the matched rows appear in both halves. But if your selected columns can legitimately produce identical rows (say you drop emp_id and two employees share a name), they'll be collapsed into one. In that case use UNION ALL and add WHERE e.emp_id IS NULL to the second query so it only contributes the unmatched right rows.

CROSS JOIN: The Cartesian Product

CROSS JOIN pairs every row of one table with every row of the other, with no condition at all. That's the Cartesian product. Six employees and four departments give you 6 × 4 = 24 rows.

-- Explicit CROSS JOIN syntax
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e
CROSS JOIN departments d;

-- Alternative implicit syntax (comma-separated)
SELECT 
    e.emp_name,
    d.dept_name
FROM employees e, departments d;

SELF JOIN: Joining a Table to Itself

Org charts, category trees, "which rows in this table relate to other rows in the same table": that's where a self join comes in. There's no SELF JOIN keyword. You join the table to itself under two different aliases, so SQL can tell the two copies apart.

To try it, add a manager_id column to employees:

-- Add manager relationship
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = 106 WHERE emp_id IN (101, 102);
UPDATE employees SET manager_id = 103 WHERE emp_id = 104;

-- Self join to find employees and their managers
SELECT 
    e.emp_name AS employee,
    m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Multiple JOINs: More Than Two Tables

Real queries often touch three or more tables. You chain JOIN clauses, and each one joins against the result built so far:

-- Add a projects table
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    dept_id INT
);

INSERT INTO projects VALUES
    (1, 'Website Redesign', 2),
    (2, 'Mobile App', 1),
    (3, 'Data Analytics', 1);

-- Join three tables
SELECT 
    e.emp_name,
    d.dept_name,
    p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON d.dept_id = p.dept_id
ORDER BY e.emp_name;

Notice that Alice, Bob and Frank each appear twice in this result, because Engineering has two projects. That's correct here, but it's the same row multiplication that gives you inflated totals when you SUM() or COUNT() across a one-to-many join. If your numbers look too big, check for this first.

JOIN Types Comparison

Performance Tips for JOINs

-- Create index on foreign key column
CREATE INDEX idx_emp_dept ON employees(dept_id);

-- Analyze query execution plan
EXPLAIN SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Common JOIN Mistakes to Avoid

-- ❌ WRONG: this is a CROSS JOIN!
SELECT * FROM employees e, departments d;

-- ✅ CORRECT
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- ❌ WRONG: ambiguous column 'dept_id'
SELECT dept_id FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- ✅ CORRECT: say which table
SELECT e.dept_id FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Practice Exercise

-- 1. Engineering employees
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering';

-- 2. Department employee counts
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- 3. Employees earning more than their manager
SELECT e.emp_name AS employee, e.salary AS emp_salary,
       m.emp_name AS manager, m.salary AS mgr_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- 4. All employee-project combinations (CROSS JOIN)
SELECT e.emp_name, p.project_name
FROM employees e
CROSS JOIN projects p;

Which JOIN I Reach For (and When I Don't)

Day to day, almost everything I write is INNER JOIN or LEFT JOIN. My rule of thumb: if a missing match means the row is meaningless (an order line without a product), use INNER. If the row still matters on its own (a user with no orders yet), use LEFT, and keep any right table filters in the ON clause.

A few cases where a JOIN isn't the right tool:

• You only need to know whether a match exists. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) says that directly and can't duplicate rows the way a one-to-many JOIN does.
• You're adding DISTINCT to fix duplicates. That usually means the JOIN is fanning out. Fix the join or aggregate in a subquery first instead of hiding it.
• You'd need FULL OUTER JOIN in MySQL. It works with UNION, but if you need it often, ask whether the data model is telling you something.
• An accidental CROSS JOIN. I'd only write one on purpose, with the explicit CROSS JOIN keyword so the next reader knows it wasn't a mistake.

Mayur Dabhi

Full Stack Developer with 5+ years of experience building scalable web applications and working with complex database systems.