Joining two tables: INNER JOIN

Up to now we've read one table at a time. In the real world, however, data is spread across multiple related tables: in our dataset, for example, each employee has a department_id that points to the departments table. To see the name of the department (and not just its id) we need to join the two tables. The operator that does this is called JOIN.

The basic syntax

SELECT columns
FROM   table_a
JOIN   table_b ON table_a.x = table_b.y;

Read in English: "take table_a, attach table_b to it by pairing every row where table_a.x equals table_b.y". The result is a new virtual table with the columns of both.

-- For each employee, also show the name of their department:
SELECT employees.first_name,
       employees.last_name,
       departments.name AS dipartimento
FROM   employees
JOIN   departments ON employees.department_id = departments.id;

The part after ON is called the join condition: it's what tells the engine "these two rows are about the same thing, line them up". In the vast majority of cases it's an equality between a primary key and a foreign key (here departments.id and employees.department_id).

INNER JOIN, in detail

The full keyword is INNER JOIN, but JOIN on its own is the standard synonym. "Inner" means that only the rows that have a match in both tables are returned:

• if an employee has department_id = NULL, they will not appear in the result;
• if a department has no employees, it will not appear in the result.

Qualifying column names

When two tables have columns with the same name (here both have id, and both have name if you think about it), you need to specify which table a column comes from by writing table.column. To avoid repeating long names, aliases are often used (you saw them in Module 2):

SELECT e.first_name, e.last_name, d.name AS dipartimento
FROM   employees   AS e
JOIN   departments AS d ON e.department_id = d.id;

AS is optional here too: FROM employees e works the same.

Try it

Review exercise