SQL Joins Explained — INNER, LEFT, RIGHT, and FULL JOIN

SQL joins combine rows from two or more tables based on a related column. Choosing the wrong join type produces wrong results — usually missing rows or unexpected NULLs. Understanding each join type prevents these bugs.

Use the SQL Formatter to format and beautify SQL queries online.

Sample tables

All examples use these two tables:

-- users table:
id | name    | dept_id
---|---------|--------
1  | Alice   | 1
2  | Bob     | 2
3  | Charlie | NULL
4  | Diana   | 99

-- departments table:
id | name
---|----------
1  | Engineering
2  | Marketing
3  | Finance

INNER JOIN

Returns only rows with matching values in both tables:

SELECT users.name, departments.name AS department
FROM users
INNER JOIN departments ON users.dept_id = departments.id;

Result:

name    | department
--------|-------------
Alice   | Engineering
Bob     | Marketing

Charlie (dept_id = NULL) and Diana (dept_id = 99, no matching department) are excluded. Department “Finance” (no users) is also excluded.

Use INNER JOIN when: You want only rows where the relationship exists in both tables.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, with matching rows from the right table. Unmatched right-table columns are NULL:

SELECT users.name, departments.name AS department
FROM users
LEFT JOIN departments ON users.dept_id = departments.id;

Result:

name    | department
--------|-------------
Alice   | Engineering
Bob     | Marketing
Charlie | NULL          ← No dept (dept_id is NULL)
Diana   | NULL          ← No matching dept (99 doesn't exist)

All 4 users appear. The department column is NULL where there’s no match.

Use LEFT JOIN when: You want all rows from the left (primary) table, with related data when it exists. Classic use: “all users and their orders” — shows users with no orders too.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, with matching rows from the left. Less common than LEFT JOIN:

SELECT users.name, departments.name AS department
FROM users
RIGHT JOIN departments ON users.dept_id = departments.id;

Result:

name    | department
--------|-------------
Alice   | Engineering
Bob     | Marketing
NULL    | Finance       ← Department has no users

All 3 departments appear. Users without matching departments (Charlie, Diana) are excluded because the join is “right” — departments drive the result.

Use RIGHT JOIN when: You want all rows from the right table. Usually, you can rewrite as LEFT JOIN by swapping table order — most developers prefer LEFT JOIN for readability.

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. NULL fills missing matches from either side:

SELECT users.name, departments.name AS department
FROM users
FULL JOIN departments ON users.dept_id = departments.id;

Result:

name    | department
--------|-------------
Alice   | Engineering
Bob     | Marketing
Charlie | NULL          ← User with no dept
Diana   | NULL          ← User with invalid dept
NULL    | Finance       ← Dept with no users

Use FULL JOIN when: You need a complete view of both tables, including unmatched rows from each side. Useful for reconciliation queries (“find users without a department AND departments without users”).

CROSS JOIN

Returns every combination of rows from both tables (Cartesian product):

SELECT users.name, departments.name AS department
FROM users
CROSS JOIN departments;

Result: 4 users × 3 departments = 12 rows.

Use CROSS JOIN when: You intentionally need every combination — generating test data, date ranges with categories, or combinatorial analysis.

Self JOIN

A table joined to itself — useful for hierarchical or relationship data:

-- employees table:
id | name    | manager_id
---|---------|----------
1  | Alice   | NULL      (CEO)
2  | Bob     | 1
3  | Charlie | 1
4  | Diana   | 2

-- Self JOIN to find 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;

Result:

employee | manager
---------|--------
Alice    | NULL    (no manager)
Bob      | Alice
Charlie  | Alice
Diana    | Bob

JOIN performance

Index the join columns. If users.dept_id and departments.id aren’t indexed, the database scans every row in both tables:

-- Ensure indexes exist on join columns:
CREATE INDEX idx_users_dept_id ON users(dept_id);
-- departments.id is already the primary key (auto-indexed)

Join order matters for query planners. Put the smaller table or the more selective filter first when the optimizer doesn’t reorder automatically.

*Avoid SELECT : Selecting all columns forces reading all column data. Select only what you need.

Common JOIN mistakes

Unintentional row multiplication

-- WRONG: if user has multiple orders, user info repeats
SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id;
-- One row per order, not per user

-- If you want totals:
SELECT users.name, SUM(orders.total) AS total_spent
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

NULL in JOIN condition

NULL never equals NULL in SQL:

-- This never matches anything when dept_id is NULL:
JOIN departments ON users.dept_id = departments.id
-- NULL = 1 → false, NULL = NULL → false (not true!)

-- To include NULL matching (rare):
JOIN departments ON users.dept_id IS NOT DISTINCT FROM departments.id
-- PostgreSQL syntax; uses IS NOT DISTINCT FROM which treats NULL = NULL as true

Missing GROUP BY with aggregates

-- WRONG: mixing aggregate and non-aggregate without GROUP BY:
SELECT users.name, COUNT(orders.id)
FROM users
LEFT JOIN orders ON users.id = orders.user_id;  -- Error!

-- CORRECT:
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

JOIN syntax variations

-- ANSI SQL (standard, preferred):
SELECT * FROM a INNER JOIN b ON a.id = b.a_id

-- Implicit join (older syntax, avoid):
SELECT * FROM a, b WHERE a.id = b.a_id

-- USING clause (when column names are identical):
SELECT * FROM users JOIN departments USING (dept_id)

-- NATURAL JOIN (auto-matches same-named columns, avoid — fragile):
SELECT * FROM users NATURAL JOIN departments

Related tools

• SQL Formatter — format and beautify SQL queries
• SQL Formatter Online — SQL formatting guide
• SQL Style Guide — writing clean SQL