Joining Tables: INNER JOIN

So far every query has looked at one table. Real questions usually need data from multiple tables — that's what JOIN is for.

Why we need joins

The employees table only stores a department_id number — not the department's name. To show employee names alongside their department name, we need to combine employees with departments.

INNER JOIN syntax

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

Breaking this down:

• e and d are aliases — short nicknames for the tables, used to keep column references unambiguous and queries shorter.
• ON e.department_id = d.id is the join condition — it tells the database how the two tables relate.
• INNER JOIN returns only rows where the condition matches on both sides. An employee with a department_id that doesn't exist in departments (or a NULL one) would be excluded entirely.

Joining three or more tables

Chain joins together to pull in as many related tables as you need:

SELECT o.id AS order_id, c.customer_name, e.first_name AS handled_by, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN employees e ON o.employee_id = e.id;

Joining with a WHERE clause

SELECT e.first_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.location = 'Lahore';

JOIN vs INNER JOIN

Plain JOIN is exactly the same as INNER JOIN — the INNER keyword is optional but recommended for clarity, especially once you start using outer joins in the next article.

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;