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_namesalarypet_nameclass
Steven125,000SnowflakeMammal
Joe90,000CocoReptile
Wyatt120,000squackBird
Joe90,000RexMammal
Steven125,000LadybugMammal

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_idfirst_namesalary
25Steven125,000
26Joe90,000
27Wyatt120,000

Pets table

owner_idpet_nameclass
26RexMammal
25SnowflakeMammal
27SquackBird
26CocoReptile
25LadybugMammal

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_idnamegenderageoccupation
13JohnMale47Cook

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_idpet_namefamily
13PJDog
13BuffyDog
13SkeeterSnake
13MoonlightDog

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;
nameageowner_id
John4713

pets table

SELECT owner_id, pet_name, family
FROM pets
WHERE owner_id = 13;
owner_idpet_namefamily
13PJDog
13BuffyDog
13SkeeterSnake
13MoonlightDog

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;
nameowners_ageowner_idpet_namefamily
John4713PJDog
John4713BuffyDog
John4713SkeeterSnake
John4713MoonlightDog

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_namename
PJJohn
BuffyJohn
PixieFaith
CharlieJudy
ScooterJudy
SmokeAlex
ButchAllison
RexLucy
SnowflakeFaith
SpotMary

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_nameowner_idowner_idname
PJ1313John
Buffy1313John
Pixie1111Faith
Charlie77Judy
Scooter77Judy
Smoke88Alex
Butch1414Allison
Rex99Lucy
Snowflake1111Faith
Spot33Mary

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_namefamilypets_owner_idname
SpotBlack widow3Mary
DaisyOrchid16Mary
SunshinePiranha3Mary
CocoParakeet3Mary
MollyWhale3Mary
SimbaCat3Mary

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_nameowner_idname
PJ13John
Buffy13John
Pixie11Faith
Charlie7Judy
Scooter7Judy
Smoke8Alex
Butch14Allison
Rex9Lucy
Snowflake11Faith
Spot3Mary
Daisy16Mary
Squawk12Giovanni
Skeeter13John
Moonlight13John
Sunshine3Mary
Sandy14Allison
Wags1Alex
Willy11Faith
Tank15Kate
Ollie7Judy
Coco3Mary
Luna12Giovanni
Molly3Mary
Milly5Edwina
Simba3Mary
Jaws10Chris
Yoshi20Zena
Rango4Jason
Pringle2Caroline
Lizzie6Curtis
Norbert18Owen
Godzilla2Caroline
Blue17Thomas
Blizzard19Sreehari
Spinner19Sreehari
Charmander4Jason
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_nameowner_idname
PJ13John
Buffy13John
Pixie11Faith
Charlie7Judy
Scooter7Judy
Smoke8Alex
Butch14Allison
Rex9Lucy
Snowflake11Faith
Spot3Mary
Daisy16Mary
Squawk12Giovanni
Skeeter13John
Moonlight13John
Sunshine3Mary
Sandy14Allison
Wags1Alex
Willy11Faith
Tank15Kate
Ollie7Judy
Coco3Mary
Luna12Giovanni
Molly3Mary
Milly5Edwina
Simba3Mary
Jaws10Chris
Yoshi20Zena
Rango4Jason
Pringle2Caroline
Lizzie6Curtis
Norbert18Owen
Godzilla2Caroline
Blue17Thomas
Blizzard19Sreehari
Spinner19Sreehari
Charmander4Jason
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_namefamilyowner_idnameoccupation
PJDog13JohnCook
BuffyDog13JohnCook
PixieCat11FaithPainter
CharlieCat7JudyWelder
ScooterSquirrel7JudyWelder
SmokeParrot8AlexProgrammer
ButchIguana14AllisonStudent
RexVenus Flytrap9LucyStudent
SnowflakeSalamander11FaithPainter
SpotBlack widow3MaryPersonal Trainer
DaisyOrchid16MaryNutritionist
SquawkHawk12GiovanniArchitect
SkeeterSnake13JohnCook
MoonlightDog13JohnCook
SunshinePiranha3MaryPersonal Trainer
SandyKoi14AllisonStudent
WagsDog1AlexLawn Mower
WillyFrog11FaithPainter
TankEagle15KateRetired
OllieDragon7JudyWelder
CocoParakeet3MaryPersonal Trainer
LunaScorpion12GiovanniArchitect
MollyWhale3MaryPersonal Trainer
MillyGrasshopper5EdwinaHobo
SimbaCat3MaryPersonal Trainer
JawsBarracuda10ChrisArchitect
YoshiDragon20ZenaPersonal Trainer
RangoFrog4JasonWriter
PringleDolphin2CarolineChemist
LizzieLizard6CurtisStudent
NorbertMagpie18OwenHobo
GodzillaDragon2CarolineChemist
BlueWhale17ThomasStudent
BlizzardOrchid19SreehariProgrammer
SpinnerDolphin19SreehariProgrammer
CharmanderLizard4JasonWriter
PuffyDog
JingleParrot
SandstormLizard
LoggieCrocodile
AmyAccountant
LouieConstruction
MontyWindow Cleaner
JackieProgrammer

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.

  1. Get all rows where the owner is a male, even if he does not currently own a pet.
  2. Get all owners that do not own a pet
  3. 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";

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";

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";

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";

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;

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;

Leave a Comment