SQL - JOIN Types Complete Guide (INNER, LEFT, RIGHT, FULL)

Key Insights

• INNER JOIN returns only matching rows, while LEFT/RIGHT JOINs preserve all rows from one table regardless of matches—choosing wrong means missing data or unexpected NULLs
• LEFT JOIN is almost always preferable to RIGHT JOIN because it reads naturally (keep everything from the “main” table you’re querying from)
• Filter conditions in ON vs WHERE clauses behave differently for OUTER JOINs—placing filters in WHERE can accidentally convert your LEFT JOIN into an INNER JOIN

Understanding SQL JOINs is fundamental to working with relational databases. Once you move beyond single-table queries, JOINs become the primary mechanism for combining related data. This guide covers the four main JOIN types with practical examples you can run immediately.

The Sample Dataset

Before diving into JOIN types, let’s establish a consistent dataset. We’ll use two related tables that model a common real-world scenario: employees and their departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    budget DECIMAL(12, 2)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert departments
INSERT INTO departments VALUES (1, 'Engineering', 500000.00);
INSERT INTO departments VALUES (2, 'Marketing', 200000.00);
INSERT INTO departments VALUES (3, 'Sales', 300000.00);
INSERT INTO departments VALUES (4, 'Research', 150000.00);  -- No employees

-- Insert employees
INSERT INTO employees VALUES (1, 'Alice Chen', 1, 95000.00);
INSERT INTO employees VALUES (2, 'Bob Smith', 1, 85000.00);
INSERT INTO employees VALUES (3, 'Carol White', 2, 75000.00);
INSERT INTO employees VALUES (4, 'David Brown', 3, 70000.00);
INSERT INTO employees VALUES (5, 'Eve Johnson', NULL, 60000.00);  -- No department

Notice the intentional gaps: the Research department has no employees, and Eve Johnson has no assigned department. These gaps will demonstrate how different JOINs handle missing relationships.

INNER JOIN - Matching Records Only

INNER JOIN is the most common JOIN type and the default when you simply write JOIN. It returns only rows where the join condition matches in both tables. Think of it as the intersection of two sets.

SELECT 
    e.employee_id,
    e.name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Result:

Notice what’s missing: Eve Johnson (no department) and the Research department (no employees) don’t appear. INNER JOIN excludes any row that lacks a match on the other side.

When to use INNER JOIN: When you only want complete data. If an employee without a department is meaningless for your report, INNER JOIN is correct. It’s also the most performant JOIN type since the database can eliminate non-matching rows early.

LEFT JOIN - All Left, Matching Right

LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right table. When there’s no match, the right table’s columns contain NULL.

SELECT 
    e.employee_id,
    e.name,
    d.department_name,
    e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Result:

Eve Johnson now appears with NULL for department_name. The Research department still doesn’t appear because it’s on the right side.

LEFT JOIN is particularly useful for finding orphaned records:

-- Find employees not assigned to any department
SELECT 
    e.employee_id,
    e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This pattern—LEFT JOIN followed by a NULL check on the right table’s primary key—is the standard way to find records without relationships.

RIGHT JOIN - All Right, Matching Left

RIGHT JOIN is the mirror image of LEFT JOIN. It returns all rows from the right table and matched rows from the left table.

SELECT 
    e.name,
    d.department_id,
    d.department_name,
    d.budget
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Result:

Now Research appears (with NULL for employee name), but Eve Johnson doesn’t.

Practical advice: Avoid RIGHT JOIN. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. LEFT JOIN reads more naturally because we typically think “give me all X with their related Y” rather than “give me related Y for all X.”

The equivalent LEFT JOIN:

SELECT 
    e.name,
    d.department_id,
    d.department_name,
    d.budget
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id;

Same result, more readable. Put your “main” table on the left.

FULL OUTER JOIN - Everything From Both

FULL OUTER JOIN returns all rows from both tables, matching where possible and filling NULLs where not. It’s the union of LEFT and RIGHT JOINs.

-- PostgreSQL, SQL Server, Oracle
SELECT 
    e.employee_id,
    e.name,
    d.department_id,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

Result:

Both Eve Johnson and Research appear, each with NULLs for the unmatched side.

MySQL workaround: MySQL doesn’t support FULL OUTER JOIN directly. Simulate it with UNION:

-- MySQL equivalent
SELECT e.employee_id, e.name, d.department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

UNION

SELECT e.employee_id, e.name, d.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

FULL OUTER JOIN is less common but valuable for data reconciliation—finding mismatches between two datasets.

Visual Comparison and Performance

Here’s a quick reference for what each JOIN returns:

• INNER JOIN: Only the overlap (rows matching in both tables)
• LEFT JOIN: All of left table + overlap
• RIGHT JOIN: All of right table + overlap
• FULL OUTER JOIN: Everything from both tables

-- Compare result counts
SELECT 'INNER' as join_type, COUNT(*) as row_count
FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT 'LEFT', COUNT(*)
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT 'RIGHT', COUNT(*)
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

Result:

Performance tip: Always index your foreign key columns. The department_id column in employees should have an index since it’s used in JOIN conditions. Without indexes, JOIN performance degrades dramatically as tables grow.

Common Mistakes and Best Practices

Mistake 1: Wrong Filter Placement in OUTER JOINs

This is the most common JOIN bug. Filter conditions behave differently in ON versus WHERE clauses for OUTER JOINs.

-- WRONG: Converts LEFT JOIN to INNER JOIN behavior
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

This excludes Eve Johnson entirely because her NULL department_name fails the WHERE condition.

-- RIGHT: Filter in ON clause preserves LEFT JOIN behavior
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id 
    AND d.department_name = 'Engineering';

Now Eve Johnson appears with NULL for department_name. The filter applies during the JOIN, not after.

Rule: For OUTER JOINs, put filters on the preserved table in WHERE, and filters on the optional table in ON.

Mistake 2: Accidental Cartesian Products

Forgetting the ON clause or using incorrect join conditions creates a Cartesian product—every row matched with every other row.

-- DANGEROUS: Creates 20 rows (5 employees × 4 departments)
SELECT e.name, d.department_name
FROM employees e, departments d;  -- Old implicit join syntax

-- ALSO DANGEROUS: Wrong column in ON
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.employee_id = d.department_id;  -- Wrong!

Always verify your result counts make sense.

Mistake 3: Chaining Multiple JOINs Incorrectly

When joining multiple tables, each JOIN builds on the previous result:

-- Correct chaining
SELECT e.name, d.department_name, p.project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN projects p ON e.employee_id = p.lead_employee_id;

If you use INNER JOIN in the middle of a chain of LEFT JOINs, you’ll lose the NULL-preserving behavior for earlier tables.

Best Practices Summary

1. Prefer LEFT JOIN over RIGHT JOIN for readability
2. Always index foreign key columns
3. Use table aliases consistently (short, meaningful names)
4. Put filters on optional tables in the ON clause, not WHERE
5. Verify row counts match expectations
6. Use INNER JOIN when you truly need only matching records

JOINs are the backbone of relational queries. Master these four types and their behaviors with NULL values, and you’ll handle the vast majority of multi-table query requirements confidently.