Several JOINs, aliases and self-join

Table aliases

Writing products.name and categories.name over and over gets tedious. An alias is a short name you assign to a table with AS (or without it):

SELECT p.name, c.name AS category
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.id;

p and c are the aliases. From there you refer to the columns as p.name and c.name. It is shorter and more readable.

Several chained JOINs

You can combine more than two tables: each JOIN adds a new table with its own ON. They are evaluated top to bottom, chaining the matches:

SELECT p.name, c.name, e.name
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.id
INNER JOIN employees  AS e ON c.manager_id = e.id;

Self-join: a table with itself

Sometimes a table relates to itself. Think of employees, where each one has a manager_id that points to another employee in the same table:

To show each employee next to their manager's name we join employees with employees. Since we cannot have the same table twice with the same name, the aliases are mandatory: one for the employee, another for the manager.

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

Here e is "the employee" and m is "their manager": both are the same table, but the ON relates them as two distinct roles.

With INNER JOIN, Ana (who has no manager) does not appear, because her manager_id is NULL and matches no one. If you wanted to include her with a NULL manager, you would use LEFT JOIN.

Examples

Self-join: each employee and their manager's name

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