Skip to content
ohsudo, Home

Joining tables: INNER, LEFT and self joins

Good databases split data across tables, and joins bring it back together. The most useful trick here is finding what's missing, with a LEFT JOIN.

● A real database, running in your browser. Nothing you type leaves this page.

Exercise 1 of 6

Two tables

JOIN … ON matches rows from two tables. Short names after each table, such as e and d, keep it readable.

Your task

Show each employee's first_name, with their department's name.

Tables and columns
    or Ctrl+Enter

    What you'll practice

    1. Two tables. JOIN … ON matches rows from two tables. Short names after each table, such as e and d, keep it readable.
    2. Filter a join. After the join, you can use WHERE on columns from either table.
    3. Join and count. Joins and grouping work together, and now you can show names rather than ids.
    4. Find what's missing. A LEFT JOIN keeps every row of the left table, with NULL where there's no match. So WHERE right.id IS NULL finds rows with no match at all.
    5. Nobody on a project. It's the same idea, on a table that links two others.
    6. A table with itself. A manager is also an employee, so employees joins to itself, with two short names. A LEFT JOIN keeps those who have no manager.

    See also