SQL JOINs Explained Visually: INNER, LEFT, RIGHT, FULL, CROSS

Our Sample Data

We will use two simple tables throughout every example so you can see exactly how each JOIN behaves with the same data.

-- employees table
+----+----------+--------+
| id | name     | dep_id |
+----+----------+--------+
|  1 | Alice    |    101 |
|  2 | Bob      |    102 |
|  3 | Charlie  |    103 |
|  4 | Diana    |   NULL |
+----+----------+--------+

-- departments table
+-----+-------------+
| id  | dept_name   |
+-----+-------------+
| 101 | Engineering |
| 102 | Marketing   |
| 104 | Sales       |
+-----+-------------+

Notice: employee Charlie has dep_id = 103 which does not exist in departments. Diana has NULL. Department Sales (104) has no employees. This lets us see how each JOIN handles mismatches and NULLs.

INNER JOIN

Returns only rows where there is a match in both tables. If a row from either table has no match, it is excluded.

  Table A         Table B
 +-------+       +-------+
 |       |       |       |
 |   +---+---+---+---+   |
 |   | MATCHED |   |     |
 |   +---+---+---+---+   |
 |       |       |       |
 +-------+       +-------+
           ^^^^^
       INNER JOIN
  (only the overlap)

Query

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dep_id = d.id;

Result

+-------+-------------+
| name  | dept_name   |
+-------+-------------+
| Alice | Engineering |
| Bob   | Marketing   |
+-------+-------------+
-- Charlie (dep_id 103 has no match) is excluded
-- Diana (NULL dep_id) is excluded
-- Sales (id 104, no employee) is excluded

When to use: When you only want rows with complete relationships. This is the most common JOIN in application code.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matched rows from the right table. If there is no match, right-side columns are filled with NULL.

  Table A         Table B
 +-------+       +-------+
 |       |       |       |
 | +-----+---+---+---+   |
 | | ALL | MATCHED |     |
 | +-----+---+---+---+   |
 |       |       |       |
 +-------+       +-------+
  ^^^^^^^^^^^^^^^
     LEFT JOIN
 (all of A + matches from B)

Query

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dep_id = d.id;

Result

+---------+-------------+
| name    | dept_name   |
+---------+-------------+
| Alice   | Engineering |
| Bob     | Marketing   |
| Charlie | NULL        |  -- No matching department
| Diana   | NULL        |  -- NULL dep_id, no match
+---------+-------------+
-- Sales is still excluded (it's on the right side)

When to use:When you want every row from the primary table even if there is no relationship. Common for reports: "Show all employees and their department, if any."

Finding rows with no match (LEFT JOIN + WHERE NULL)

-- Employees without a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dep_id = d.id
WHERE d.id IS NULL;

-- Result: Charlie, Diana

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN. Returns all rows from the right table, plus matches from the left.

  Table A         Table B
 +-------+       +-------+
 |       |       |       |
 |   +---+---+---+-----+ |
 |   | MATCHED | ALL   | |
 |   +---+---+---+-----+ |
 |       |       |       |
 +-------+       +-------+
           ^^^^^^^^^^^^^^^
            RIGHT JOIN
 (all of B + matches from A)

Query

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dep_id = d.id;

Result

+-------+-------------+
| name  | dept_name   |
+-------+-------------+
| Alice | Engineering |
| Bob   | Marketing   |
| NULL  | Sales       |  -- No employee has dep_id 104
+-------+-------------+
-- Charlie and Diana excluded (they're on the left side)

When to use: Rarely in practice. Most developers rewrite RIGHT JOINs as LEFT JOINs by swapping table order, which reads more naturally. But it exists, and you should understand it.

FULL OUTER JOIN

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

  Table A         Table B
 +-------+       +-------+
 |       |       |       |
 | +-----+---+---+-----+ |
 | | ALL | MATCHED| ALL | |
 | +-----+---+---+-----+ |
 |       |       |       |
 +-------+       +-------+
  ^^^^^^^^^^^^^^^^^^^^^^^
      FULL OUTER JOIN
   (everything from both)

Query

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dep_id = d.id;

Result

+---------+-------------+
| name    | dept_name   |
+---------+-------------+
| Alice   | Engineering |
| Bob     | Marketing   |
| Charlie | NULL        |  -- No matching department
| Diana   | NULL        |  -- NULL dep_id
| NULL    | Sales       |  -- No matching employee
+---------+-------------+

When to use:Data reconciliation. "Show me everything from both sides and highlight where relationships are missing." Also great for finding orphaned records during migration.

Note: MySQL does not support FULL OUTER JOIN directly. You can emulate it with a UNION of LEFT and RIGHT JOINs:

SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dep_id = d.id
UNION
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dep_id = d.id;

CROSS JOIN

Produces a Cartesian product — every row from the left table paired with every row from the right table. No ON condition.

  Table A (4 rows)  x  Table B (3 rows)  =  12 rows

  Every combination:
  (Alice, Engineering), (Alice, Marketing), (Alice, Sales),
  (Bob, Engineering),   (Bob, Marketing),   (Bob, Sales),
  ...

Query

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

-- Equivalent shorthand:
SELECT e.name, d.dept_name
FROM employees e, departments d;

Result

+---------+-------------+
| name    | dept_name   |
+---------+-------------+
| Alice   | Engineering |
| Alice   | Marketing   |
| Alice   | Sales       |
| Bob     | Engineering |
| Bob     | Marketing   |
| Bob     | Sales       |
| Charlie | Engineering |
| Charlie | Marketing   |
| Charlie | Sales       |
| Diana   | Engineering |
| Diana   | Marketing   |
| Diana   | Sales       |
+---------+-------------+
(4 x 3 = 12 rows)

When to use: Generating combinations. For example, creating a schedule where every employee must be paired with every shift, or building a matrix for reporting. Use with caution on large tables (1000 x 1000 = 1,000,000 rows).

SELF JOIN

Not a separate syntax — it is any JOIN where a table joins to itself. Common for hierarchical data like org charts.

-- employees table with manager_id
+----+---------+------------+
| 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;

-- Result:
-- Alice   | NULL    (no manager)
-- Bob     | Alice
-- Charlie | Alice
-- Diana   | Bob

Chaining Multiple JOINs

Real queries often join three or more tables. Each JOIN adds to the result set.

SELECT
  e.name,
  d.dept_name,
  p.project_name
FROM employees e
INNER JOIN departments d ON e.dep_id = d.id
LEFT JOIN projects p ON e.id = p.lead_id
WHERE d.dept_name = 'Engineering'
ORDER BY e.name;

Performance Tips

• Index your JOIN columns. The columns in your ON clause should always be indexed.
• Filter early. Put conditions in the ON clause (not WHERE) when they affect which rows to join.
• Avoid CROSS JOIN on large tables. The row count multiplies — 10K x 10K = 100 million rows.
• Use EXPLAIN ANALYZE to understand how your database executes the JOIN and spot full table scans.
• Prefer INNER JOIN when possible. It gives the optimizer the most freedom to choose the join order.

Summary Table