SQL One-to-Many Relationships and JOINS

To view all the SQL tutorials in chronological order, visit the SQL page

In this post we’ll cover One-to-Many Relationships along with joining tables together. Table relationships are where things start to come together.

In our previous tutorial we had a table with the rows pet_id, pet_name, class, age, family, owner, owner_gender. There’s quite a few problems with having a setup like this. What if a person owns more than one pet? This person would end up in the table multiple times.

What if he changed his name from “Bob” to “Thomas”, or his email address? We’d have to hunt down every single place in the database where Bob showed up and change it to “Thomas”. You’d also have one ginormous table with hundreds, even thousands of columns with TONS of duplicate data.

Setting up a database this way wastes space, clutters up your database, and introduces bugs. It’s just bad.

A better way is to split your data into multiple tables. Instead of putting the owner information in the “pets” table where it obviously doesn’t belong, why don’t we put the “owner” information in an “owners” table and leave the pets in their own table? This will make the tables easier to manage.

Take a look at the two examples below. The first one has everything crammed into one table. The second setup has multiple tables.

One Table

first_name salary pet_name class
Steven 125,000 Snowflake Mammal
Joe 90,000 Coco Reptile
Wyatt 120,000 squack Bird
Joe 90,000 Rex Mammal
Steven 125,000 Ladybug Mammal

The problem here is what if Steven gets fired from his job and is now only making $40,000 at his new job? When the program goes to update his salary, it has to scan the entire database and change the salary field multiple times. Once for the row where he owns “snowflake”, and again in the row where he owns “Ladybug”.

That’s terrible design. What if the update misses a field because there was some update to the database? Now we have some fields where Steven is making $125,000 and other fields where he’s making $40,000… Well which one is it?

Here’s a better way to do it:

Owners table

owner_id first_name salary
25 Steven 125,000
26 Joe 90,000
27 Wyatt 120,000

Pets table

owner_id pet_name class
26 Rex Mammal
25 Snowflake Mammal
27 Squack Bird
26 Coco Reptile
25 Ladybug Mammal

The second example is cleaner, easier to read, and more efficient. It also lets us edit any change ONCE rather than hundreds or even thousands of times. In the entire database, Steven’s salary is recorded in ONE PLACE. If the program ever needs to get Steven’s salary information, we know where to get it! This introduces us to something called “Relationships”, hence the reason MySQL is called a “Relational Database”.

What is a One-to-Many Relationship?

A One-to-Many relationship is where ONE element of the database can have many other elements associated to it. For example. ONE owner can have MANY pets. ONE house can have MANY rooms. etc…

Tables can be related to each other using a “Primary Key” and “Foreign Key”. A “Primary Key” is the unique identifier for a row in a table. Every row should have its own unique “Primary Key”. A “Foreign Key” is a way to connect tables together using the primary key from another table.

Look at the two tables above. Notice that there is an “owner_id” in each table. The “owner_id” in the owners table is the owners “primary key”. In the “pets” table, the owner_id is referencing the owner by his owner_id.

Steven’s “owner_id” is 25, so any pet with the owner_id of 25 belongs to Steven. Can you see which pets belong to Steven and which pets belong to Wyatt and Joe?

Now that we’ve separated our data into multiple tables it’s harder to access that information in useful ways.

For example: Your pet website has a profile page, and you want to be able to give each user a list of all the pets they own. You now have to get information from both the “pets” table AND the “owners” table in order to get the information you need, and this is where JOINS come in.

Creating Some Data to Play With

Before we go any further, let’s get on the same page. Run the following SQL code to create some tables and insert data. Note: We’re starting over if you’ve been following along with the previous tutorials, so delete and re-create the tables.

Create the owners table

CREATE TABLE owners (
    owner_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20),
    age TINYINT,
    occupation VARCHAR(30),
    PRIMARY KEY (owner_id)
);

Insert data into owners table

INSERT INTO owners (owner_id, name, gender, age, occupation)
VALUES
(1, "Alex",     "Male",   15, "Lawn Mower"),
(2, "Caroline", "Female", 34,  "Chemist"),
(3, "Mary",     "Female", 53,  "Personal Trainer"),
(4, "Jason",    "Male",   39,  "Writer"),
(5, "Edwina",   "Female", 29,  "Hobo"),
(6, "Curtis",   "Male",   18,  "Student"),
(7, "Judy",     "Female", 44,  "Welder"),
(8, "Alex",     "None ya bizness",  22, "Programmer"),
(9, "Lucy",     "Female", 6,  "Student"),
(10, "Chris",    "Male",   56,  "Architect"),
(11, "Faith",    "Female", 11,  "Painter"),
(12, "Giovanni", "Male",   32,  "Architect"),
(13, "John",     "Male",   47,  "Cook"),
(14, "Allison",  "Female", 26, "Student"),
(15, "Kate",     "Female", 62,  "Retired"),
(16, "Mary",     "None ya bizness",  37,  "Nutritionist"),
(17, "Thomas",   "Male",   10,  "Student"),
(18, "Owen",     "Male",   26,  "Hobo"),
(19, "Sreehari", "Male",   38,  "Programmer"),
(20, "Zena",     "Female", 25,  "Personal Trainer"),
(21, "Amy",     "Female",   29, "Accountant"),
(22, "Louie",   "Male",     19, "Construction"),
(23, "Monty",   "Male",     14,  "Window Cleaner"),
(24, "Jackie",  "Female",   24,  "Programmer")

Create the pets table and insert pet data

CREATE TABLE pets ( 
    pet_id INT NOT NULL AUTO_INCREMENT,
    pet_name VARCHAR(20) NOT NULL,
    class VARCHAR(30),
    age TINYINT,
    family VARCHAR(30),
    owner_id INT,
    PRIMARY KEY (pet_id)
);

INSERT INTO pets (pet_id, pet_name, class, age, family, owner_id)
VALUES
(1,  "PJ",         "Mammal",    15, "Dog",           13),
(2,  "Buffy",      "Mammal",    14, "Dog",           13),
(3,  "Pixie",      "Mammal",    8,  "Cat",           11),
(4,  "Charlie",    "Mammal",    7,  "Cat",           7),
(5,  "Scooter",    "Mammal",    10, "Squirrel",      7),
(6,  "Smoke",      "Bird",      4,  "Parrot",        8),
(7,  "Butch",      "Reptile",   2,  "Iguana",        14),
(8,  "Rex",        "Plant",     8,  "Venus Flytrap", 9),
(9,  "Snowflake",  "Amphibian", 23, "Salamander",    11),
(10, "Spot",       "Arthropod", 4,  "Black widow",   3),
(11, "Daisy",      "Plant",     2,  "Orchid",        16),
(12, "Squawk",     "Bird",      9,  "Hawk",          12),
(13, "Skeeter",    "Reptile",   3,  "Snake",         13),
(14, "Moonlight",  "Mammal",    6,  "Dog",           13),
(15, "Sunshine",   "Fish",      1,  "Piranha",       3),
(16, "Sandy",      "Fish",      1,  "Koi",           14),
(17, "Wags",       "Mammal",    2,  "Dog",           1),
(18, "Willy",      "Amphibian", 9,  "Frog",          11),
(19, "Tank",       "Bird",      7,  "Eagle",         15),
(20, "Ollie",      "reptile",   14, "Dragon",        7),
(21, "Coco",       "Bird",      11, "Parakeet",      3),
(22, "Luna",       "Arthropod", 2,  "Scorpion",      12),
(23, "Molly",      "Mammal",    5,  "Whale",         3),
(24, "Milly",      "Arthropod", 4,  "Grasshopper",   5),
(25, "Simba",      "Mammal",    8,  "Cat",           3),
(26, "Jaws",       "Fish",      13, "Barracuda",     10),
(27, "Yoshi",      "Reptile",   4,  "Dragon",        20),
(28, "Rango",      "Amphibian", 7,  "Frog",          4),
(29, "Pringle",    "Mammal",    13, "Dolphin",       2),
(30, "Lizzie",     "Reptile",   3,  "Lizard",        6),
(31, "Norbert",    "Bird",      7,  "Magpie",        18),
(32, "Godzilla",   "Reptile",   20, "Dragon",        2),
(33, "Blue",       "Mammal",    40, "Whale",         17),
(34, "Blizzard",   "Plant",     6,  "Orchid",        19),
(35, "Spinner",    "Mammal",    5,  "Dolphin",       19),
(36, "Charmander", "Reptile",   2,  "Lizard",        4)

We also need some pets without owners

INSERT INTO pets (pet_name, class, age, family)
VALUES
("Puffy",    "Mammal",   2,  "Dog"),
("Jingle",    "Bird",   4,  "Parrot"),
("Sandstorm",    "Reptile",   5,  "Lizard"),
("Loggie",    "Reptile",   8,  "Crocodile")

Now that we have some data to play with, let’s just try to get some information. Let’s try to get all of the pets who are owned by “John”. We use to be able to just run something like SELECT * FROM pets WHERE owner = ‘John’, but that’s not going to work anymore because the data is split up into two tables….

Without joins, you’d have to run two separate queries

The first step would be to Figure out what John’s owner_id is.

SELECT * 
FROM owners
WHERE name = 'John';
owner_id name gender age occupation
13 John Male 47 Cook

We get one row, and you can see that John’s owner_id is 13.

Now we can get all the pets with John’s owner_id.

SELECT pet_name, family 
FROM pets
WHERE owner_id = 13;
owner_id pet_name family
13 PJ Dog
13 Buffy Dog
13 Skeeter Snake
13 Moonlight Dog

That works, but it’s painful. Running two separate queries is confusing. Instead, we can temporarily JOIN the tables together by their PRIMARY and FOREIGN keys.

an Introduction to JOINS

A Table JOIN temporarily combines tables together using matching columns. In the example above, the “owner_id” is the matching column, so we’ll join the tables based on the matching owner_id’s. By joining tables together you can write ONE query to get all of the information you need.

In our last tutorial we had both the pets AND the owners all in the same table. That’s pretty much what we’re doing with Table Joins, but it’s only temporary. So instead of putting everything into one table, we can organize our data into multiple tables, and only join them together when we need to.

Now we’ll try the same query again, but with a JOIN. We want to get ALL the pets who are owned by John. Let’s take a look at these tables one more time:

Owners Table

SELECT name, age, owner_id
FROM owners
WHERE owner_id = 13;
name age owner_id
John 47 13

pets table

SELECT owner_id, pet_name, family 
FROM pets
WHERE owner_id = 13;
owner_id pet_name family
13 PJ Dog
13 Buffy Dog
13 Skeeter Snake
13 Moonlight Dog

Notice how BOTH these tables have “owner_id”.

We can use the “owner_id” field as a hook to join the rows together to create a sort of imaginary table using a JOIN.

SELECT name, owners.age, owners.owner_id, pet_name, family
FROM owners
INNER JOIN pets
ON owners.owner_id = pets.owner_id
WHERE owners.owner_id = 13;
name owners_age owner_id pet_name family
John 47 13 PJ Dog
John 47 13 Buffy Dog
John 47 13 Skeeter Snake
John 47 13 Moonlight Dog

Let’s look at this line by line.

SELECT name, owners.age, owners.owner_id, pet_name, family — Grabs all columns you want from the tables that you’re joining together. Notice that some have a prefix. There is an “age” column in both the owners table AND the pets table, so we need to specify which “age” column you want, otherwise you’ll get an “ambiguous” error.

FROM owners This just determines which table is the first table. You could just as easily use pets, and change the next line to owners. By default, this table is the LEFT-most table, while the following tables are added onto the “RIGHT” side.

INNER JOIN pets — uses an “INNER JOIN” to JOIN the pets table onto the owners table. We’ll cover different types of joins a little later.

ON owners.owner_id = pets.owner_id — Combines the columns of BOTH tables when the owners table’s owner_id is equal to the pets table’s owner_id.

WHERE owners.owner_id = 13 — Only grab records that have an owner_id of 13.

Notice how the JOIN table returns duplicate data. We get John’s name and age four times because he has four pets, kind of like when we only had one giant table. By JOINING tables together we get the best of both worlds. We’re able to see our data in any way that we want in one big table without actually having duplicate data.

Just like with previous tutorials, we can specify the exact columns we want. Let’s ONLY get the “pet_name” and “name” (owners name) from the tables and LIMIT the results to 10.

SELECT pet_name, name FROM pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
LIMIT 10;
pet_name name
PJ John
Buffy John
Pixie Faith
Charlie Judy
Scooter Judy
Smoke Alex
Butch Allison
Rex Lucy
Snowflake Faith
Spot Mary

They are connected by “owner_id” though, so let’s add the owner_id to the columns we want.

SELECT pet_name, owner_id, name FROM pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
LIMIT 10;

You’ll hopefully get an error saying “Column owner_id in field list is ambiguous“. The reason is because there’s an owner_id in both tables! Which one do you want? You need to specify.

SELECT pet_name, pets.owner_id, owners.owner_id, name FROM pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
LIMIT 10;
pet_name owner_id owner_id name
PJ 13 13 John
Buffy 13 13 John
Pixie 11 11 Faith
Charlie 7 7 Judy
Scooter 7 7 Judy
Smoke 8 8 Alex
Butch 14 14 Allison
Rex 9 9 Lucy
Snowflake 11 11 Faith
Spot 3 3 Mary

pets.owner_id and owners.owner_id have the format of table.column_name. So “pets.owner_id” means get the “owner_id” of the “pets” table.

Now that we know how to join tables together, it’s much much easier to get specific information. You just use the same WHERE clause that we learned about a while ago. Let’s say we want ALL the pets who are owned by ‘Mary’.

SELECT pet_name, family, pets.owner_id, name
FROM pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
WHERE name = 'Mary';
pet_name family pets_owner_id name
Spot Black widow 3 Mary
Daisy Orchid 16 Mary
Sunshine Piranha 3 Mary
Coco Parakeet 3 Mary
Molly Whale 3 Mary
Simba Cat 3 Mary

Now that you understand how to JOIN tables together, you’ll be able to connect your tables in just about any way you want, and query the database for precisely what you want. To verify your understanding, I encourage you to check out the homework assignments at the bottom of the page.

Different kinds of JOINS

There are several different kinds of JOIN’s. The first table is referred to as the “Left” table. The second table will become the “Right” table.

In the query SELECT * FROM pets INNER JOIN owners ON pets.owner_id = owners.owner_id, the “pets” table is LEFT, and the “owners” table is RIGHT.

INNER JOIN

The JOIN we used above is an “Inner Join”, which means it only returns records where both tables have a matching column. (The match being owner_id in the example above)

LEFT JOIN

A LEFT JOIN returns ALL records in the LEFT table even if they don’t have a match, and combines the rows with a matching column on the RIGHT table to the left table.

Here’s a query that demonstrates a LEFT join:

SELECT pet_name, pets.owner_id, name
from pets
LEFT JOIN owners
ON pets.owner_id = owners.owner_id

Notice how “pets” is the “LEFT” table, and we get ALL rows from the pets table.

pet_name owner_id name
PJ 13 John
Buffy 13 John
Pixie 11 Faith
Charlie 7 Judy
Scooter 7 Judy
Smoke 8 Alex
Butch 14 Allison
Rex 9 Lucy
Snowflake 11 Faith
Spot 3 Mary
Daisy 16 Mary
Squawk 12 Giovanni
Skeeter 13 John
Moonlight 13 John
Sunshine 3 Mary
Sandy 14 Allison
Wags 1 Alex
Willy 11 Faith
Tank 15 Kate
Ollie 7 Judy
Coco 3 Mary
Luna 12 Giovanni
Molly 3 Mary
Milly 5 Edwina
Simba 3 Mary
Jaws 10 Chris
Yoshi 20 Zena
Rango 4 Jason
Pringle 2 Caroline
Lizzie 6 Curtis
Norbert 18 Owen
Godzilla 2 Caroline
Blue 17 Thomas
Blizzard 19 Sreehari
Spinner 19 Sreehari
Charmander 4 Jason
Puffy
Jingle
Sandstorm
Loggie

RIGHT JOIN

A RIGHT JOIN returns ALL records in the RIGHT table, and combines the matching records in the LEFT table to the right. It’s the exact opposite of a LEFT join.

Here’s an example of a RIGHT JOIN.

SELECT pet_name, pets.owner_id, name
from pets
RIGHT JOIN owners
ON pets.owner_id = owners.owner_id

Notice how this time we don’t get the pets without owners, but we get the owners without pets!

pet_name owner_id name
PJ 13 John
Buffy 13 John
Pixie 11 Faith
Charlie 7 Judy
Scooter 7 Judy
Smoke 8 Alex
Butch 14 Allison
Rex 9 Lucy
Snowflake 11 Faith
Spot 3 Mary
Daisy 16 Mary
Squawk 12 Giovanni
Skeeter 13 John
Moonlight 13 John
Sunshine 3 Mary
Sandy 14 Allison
Wags 1 Alex
Willy 11 Faith
Tank 15 Kate
Ollie 7 Judy
Coco 3 Mary
Luna 12 Giovanni
Molly 3 Mary
Milly 5 Edwina
Simba 3 Mary
Jaws 10 Chris
Yoshi 20 Zena
Rango 4 Jason
Pringle 2 Caroline
Lizzie 6 Curtis
Norbert 18 Owen
Godzilla 2 Caroline
Blue 17 Thomas
Blizzard 19 Sreehari
Spinner 19 Sreehari
Charmander 4 Jason
Amy
Louie
Monty
Jackie

FULL OUTER JOIN

A FULL OUTER JOIN returns all records from both tables whether there is a match or not. MySQL actually doesn’t support full outer joins, so we have to improvise.

Because FULL OUTER JOIN is not supported in MySQL, it’s a little bit more complicated to do. We won’t go into much detail, but I’ll provide an example, and you’re free to reverse engineer it and look it up in the manual.

The code below uses “UNION” to join a LEFT and RIGHT join together to create a FULL OUTER JOIN. The “UNION” joins together two separate queries into one. This particular method of the “FULL OUTER JOIN” removes all the duplicate rows if there are any:

SELECT pet_name, family, pets.owner_id, name, occupation FROM pets
LEFT JOIN owners ON pets.owner_id = owners.owner_id
UNION
SELECT pet_name, family, pets.owner_id, name, occupation FROM pets
RIGHT JOIN owners ON pets.owner_id = owners.owner_id

The Magic is at the bottom. Notice how we get BOTH sides of the tables. We get the owners without pets, AND the pets without owners….

pet_name family owner_id name occupation
PJ Dog 13 John Cook
Buffy Dog 13 John Cook
Pixie Cat 11 Faith Painter
Charlie Cat 7 Judy Welder
Scooter Squirrel 7 Judy Welder
Smoke Parrot 8 Alex Programmer
Butch Iguana 14 Allison Student
Rex Venus Flytrap 9 Lucy Student
Snowflake Salamander 11 Faith Painter
Spot Black widow 3 Mary Personal Trainer
Daisy Orchid 16 Mary Nutritionist
Squawk Hawk 12 Giovanni Architect
Skeeter Snake 13 John Cook
Moonlight Dog 13 John Cook
Sunshine Piranha 3 Mary Personal Trainer
Sandy Koi 14 Allison Student
Wags Dog 1 Alex Lawn Mower
Willy Frog 11 Faith Painter
Tank Eagle 15 Kate Retired
Ollie Dragon 7 Judy Welder
Coco Parakeet 3 Mary Personal Trainer
Luna Scorpion 12 Giovanni Architect
Molly Whale 3 Mary Personal Trainer
Milly Grasshopper 5 Edwina Hobo
Simba Cat 3 Mary Personal Trainer
Jaws Barracuda 10 Chris Architect
Yoshi Dragon 20 Zena Personal Trainer
Rango Frog 4 Jason Writer
Pringle Dolphin 2 Caroline Chemist
Lizzie Lizard 6 Curtis Student
Norbert Magpie 18 Owen Hobo
Godzilla Dragon 2 Caroline Chemist
Blue Whale 17 Thomas Student
Blizzard Orchid 19 Sreehari Programmer
Spinner Dolphin 19 Sreehari Programmer
Charmander Lizard 4 Jason Writer
Puffy Dog
Jingle Parrot
Sandstorm Lizard
Loggie Crocodile
Amy Accountant
Louie Construction
Monty Window Cleaner
Jackie Programmer

Creating a Proper Table Relationship

When you’re creating a table with a “foreign Key”, you want to identify the column specifically as a foreign key, and make sure you reference the “Primary key” from the table it’s associated with.

Because the “pets” are associated with the “owners”, we reference the matching column from the owners table.

The reason you want to set it up officially is so you don’t accidentally create bugs. What happens when you delete an “owner” from the owners table? If there’s a pet belonging to that owner, you’ll now have a pet with an owner that no longer exists!

By setting things up correctly you can define what happens in these situations. Does the pet’s table just set the pet’s owner_id to NULL? Does it DELETE the pet? Does it refuse to let you delete the owner? YOU have to decide.

For more information, check out the documentation!

MySQL Foreign Key Page

We already ran a query for creating the pets table, so the below code is just for demonstration. If you want to run this, then delete the tables you already have and create the pets table with the query below, then re-populate the tables.

The line ON DELETE SET NULL is where you decide what happens. This will set the “owner_id”
of the pets table to NULL on the rows where the pet belonged to the deleted owner.

CREATE TABLE pets ( 
    pet_id INT NOT NULL AUTO_INCREMENT,
    pet_name VARCHAR(20) NOT NULL,
    class VARCHAR(30),
    age TINYINT,
    family VARCHAR(30),
    owner_id INT,
    FOREIGN KEY (owner_id)
        REFERENCES owners(owner_id)
        ON DELETE SET NULL,
    PRIMARY KEY (pet_id)
);

I hope that was clear!!! Give the assignments below a try to verify your understanding. Leave a comment and let me know how it goes 🙂

Assignments:

NOTE: There may be various ways to solve the problems.

1. Get all pet_name’s that are owned by owners with the occupation of “Programmer”

2. Get The Occupation, name, and class fields for all rows where the pet is a Bird.

3. Get the all the rows where the owner is a female OR the class is a Reptile. ONLY when the pet has an owner

3.5 Same as 3, but including pets that are Reptiles without homes.

4. Get all rows where the owner is a male, even if he does not currently own a pet.

5. Get all owners that do not own a pet

6. Get all pets that do not have an owner

Solutions:

1. Get all pet_name’s that are owned by owners with the occupation of “Programmer”

SELECT pet_name, name, occupation
from pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
WHERE occupation = "Programmer";

2. Get The occupation, name, pet_name, and class fields for all rows where the pet is a Bird.

SELECT occupation, name, pet_name, class
from pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
WHERE class = "Bird";

3. Get the all the rows where the owner is a female OR the class is a Reptile. ONLY when the pet has an owner

SELECT name, gender, pet_name, family, class
from pets
INNER JOIN owners
ON pets.owner_id = owners.owner_id
WHERE gender = "Female" OR class = "Reptile";

3.5 Same as 3, but including pets without homes.

SELECT name, gender, pet_name, family, class
from pets
LEFT JOIN owners
ON pets.owner_id = owners.owner_id
WHERE gender = "Female" OR class = "Reptile";

4. Get all rows where the owner is a male, even if he does not currently own a pet.

SELECT name, gender, pet_name, family, class
from pets
RIGHT JOIN owners
ON pets.owner_id = owners.owner_id
WHERE gender = "Male";  

5. Get all owners that do not own a pet

SELECT occupation, name, pet_name, class
from pets
RIGHT JOIN owners
ON pets.owner_id = owners.owner_id
WHERE pet_name IS NULL;

6. Get all pets that do not have an owner

SELECT pet_name, family, pets.owner_id
from pets
LEFT JOIN owners
ON pets.owner_id = owners.owner_id
WHERE pets.owner_id IS NULL;

You must have an account and be logged in to solve challenges and lessons!

Submit a Comment

Your email address will not be published. Required fields are marked *