Joining tables: INNER JOIN

This is where relational databases shine. Employees only store the department number; the department name lives in departments. To show both, you join the tables on the shared column.

SELECT e.first_name, e.job_title, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

e and d are short table aliases. ON sets the join condition: foreign key = primary key. Plain JOIN means INNER JOIN: only rows with a match in both tables.

A = the table after FROM, B = the table after JOIN.

You can join more than two tables. This query shows each order line with the customer and product names:

SELECT o.order_id, c.name AS customer, p.name AS product, oi.quantity
FROM orders o
JOIN customers   c  ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id    = oi.order_id
JOIN products    p  ON oi.product_id = p.product_id
ORDER BY o.order_id;