After this lesson you can write a query that pulls related data from two tables into one result, for example each employee next to the name of their department, and you can explain why some rows are missing from it.
The idea
In the previous lesson you followed a foreign key by hand: you read department_id from an employee and then looked that number up in departments. An inner join automates that for all rows at once. It pairs rows from two tables whenever a condition you give is true, and discards everything else.
SELECT e.first_name, e.last_name, d.name AS department
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
WHERE e.id <= 6;+------------+-----------+-------------+
| first_name | last_name | department |
+------------+-----------+-------------+
| Meera | Iyer | Engineering |
| Arjun | Rao | Engineering |
| Kavya | Nair | Engineering |
| Rohan | Das | Engineering |
| Isha | Kapoor | Engineering |
| Vikram | Singh | Sales |
+------------+-----------+-------------+Read it in three parts:
FROM employees AS estarts with the employees table and gives it the short namee.INNER JOIN departments AS dsays to bring in the departments table, calledd.ON e.department_id = d.idis the matching rule: keep a pair of rows only when the employee's foreign key equals the department's primary key.
The SELECT list can then pick columns from either side.
Aliases and ambiguous columns
Table aliases such as e and d keep queries short, and they matter when both tables contain a column with the same name. Here both employees and departments have id. A bare id would leave the database guessing, so it refuses with an "ambiguous column" error. The exact wording depends on the database, so the failing query is shown without output:
SELECT id, first_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;Prefix the column with its table or alias, e.id or d.id, and the problem goes away. A good habit is to prefix every column in a join, even when it is not strictly required, so the query is clear to the next reader. The AS keyword for table aliases is optional in most databases, and Oracle is the usual exception that does not accept it for tables.
Filtering and sorting joined rows
Once the join is done you have one wide set of rows. WHERE, ORDER BY and LIMIT work on it as normal:
SELECT d.name AS department, e.first_name, e.job_title, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.city = 'Mumbai' AND e.salary >= 70000
ORDER BY d.name, e.salary DESC;+------------+------------+-------------------+--------+
| department | first_name | job_title | salary |
+------------+------------+-------------------+--------+
| Finance | Lata | Finance Manager | 115000 |
| Finance | Omar | Accountant | 70000 |
| Sales | Vikram | Sales Director | 150000 |
| Sales | Neha | Account Executive | 90000 |
| Sales | Sameer | Account Executive | 90000 |
+------------+------------+-------------------+--------+The WHERE clause here uses d.city, a column that only exists because of the join. That is one of the main reasons to join: you can filter employees by a property that lives in another table.
Joining and summarising
Joins and GROUP BY combine naturally. To see the payroll and headcount for each department by name:
SELECT d.name AS department, COUNT(*) AS employees, SUM(e.salary) AS payroll
FROM departments d
INNER JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY payroll DESC;+-------------+-----------+---------+
| department | employees | payroll |
+-------------+-----------+---------+
| Engineering | 6 | 650000 |
| Sales | 5 | 432000 |
| Marketing | 3 | 242000 |
| Support | 3 | 197000 |
| Finance | 2 | 185000 |
+-------------+-----------+---------+Notice that only five departments appear. Research has no employees, so it never forms a pair, and the inner join drops it.
Which rows disappear
An inner join only returns rows where the condition is true. The sample data has two rows that never match:
SELECT COUNT(*) AS all_employees FROM employees;
SELECT COUNT(*) AS joined_employees
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;+---------------+
| all_employees |
+---------------+
| 20 |
+---------------+
+------------------+
| joined_employees |
+------------------+
| 19 |
+------------------+There are 20 employees, but the join returns 19. Ravi, employee 18, has NULL in department_id, and NULL = anything is never true, so his row finds no partner. Department 6 is lost in the same way from the other side.
This is not a bug. It is exactly what an inner join promises. When you need those rows, you use an outer join, which is the subject of the next lesson. Silent loss is the thing to watch for: if a report must include every employee, an inner join may quietly give you the wrong total.
A join that fans out
When one row on the left matches several rows on the right, the left row is repeated once per match. That is why joining projects to assignments produces one line per person on each project:
SELECT p.name AS project, a.employee_id, a.hours
FROM projects p
INNER JOIN assignments a ON a.project_id = p.id
WHERE p.id = 3
ORDER BY a.hours DESC;+-----------------+-------------+-------+
| project | employee_id | hours |
+-----------------+-------------+-------+
| Spring Campaign | 11 | 210 |
| Spring Campaign | 12 | 190 |
| Spring Campaign | 10 | 80 |
+-----------------+-------------+-------+Project 3 appears three times, once for each assignment. Keep this in mind when you sum values from the "one" side after joining the "many" side, because they get counted repeatedly.
Older comma syntax and USING
You will meet two other ways to write the same join. The old style lists the tables with a comma and puts the matching rule in WHERE:
SELECT e.first_name, d.name
FROM employees e, departments d
WHERE e.department_id = d.id AND e.id <= 3;+------------+-------------+
| first_name | name |
+------------+-------------+
| Meera | Engineering |
| Arjun | Engineering |
| Kavya | Engineering |
+------------+-------------+It works, but it is easy to forget the WHERE and accidentally pair every row with every row. The explicit JOIN ... ON form keeps the matching rule next to the join and is the one to prefer.
When the two columns have the same name, USING (column) is a shorthand for ON. It is not useful here because the keys are named id and department_id, so the sample tables mostly need ON.
Differences between databases
INNER JOIN with ON works the same in MySQL, PostgreSQL, SQLite and SQL Server. The comma syntax also works in all of them. For MySQL-specific notes, see INNER JOIN in MySQL.
Common mistakes
- Forgetting the ON condition. PostgreSQL and SQL Server reject a JOIN with no ON as a syntax error, but MySQL and SQLite accept it and return every combination of rows. Always state how the tables match.
- Joining on the wrong columns. Matching
e.id = d.idis valid SQL and returns rows, but it pairs employee 2 with department 2. The result looks plausible and is wrong. - Not noticing lost rows. Compare row counts before and after the join when the totals matter.
- Leaving columns unqualified. An unprefixed
idornameis ambiguous when both tables have one. - Summing after a fan-out. A department budget joined to many employees is repeated once per employee.
Exercise
List each project with the name of the department that owns it, ordered by budget from highest to lowest. Show the project name, the department name and the budget. Then list the projects owned by departments in Pune.
Show one possible answer
SELECT p.name AS project, d.name AS department, p.budget
FROM projects p
INNER JOIN departments d ON p.department_id = d.id
ORDER BY p.budget DESC;
SELECT p.name AS project, d.city
FROM projects p
INNER JOIN departments d ON p.department_id = d.id
WHERE d.city = 'Pune';+-------------------+-------------+--------+
| project | department | budget |
+-------------------+-------------+--------+
| Mobile App | Engineering | 650000 |
| Customer Portal | Engineering | 400000 |
| Billing Migration | Finance | 300000 |
| Q3 Sales Push | Sales | 200000 |
| Spring Campaign | Marketing | 120000 |
+-------------------+-------------+--------+
+-----------------+------+
| project | city |
+-----------------+------+
| Customer Portal | Pune |
| Mobile App | Pune |
+-----------------+------+Key points
- INNER JOIN combines each row of one table with every row of the other table where the ON condition is true.
- Rows without a match on the other side are dropped. In the sample data that loses employee 18 and department 6.
- Use short table aliases and put the table name in front of any column that exists in both tables, such as e.id and d.id.
- After the join, WHERE, ORDER BY and aggregates work on the combined rows as usual.