SQL Joins Explained Visually: INNER, LEFT, RIGHT, FULL, and CROSS

What is a SQL JOIN?

A JOIN combines rows from two or more tables based on a related column. It's one of the most fundamental operations in SQL and the key to working with relational databases effectively.

If you've ever written a query that pulls data from multiple tables, you've used a JOIN (even if it was implicit).

Setup: Our Example Tables

Let's use two simple tables throughout this guide:

-- employees table
CREATE TABLE employees (
  id    INT PRIMARY KEY,
  name  TEXT,
  dept_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   10),
  (2, 'Bob',     20),
  (3, 'Charlie', 10),
  (4, 'Diana',   30),
  (5, 'Eve',     NULL);  -- No department assigned

-- departments table
CREATE TABLE departments (
  id    INT PRIMARY KEY,
  name  TEXT
);

INSERT INTO departments VALUES
  (10, 'Engineering'),
  (20, 'Marketing'),
  (30, 'Sales'),
  (40, 'HR');  -- No employees in HR

Key observations:

• Eve (employee 5) has no department (NULL)
• HR (department 40) has no employees
• These edge cases are what make JOINs interesting

INNER JOIN

Returns only rows that have matching values in both tables.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Result:

name	department
Alice	Engineering
Bob	Marketing
Charlie	Engineering
Diana	Sales

What happened:

• Eve is excluded (no matching department)
• HR is excluded (no matching employees)
• Only rows with matches in BOTH tables appear

Use when: You only want rows that have related data in both tables. This is the most common JOIN type.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. If there's no match, the right side columns are NULL.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Result:

name	department
Alice	Engineering
Bob	Marketing
Charlie	Engineering
Diana	Sales
Eve	NULL

What happened:

• All employees appear (including Eve)
• Eve has NULL for department because she has no dept_id
• HR still doesn't appear (it's on the right side)

Use when: You want all records from the primary table, even if they don't have related data. Examples:

• All users, with their orders (if any)
• All products, with their reviews (if any)
• All employees, with their manager info (if any)

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table. The mirror of LEFT JOIN.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Result:

name	department
Alice	Engineering
Charlie	Engineering
Bob	Marketing
Diana	Sales
NULL	HR

What happened:

• All departments appear (including HR)
• HR has NULL for employee name
• Eve doesn't appear (she's on the left side)

In practice: RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order:

-- These are equivalent:
SELECT * FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;
SELECT * FROM departments d LEFT JOIN employees e ON e.dept_id = d.id;

FULL OUTER JOIN

Returns all rows from both tables. Where there's no match, the missing side gets NULL.

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Result:

name	department
Alice	Engineering
Bob	Marketing
Charlie	Engineering
Diana	Sales
Eve	NULL
NULL	HR

What happened:

• ALL employees appear (including Eve with no department)
• ALL departments appear (including HR with no employees)
• This is the union of LEFT JOIN and RIGHT JOIN

Use when: You need to find unmatched records on both sides. Common for data reconciliation and finding orphaned records.

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

SELECT e.name, d.name AS department
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

CROSS JOIN

Returns the Cartesian product — every row from the left table combined with every row from the right table. No ON condition needed.

SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;

This produces 5 × 4 = 20 rows (every employee paired with every department).

Use when:

• Generating all possible combinations (e.g., all sizes × all colors)
• Creating a calendar (all dates × all time slots)
• Testing with sample data

Warning: CROSS JOIN can produce enormous result sets. 1000 rows × 1000 rows = 1,000,000 rows.

Self JOIN

A table joined with itself. Uses aliases to distinguish the two "copies":

-- Find employees in the same department
SELECT e1.name AS employee1, e2.name AS employee2, d.name AS department
FROM employees e1
JOIN employees e2 ON e1.dept_id = e2.dept_id AND e1.id < e2.id
JOIN departments d ON e1.dept_id = d.id;

Result:

employee1	employee2	department
Alice	Charlie	Engineering

Common use cases:

• Finding pairs (employees in same department)
• Hierarchical data (employee → manager)
• Comparing rows within the same table

-- Employee and their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Multiple JOINs

You can chain multiple JOINs:

SELECT
  e.name AS employee,
  d.name AS department,
  p.title AS project
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
LEFT JOIN projects p ON e.id = p.lead_id
WHERE d.name = 'Engineering';

Order matters for LEFT JOINs. The result of each JOIN becomes the left table for the next JOIN.

JOIN Performance Tips

1. Always JOIN on indexed columns

-- Make sure these columns have indexes
CREATE INDEX idx_employees_dept ON employees(dept_id);
CREATE INDEX idx_departments_id ON departments(id);

2. Filter early with WHERE

-- Good: filter before joining
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.is_active = true;

-- The query optimizer usually handles this, but explicit filtering helps readability

3. Select only needed columns

-- Bad: selecting everything
SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id;

-- Good: select only what you need
SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id;

4. Use EXPLAIN to understand query plans

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

Quick Reference

JOIN Type	Left Unmatched	Right Unmatched	Use Case
INNER	Excluded	Excluded	Only matching rows
LEFT	Included (NULL)	Excluded	All from left + matches
RIGHT	Excluded	Included (NULL)	All from right + matches
FULL OUTER	Included (NULL)	Included (NULL)	All from both
CROSS	N/A	N/A	All combinations

Common Mistakes

1. Forgetting the ON condition — Without it, you get a CROSS JOIN (millions of rows)
2. Using WHERE instead of ON for outer joins — WHERE filters after the join, turning a LEFT JOIN into an INNER JOIN
3. Not handling NULLs — Remember that NULL = NULL is NULL (not true). Use IS NULL for NULL checks
4. Joining on non-indexed columns — Can make queries orders of magnitude slower

Format your SQL queries with our SQL Formatter tool.