SQL Joins visuell erklaert: INNER, LEFT, RIGHT, FULL, CROSS

SQL Joins Explained with Visual Examples

SQL Joins are the backbone of relational database queries. They allow you to combine rows from two or more tables based on a related column. Understanding how different join types work is essential for writing efficient queries, building reports, and working with normalized databases. This guide explains each join type with clear visual diagrams, practical examples, and performance tips.

Sample Tables

All examples in this guide use these two tables. The department_id column connects employees to their departments.

-- employees table
+----+----------+---------------+
| id | name     | department_id |
+----+----------+---------------+
| 1  | Alice    | 1             |
| 2  | Bob      | 1             |
| 3  | Carol    | 2             |
| 4  | Dave     | 3             |
| 5  | Eve      | NULL          |
+----+----------+---------------+

-- departments table
+----+-------------+
| id | name        |
+----+-------------+
| 1  | Engineering |
| 2  | Marketing   |
| 3  | Sales       |
| 4  | HR          |
+----+-------------+

-- Notice: Eve has no department (NULL)
-- Notice: HR department has no employees

INNER JOIN

Returns only matching rows from both tables. If a row in either table has no match in the other, it is excluded from the result. This is the most common and default join type.

--  Visual:
--  employees       departments
--  ┌─────────┐     ┌────────────┐
--  │ Alice  1│─────│1 Engineering│
--  │ Bob    1│─────│             │
--  │ Carol  2│─────│2 Marketing  │
--  │ Dave   3│─────│3 Sales      │
--  │ Eve NULL│     │4 HR         │  ← No match
--  └─────────┘     └────────────┘
--     ↑ No match

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

-- Result:
-- +----------+-------------+
-- | employee | department  |
-- +----------+-------------+
-- | Alice    | Engineering |
-- | Bob      | Engineering |
-- | Carol    | Marketing   |
-- | Dave     | Sales       |
-- +----------+-------------+
-- Eve is excluded (no department_id)
-- HR is excluded (no employees)

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. Where there is no match, the right side columns contain NULL. Use this when you want to keep all records from the primary table even if they have no related data.

--  Visual:
--  employees       departments
--  ┌─────────┐     ┌────────────┐
--  │ Alice  1│─────│1 Engineering│
--  │ Bob    1│─────│             │
--  │ Carol  2│─────│2 Marketing  │
--  │ Dave   3│─────│3 Sales      │
--  │ Eve NULL│     │4 HR         │  ← Not included
--  └─────────┘     └────────────┘
--  All left rows    Only matches

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

-- Result:
-- +----------+-------------+
-- | employee | department  |
-- +----------+-------------+
-- | Alice    | Engineering |
-- | Bob      | Engineering |
-- | Carol    | Marketing   |
-- | Dave     | Sales       |
-- | Eve      | NULL        |  ← Included with NULL
-- +----------+-------------+

Finding Rows with No Match

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

-- Result: Eve

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table. This is the mirror of LEFT JOIN. In practice, LEFT JOIN is used more often because most people read queries left to right.

--  Visual:
--  employees       departments
--  ┌─────────┐     ┌────────────┐
--  │ Alice  1│─────│1 Engineering│
--  │ Bob    1│─────│             │
--  │ Carol  2│─────│2 Marketing  │
--  │ Dave   3│─────│3 Sales      │
--  │ Eve NULL│     │4 HR         │  ← Included with NULL
--  └─────────┘     └────────────┘
--  Only matches    All right rows

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

-- Result:
-- +----------+-------------+
-- | employee | department  |
-- +----------+-------------+
-- | Alice    | Engineering |
-- | Bob      | Engineering |
-- | Carol    | Marketing   |
-- | Dave     | Sales       |
-- | NULL     | HR          |  ← HR included with NULL employee
-- +----------+-------------+

FULL OUTER JOIN

Returns all rows from both tables. Where there is no match, the missing side contains NULL. This combines LEFT JOIN and RIGHT JOIN to give you the complete picture.

--  Visual:
--  employees       departments
--  ┌─────────┐     ┌────────────┐
--  │ Alice  1│─────│1 Engineering│
--  │ Bob    1│─────│             │
--  │ Carol  2│─────│2 Marketing  │
--  │ Dave   3│─────│3 Sales      │
--  │ Eve NULL│     │4 HR         │  ← Included
--  └─────────┘     └────────────┘
--  All rows        All rows

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

-- Result:
-- +----------+-------------+
-- | employee | department  |
-- +----------+-------------+
-- | Alice    | Engineering |
-- | Bob      | Engineering |
-- | Carol    | Marketing   |
-- | Dave     | Sales       |
-- | Eve      | NULL        |  ← No department
-- | NULL     | HR          |  ← No employees
-- +----------+-------------+

-- Note: MySQL does not support FULL OUTER JOIN directly.
-- Workaround using UNION:
SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

CROSS JOIN

Returns the Cartesian product of both tables — every row from the left table paired with every row from the right table. With 5 employees and 4 departments, you get 20 rows.

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

-- Returns 5 x 4 = 20 rows (every possible combination)
-- Alice-Engineering, Alice-Marketing, Alice-Sales, Alice-HR,
-- Bob-Engineering, Bob-Marketing, ...

-- Practical use case: Generate a calendar matrix
SELECT months.name, years.year
FROM (SELECT 'Jan' AS name UNION SELECT 'Feb' UNION SELECT 'Mar') months
CROSS JOIN (SELECT 2025 AS year UNION SELECT 2026) years;

SELF JOIN

A self join joins a table with itself. This is useful for hierarchical data like org charts, category trees, or finding related records within the same table.

-- employees table with manager_id
-- +----+--------+------------+
-- | id | name   | manager_id |
-- +----+--------+------------+
-- | 1  | Alice  | NULL       |  ← CEO, no manager
-- | 2  | Bob    | 1          |
-- | 3  | Carol  | 1          |
-- | 4  | Dave   | 2          |
-- | 5  | Eve    | 2          |
-- +----+--------+------------+

-- Find each employee's 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    |
-- | Bob      | Alice   |
-- | Carol    | Alice   |
-- | Dave     | Bob     |
-- | Eve      | Bob     |
-- +----------+---------+

NATURAL JOIN

Automatically joins on columns with the same name in both tables. While convenient, it can produce unexpected results if column names change. Explicit joins are preferred in production code.

-- If both tables have a column named "department_id"
SELECT *
FROM employees
NATURAL JOIN departments;

-- Equivalent to:
SELECT *
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Warning: If tables share multiple column names,
-- NATURAL JOIN matches ALL of them, which may not be intended.

Multiple Table Joins

You can chain multiple joins to combine data from three or more tables. Each join adds another table to the result set.

-- Three tables: employees, departments, projects
SELECT
  e.name AS employee,
  d.name AS department,
  p.name AS project
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON e.id = p.lead_id;

-- You can mix join types
SELECT
  d.name AS department,
  e.name AS employee,
  p.name AS project
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN projects p ON e.id = p.lead_id
ORDER BY d.name, e.name;

Join Conditions: ON vs WHERE vs USING

-- ON clause: defines the join condition
SELECT * FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- USING clause: shorthand when column names match
SELECT * FROM employees
INNER JOIN departments USING (department_id);
-- Only works when the join column has the same name in both tables

-- WHERE clause: filters AFTER the join
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';
-- Warning: with LEFT JOIN, a WHERE on the right table
-- effectively converts it to an INNER JOIN!

-- Correct way to filter with LEFT JOIN:
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Engineering';
-- Keeps all employees, only shows department for Engineering

Join Performance Tips

Index join columns - Always create indexes on columns used in ON clauses. This is the single most impactful optimization.
Use INNER JOIN when possible - INNER JOIN is typically faster than OUTER JOIN because the optimizer has more flexibility.
Filter early - Move conditions from WHERE to ON when using LEFT JOIN, or use subqueries to reduce the dataset before joining.
Avoid SELECT * - Only select the columns you need. This reduces memory usage and network transfer.
Check EXPLAIN output - Use EXPLAIN or EXPLAIN ANALYZE to understand the query execution plan.
Consider join order - Most optimizers handle this automatically, but for complex queries, start with the smallest table.
Use EXISTS instead of IN for subqueries - EXISTS stops at the first match, while IN materializes the entire subquery result.

Common Join Patterns

Find Records That Exist in Both Tables

-- Using INNER JOIN
SELECT a.* FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;

-- Using EXISTS (often faster for large datasets)
SELECT * FROM table_a a
WHERE EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id);

Find Records Missing from Another Table

-- Using LEFT JOIN + IS NULL (anti-join)
SELECT a.* FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.a_id IS NULL;

-- Using NOT EXISTS
SELECT * FROM table_a a
WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id);

-- Using NOT IN (careful with NULLs!)
SELECT * FROM table_a
WHERE id NOT IN (SELECT a_id FROM table_b WHERE a_id IS NOT NULL);

Get Latest Record Per Group

-- Find the most recent order for each customer
SELECT c.name, o.order_date, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN (
  SELECT customer_id, MAX(order_date) AS max_date
  FROM orders
  GROUP BY customer_id
) latest ON o.customer_id = latest.customer_id
  AND o.order_date = latest.max_date;

-- Using window functions (modern SQL)
SELECT name, order_date, total
FROM (
  SELECT c.name, o.order_date, o.total,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.order_date DESC) AS rn
  FROM customers c
  INNER JOIN orders o ON c.id = o.customer_id
) ranked
WHERE rn = 1;

Join Types Quick Reference

Join Type	Left Unmatched	Right Unmatched	Use Case
INNER JOIN	Excluded	Excluded	Only matching records
LEFT JOIN	Included (NULL)	Excluded	All from left + matches
RIGHT JOIN	Excluded	Included (NULL)	All from right + matches
FULL OUTER JOIN	Included (NULL)	Included (NULL)	All from both tables
CROSS JOIN	All combinations	All combinations	Cartesian product
SELF JOIN	Depends on join type	Same table	Hierarchical data

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns all rows from the left table, filling in NULL for columns from the right table when there is no match. Use INNER JOIN when you only need complete records, and LEFT JOIN when you need all records from the primary table regardless of whether related data exists.

Does the order of tables in a JOIN matter?

For INNER JOIN and CROSS JOIN, the order does not affect the result (only column order in SELECT). For LEFT JOIN and RIGHT JOIN, order matters because it determines which table keeps all its rows. Modern query optimizers rearrange INNER JOINs automatically for best performance.

When should I use JOIN vs a subquery?

Use JOIN when you need columns from multiple tables in the result. Use subqueries for existence checks (EXISTS, IN) or when you need to aggregate before joining. In most databases, the optimizer converts between them automatically, but joins are generally more readable.

Why does my LEFT JOIN act like an INNER JOIN?

This happens when you add a WHERE condition on the right table. Filtering on a right-table column in WHERE eliminates the NULL rows, converting the LEFT JOIN into an INNER JOIN. Move the condition to the ON clause instead to preserve the LEFT JOIN behavior.

How do I join on multiple conditions?

Use AND in the ON clause: ON a.id = b.a_id AND a.type = b.type. You can add as many conditions as needed. Each condition narrows the match criteria.

Is FULL OUTER JOIN supported in MySQL?

MySQL does not support FULL OUTER JOIN directly. The workaround is to UNION a LEFT JOIN with a RIGHT JOIN. PostgreSQL, SQL Server, and Oracle all support FULL OUTER JOIN natively.