Module 3: Working with Multiple Tables•15 min

Understanding Table Relationships

Why Tables Are Separate

In the first 2 Modules, every query is pulled from a single table. That works for simple questions such as “What are the employee names?” or “Which orders cost more than $50?”

But real business questions usually need data from multiple places. “Show me each order with the customer’s name and email.” That information lives in two different tables: orders and customers.

This separation isn’t a bug. It’s by design. Databases split data across tables to avoid redundancy, maintain accuracy, and stay organized. Before you can combine tables, you need to understand how they relate.

Primary Keys: Unique Identifiers

Every table needs a way to uniquely identify each row. That’s what a primary key does.

Look at the customers table:

Table: customers

id	first_name	last_name	city	phone_number
8	John	Joseph	San Francisco	928-386-8164
7	Jill	Michael	Austin	813-297-0692
4	William	Daniel	Denver	813-368-1200
5	Henry	Jackson	Miami	808-601-7513
13	Emma	Isaac	Miami	808-690-5201

The id column is the primary key. Each value is unique. No two customers share the same id. This lets you reference a specific customer unambiguously.

Primary key formats

Primary keys are almost always integers that auto-increment (1, 2, 3…). Some databases use UUIDs instead, but the concept is the same: one unique value per row.

Foreign Keys: The Connection

A foreign key is a column in one table that references the primary key of another table. It’s how tables link together.

Table: orders

id	cust_id	order_date	order_details	total_order_cost
1	3	2019-03-04	Coat	100
2	3	2019-03-01	Shoes	80
3	3	2019-03-07	Skirt	30
4	7	2019-02-01	Coat	25
5	7	2019-03-10	Shoes	80

The cust_id column is a foreign key. It references the id column in the customers table. Order ID 4 belongs to customer ID 7. Order ID 6 belongs to customer ID 15.

Foreign keys aren’t unique

Foreign keys don’t have to be unique. Customer ID 3 has multiple orders. That’s perfectly normal.

One-to-Many Relationships

The most common relationship in databases. One record in Table A can relate to many records in Table B.

One customer can have many orders
One department can have many employees
One author can write many books

The “one” side has the primary key. The “many” side has the foreign key.

We explored the customers and orders tables above. They had a one-to-many relationship.

How to actually join the two tables is coming in the next few lessons. For now, let’s just explore how many orders each customer has placed — using the orders table alone.

Tables: customers, orders

One-to-One Relationships

Less common. Each record in Table A relates to exactly one record in Table B, and vice versa.

One employee has one employee profile
One user has one user settings record

You might wonder: why not put all that data in one table? Sometimes tables are split for performance (rarely accessed data in a separate table) or for organizational clarity.

Many-to-Many Relationships

When records on both sides can relate to multiple records on the other side.

Students can enroll in many courses; courses have many students
Products can be in many orders; orders contain many products

Databases can’t directly represent many-to-many. Instead, we use a junction table (also called a linking table or bridge table).

Junction Tables

Consider students and courses. We need a third table:

SQL

-- students: id, name
-- courses: id, course_name
-- enrollments: student_id, course_id ← Junction table

The enrollments table has two foreign keys. Each row represents one student enrolled in one course. This breaks the many-to-many into two one-to-many relationships.

Spotting junction tables

When you see a table with mostly foreign keys (like order_items or enrollments), it’s probably a junction table for a many-to-many relationship.

Identifying Relationships in Schema

When you encounter a new database, look for:

Columns ending in _id (foreign keys)
Tables with names combining two other tables (order_items, student_courses)
Column names that match other table names

Here are two tables from an Airbnb dataset. At first glance they look related — both have nationality, gender, and age. But are they actually connected? Look closely.

Table: airbnb_hosts

host_id	nationality	gender	age
0	USA	M	28
1	USA	F	29
2	China	F	31
3	China	M	24
4	Mali	M	30

Table: airbnb_guests

guest_id	nationality	gender	age
0	Mali	M	21
1	China	F	23
2	Mali	F	27
3	Australia	F	24
4	Luxembourg	M	19

There’s no foreign key. nationality, gender, and age are shared column names, but the values describe different people — a USA male guest aged 28 isn’t the same person as a USA male host aged 28. Shared column names don’t make tables related; you need an explicit foreign key to connect them.

Key Takeaways

Primary keys uniquely identify each row in a table
Foreign keys reference primary keys in other tables, creating relationships
One-to-many is the most common relationship (one customer, many orders)
Many-to-many requires a junction table with two foreign keys
Look for _id columns and combined table names to identify relationships

What’s Next

Now that you understand how tables relate, it’s time to connect them. In the next lesson, you’ll learn UNION — a way to stack results from multiple queries together.

Next upContinue →

Combining Results with UNION

40 min