SQL JOIN Types Explained with Visual Examples

Introduction to JOINs

Joins are the mechanism SQL uses to combine rows from two or more tables based on a related column. They are the fundamental operation that makes relational databases relational. Understanding how each JOIN type works is essential for writing correct queries, and misunderstanding them is one of the most common sources of bugs in SQL code.

This guide covers all six JOIN types with consistent example data so you can compare the results directly. We use two simple tables throughout: an employees table and a departments table. Some employees have no department assigned (NULL), and some departments have no employees. These edge cases are critical for understanding how different JOINs behave.

The Example Tables

Every example in this guide uses the following two tables. Study them before proceeding, because the results of each JOIN depend on which rows match and which do not.

Notice: Diana has no department (dept_id is NULL), and Finance (id 30) has no employees. These rows are the key to understanding JOIN behavior.

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. Think of it as the intersection of two sets: if you imagine a Venn diagram with one circle for employees and one for departments, INNER JOIN returns only the overlapping region where both circles meet. Rows without a match in the other table are excluded entirely.

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

Diana is excluded because her dept_id is NULL (no match in departments). Finance is excluded because no employee has dept_id = 30. INNER JOIN is the most commonly used JOIN type and is the default when you write just JOIN without a qualifier.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table (the table after FROM), plus matching rows from the right table (the table after JOIN). If a left-table row has no match in the right table, the right-table columns are filled with NULL. In a Venn diagram, this is the entire left circle plus the overlapping region.

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

Diana now appears in the result with NULL for the department, because LEFT JOIN preserves all rows from the left table. Finance still does not appear because it is in the right table and has no matching employee. LEFT JOIN is the most common way to include rows that might not have a match, such as showing all customers even if they have no orders.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN is the mirror image of LEFT JOIN. It returns all rows from the right table plus matching rows from the left table. In a Venn diagram, this is the entire right circle plus the overlapping region. In practice, RIGHT JOIN is rarely used because the same result can always be achieved by swapping the table order and using LEFT JOIN, which most developers find more readable.

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

Finance now appears with NULL for the employee name. Diana is excluded because she is in the left table and RIGHT JOIN only preserves unmatched rows from the right table.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where a match exists, the columns are populated from both tables. Where no match exists on either side, the missing columns are filled with NULL. In a Venn diagram, this is both entire circles -- the union of both sets.

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

Both Diana (no department) and Finance (no employees) appear in the result. FULL OUTER JOIN is useful for data reconciliation tasks where you need to identify orphaned records on both sides. Note that MySQL does not natively support FULL OUTER JOIN; you can simulate it with a UNION of LEFT JOIN and RIGHT JOIN.

CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables: every row from the left table is combined with every row from the right table. There is no ON clause because there is no condition -- every possible pairing is produced. If the left table has M rows and the right table has N rows, the result has M x N rows.

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

CROSS JOIN is intentionally used for generating combinations (e.g., all size-color pairs for a product), creating date ranges, or building test data. However, accidental Cartesian products from missing JOIN conditions are one of the most common and dangerous SQL mistakes. A CROSS JOIN between two 10,000-row tables produces 100 million rows. Always ensure you mean to use CROSS JOIN when you write one.

SELF JOIN

A self join is not a separate JOIN syntax -- it is any JOIN where a table is joined to itself. This is accomplished by using table aliases to create two references to the same table. Self joins are useful for hierarchical data (employees and their managers), finding duplicates, or comparing rows within the same table.

-- Employees table with a manager_id column:
-- id | name    | manager_id
-- 1  | Alice   | NULL
-- 2  | Bob     | 1
-- 3  | Charlie | 1
-- 4  | Diana   | 2

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

The key insight is that employees e and employees m are two different references to the same physical table. The LEFT JOIN ensures Alice (who has no manager) still appears in the results. Self joins are essential for modeling hierarchical relationships in relational databases.

Common Mistakes and Gotchas

NULL Handling in JOIN Conditions

NULL is not equal to anything, including itself. This means NULL = NULL evaluates to NULL (which is falsy in SQL). If a column used in a JOIN condition can contain NULL values, those rows will never match. This is why Diana does not appear in INNER JOIN results: her dept_id is NULL, and NULL = 10, NULL = 20, and NULL = 30 all evaluate to NULL (not TRUE). Use IS NULL checks or COALESCE if you need to handle NULLs in join conditions.

Accidental Cartesian Products

If you forget the ON clause or write an ON condition that does not meaningfully restrict the join, you get a Cartesian product. This is equivalent to a CROSS JOIN and can produce enormous result sets. Common causes include joining on a column that is the same in every row, using a tautological condition like ON 1=1, or accidentally omitting the ON clause entirely.

Filtering After a LEFT JOIN

A subtle but frequent mistake is placing a filter condition in the WHERE clause that effectively converts a LEFT JOIN into an INNER JOIN. Consider this query:

-- INCORRECT: WHERE clause filters out NULL departments
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering';
-- Diana is excluded because d.name IS NULL

-- CORRECT: Move the filter to the ON clause
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.id
  AND d.name = 'Engineering';
-- Diana is preserved with NULL department

When you filter on a right-table column in the WHERE clause after a LEFT JOIN, rows where that column is NULL (i.e., unmatched rows) are filtered out. Move the condition to the ON clause instead to preserve the LEFT JOIN semantics.

Choosing the Right JOIN

In practice, the vast majority of JOINs you write will be either INNER JOIN (when you only want matching rows) or LEFT JOIN (when you want all rows from one table plus any available matches). FULL OUTER JOIN is for data reconciliation. CROSS JOIN is for generating combinations. RIGHT JOIN is rarely used; prefer LEFT JOIN with swapped table order for clarity. Self joins are for hierarchical or self-referential data.

Try formatting your JOIN queries with our SQL Formatter, which automatically structures JOIN clauses with consistent indentation and alignment. For working with API data that feeds into SQL queries, check out our JSON Formatter for transforming API payloads before processing them in your database.