Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 21 of 49

Relationships between tables

Learn why a database splits data across tables, how primary and foreign keys link them, and what one-to-many and many-to-many relationships look like.

14 minCombining tablesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonRead a foreign key, name the relationship between two tables, and trace by hand how a row in one table points to a row in another.

After this lesson you can look at two tables, say how they are related, and follow a foreign key by hand from one row to the matching row in another table. That skill is the whole idea behind joins, which start in the next lesson.

All the examples use the sample company database from this course. If you have not loaded it yet, see the lesson on the sample database.

Why data is split into tables

Imagine one giant spreadsheet with a row per employee and columns for the department name, the department city, and the manager. Every engineer would repeat "Engineering" and "Pune". If the department moved to another city, you would have to fix every one of those rows, and if you missed one, your data would disagree with itself.

A relational database avoids this by giving each kind of thing its own table. Departments live in departments, people in employees. An employee row stores only a small number, the id of the department, instead of copying its details. The department's name and city exist in exactly one place.

sql
SELECT * FROM departments;
result
+----+-------------+-----------+
| id |    name     |   city    |
+----+-------------+-----------+
|  1 | Engineering | Pune      |
|  2 | Sales       | Mumbai    |
|  3 | Marketing   | Delhi     |
|  4 | Finance     | Mumbai    |
|  5 | Support     | Pune      |
|  6 | Research    | Bengaluru |
+----+-------------+-----------+

Primary keys and foreign keys

Every row in departments has an id that no other row shares. That column is the primary key: it identifies a single row.

Now look at a few employees:

sql
SELECT id, first_name, department_id FROM employees WHERE id <= 6;
result
+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
|  1 | Meera      |             1 |
|  2 | Arjun      |             1 |
|  3 | Kavya      |             1 |
|  4 | Rohan      |             1 |
|  5 | Isha       |             1 |
|  6 | Vikram     |             2 |
+----+------------+---------------+

The department_id column in employees is a foreign key. It does not identify the employee. It stores the primary key of a row in another table. Employee 6, Vikram, has department_id 2, so he belongs to the department whose id is 2 in departments, which is Sales.

You can follow that link yourself with two small queries:

sql
SELECT department_id FROM employees WHERE id = 6;
SELECT id, name, city FROM departments WHERE id = 2;
result
+---------------+
| department_id |
+---------------+
|             2 |
+---------------+
+----+-------+--------+
| id | name  |  city  |
+----+-------+--------+
|  2 | Sales | Mumbai |
+----+-------+--------+

The first query gives the number 2; you carry it to the second query by hand. A join does exactly this lookup for every row at once, and you will write one in the next lesson.

One-to-many

The link between departments and employees is one-to-many: one department can have many employees, but each employee has at most one department. The "many" side holds the foreign key. That is why department_id sits in employees and not the other way round.

You can see the shape by counting employees for each value of the foreign key:

sql
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
ORDER BY department_id;
result
+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
| NULL          |         1 |
|             1 |         6 |
|             2 |         5 |
|             3 |         3 |
|             4 |         2 |
|             5 |         3 |
+---------------+-----------+

Projects follow the same pattern: each project has a department_id for the department that owns it, so one department can own many projects.

Many-to-many and the linking table

Employees and projects are different. Rohan works on more than one project, and every project has several people. Neither table can hold a single foreign key for this, so a third table, assignments, holds pairs: an employee id, a project id and the hours worked. Each assignment row is a link, and the pair of the two ids is its primary key.

sql
SELECT employee_id, project_id, hours FROM assignments WHERE employee_id = 4;
result
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
|           4 |          1 |   410 |
|           4 |          5 |    40 |
+-------------+------------+-------+

Employee 4 appears twice, once for project 1 and once for project 5. Looking up those project ids gives the names:

sql
SELECT id, name FROM projects WHERE id IN (1, 5);
result
+----+-------------------+
| id |       name        |
+----+-------------------+
|  1 | Customer Portal   |
|  5 | Billing Migration |
+----+-------------------+

A linking table is the standard solution for many-to-many data. It has two foreign keys, one to each side.

A table that points to itself

A foreign key may even point into its own table. In employees, manager_id holds the id of another employee:

sql
SELECT id, first_name, manager_id FROM employees WHERE id <= 5;
result
+----+------------+------------+
| id | first_name | manager_id |
+----+------------+------------+
|  1 | Meera      | NULL       |
|  2 | Arjun      |          1 |
|  3 | Kavya      |          1 |
|  4 | Rohan      |          2 |
|  5 | Isha       |          2 |
+----+------------+------------+

Meera (1) has no manager, so her manager_id is NULL. Arjun (2) and Kavya (3) both report to employee 1. Rohan (4) and Isha (5) report to employee 2. This is called a self-reference, and the lesson on self joins shows how to query it.

Gaps are allowed

The foreign key rules say that a value, when present, must exist in the parent table. They do not say a value must be present. Two cases in the sample data matter for later lessons:

  • Employee 18, Ravi, has NULL in department_id. He is a consultant with no department.
  • Department 6, Research, has no employees at all.
sql
SELECT id, first_name, department_id FROM employees WHERE department_id IS NULL;
SELECT d.id, d.name FROM departments d WHERE d.id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL);
result
+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
| 18 | Ravi       | NULL          |
+----+------------+---------------+
+----+----------+
| id |   name   |
+----+----------+
|  6 | Research |
+----+----------+

The second query uses a subquery, a topic covered later in this course; read it as "departments whose id never appears as an employee's department". These two rows are the ones a careless join will lose. Keep them in mind: the lessons on LEFT and FULL joins use them on purpose.

What the database enforces

When a foreign key is declared, the database refuses changes that would break the link. Inserting an employee that points to department 99, which does not exist, fails with a foreign key error. The sample database declares its keys, so the next statement is rejected. The exact error message depends on the database, so it is shown here without output:

sql
INSERT INTO employees (id, first_name, last_name, email, department_id, job_title, salary, hire_date)
VALUES (21, 'Test', 'Person', 'test.person@example.com', 99, 'Tester', 40000, '2025-01-01');

Differences between databases

Foreign keys are standard SQL and work in PostgreSQL, SQL Server and MySQL's InnoDB engine. SQLite parses them but only enforces them when you switch the setting on with PRAGMA foreign_keys = ON, which the sample database does at the top. For MySQL's view of keys, see Foreign keys in MySQL.

Common mistakes

  • Putting the foreign key on the wrong side. The foreign key goes on the "many" side. Employees carry department_id; departments do not carry a list of employees.
  • Trying to store a list in one column. A column such as project_ids holding "1, 5" breaks searching and joining. Use a linking table instead.
  • Assuming every foreign key has a value. Optional relationships leave NULL, as employee 18 shows.
  • Assuming every parent has children. Department 6 has none, and a query that expects a child will silently skip it.
  • Mixing up which id is which. Every table has its own id. A value of 2 in department_id is not employee 2.

Exercise

Using only single-table queries (no joins yet), find the name and city of the department that Kavya Nair works in. Then list the ids of the projects she is assigned to.

Show one possible answer
sql
SELECT id, department_id FROM employees WHERE first_name = 'Kavya' AND last_name = 'Nair';
SELECT id, name, city FROM departments WHERE id = 1;
SELECT project_id FROM assignments WHERE employee_id = 3;
result
+----+---------------+
| id | department_id |
+----+---------------+
|  3 |             1 |
+----+---------------+
+----+-------------+------+
| id |    name     | city |
+----+-------------+------+
|  1 | Engineering | Pune |
+----+-------------+------+
+------------+
| project_id |
+------------+
|          1 |
|          2 |
+------------+

The first query gives Kavya's id (3) and her foreign key (1). Carry the 1 into departments, and the 3 into assignments. A join would do both lookups in a single query.

Key points

  • A relational database stores each kind of thing in its own table and links rows with keys instead of repeating data.
  • A primary key identifies one row. A foreign key is a column that holds the primary key of a row in another table.
  • One-to-many is the common case (one department, many employees). Many-to-many needs a third linking table, like assignments.
  • A foreign key can be NULL (employee 18 has no department), and a parent row can have no children (department 6 has no employees).
  • A join is simply the automatic version of looking up a foreign key value in the other table.

Check yourself

1. Which column in the employees table points to a row in the departments table?
2. Employees and projects are linked by the assignments table. What kind of relationship is that?
3. Employee 18 has NULL in department_id. What does that mean?
4. Why do we store the department name once in departments instead of in every employee row?