MySQL lets you work with multiple tables at once. One of the main strengths of a database is being able to create relationships between tables, essentially linking them together.

So far, we've only worked with a single table at a time. But in real-life projects, you'll often have several tables in your database, and most of them will be interconnected. This makes it easier to organize your data, avoid unnecessary repetition, and manage everything more cleanly.

Take our video_games table, for instance. Right now, the owner's name is repeated for every game. The name "Patrick" shows up multiple times. Now imagine if we also wanted to store his last name, address, phone number, email... We're not going to copy all that info for each game he owns, are we? It's time to create a new table and link it up! ;)

Modeling a Relationship

If we wanted to store the first name, last name, and email address of every video game owner directly in our video_games table, we'd have no choice but to duplicate that info for every entry... which is way too repetitive. Look what it would look like:

As you can see, Phil's name, surname, and email appear as many times as he owns a game — and it's the same for Patrick and Matt. We absolutely need to avoid this kind of repetition.

So what I suggest is to create another table, say owners, that will store all the details about the video game owners in one place:

This table lists all known game owners and assigns each a unique ID. Each owner appears only once — no duplicates.

Now we need to update our video_games table to reference these owners. The best way to do that is by adding a new field called owner_id, which stores the ID of the owner from the other table:

The new field owner_id is of type INT. It links each game to a specific entry in the owners table.

So now the tables are "connected" through these owner IDs.

Nope, not at all. As far as MySQL is concerned, it's just a number. It doesn't automatically match it with the other table. We'll need to tell it how to connect the two tables in an SQL query. This is where joins come in!

What's a Join?

So now we've got two tables:

• video_games
• owners

It's great that we've split the data into separate tables — it helps avoid duplication on disk.

But when you fetch the list of games and want to display the owner's name too, you'll need to tweak your query to also pull info from the owners table. That's what joins are for.

There are different types of joins depending on exactly what data you want to retrieve. Let's look at the two most important ones:

• Inner joins: Only pull data that exists in both tables (matching entries).
• Outer joins: Pull all the data — even if some entries don't have a match in the other table.

It's really important to understand the difference between inner and outer joins.

Let's say we have a fourth person in our owners table — someone named Ash Ketchum — who doesn't own any games:

Ash is listed in the owners table, but he doesn't appear in the video_games table because he doesn't own any games.

Now if you join the two tables...

• Using an inner join: Ash won't appear in the query results. Inner joins only return data that exists in both tables.
• Using an outer join: You'll get all entries from the owners table — even if they don't have a matching entry in video_games. So Ash, who owns no games, will show up.

So outer joins are more complete — they give you more data. Inner joins are stricter — they only give you what's matched across both tables.

Here's an example of what you'd get with an inner join:

We get the games and their respective owners. But Ash, who owns no games, is completely absent.

Now with an outer join:

Ash now appears! Since he owns no games, the game_title column just shows NULL.

In the next section, we'll see how to actually write these joins in SQL.

Inner Joins

There are two ways to perform an inner join:

• Using the WHERE keyword: this is the old-school syntax — still in use today, so you should know it — but avoid it if you can.
• Using the JOIN keyword: this is the modern syntax, and the one you're encouraged to use. It's more efficient and easier to read.

Both techniques return exactly the same results, but it's important to understand both. 😉

Inner Join with WHERE (Old Syntax)

Step-by-step construction of an inner join

To do this kind of join, we'll select fields from both tables and mention their names in the FROM clause:

SELECT name, first_name FROM owners, video_games

But... that won't work. Why? Because it's not enough. The field name appears in both tables — once for the owner's name, and once for the game's name. That's what we call an ambiguous column, because MySQL doesn't know if it should fetch a person's name (like "Henry") or a game's name (like Sly 3: Honor Among Thieves). In short, it's confused. :p

The trick is to prefix the field with the table name, like this:

SELECT video_games.name, owners.first_name FROM owners, video_games

Now it's crystal clear: we're asking for the game's name and the owner's first name.

The field first_name isn't ambiguous — it only appears in the owners table — so technically we could skip the owners. prefix. But honestly, it doesn't hurt to keep it, and it makes things clearer: you can immediately tell which table the field is coming from.

Now, we still need to link the two tables together. See, games and their owners are connected through the owner_ID field in the video_games table and the ID field in the owners table. We'll link them using a WHERE clause like this:

SELECT video_games.name, owners.first_name
FROM owners, video_games
WHERE video_games.owner_ID = owners.ID

Since the query's getting longer, I'm writing it on multiple lines. That's totally allowed — and way more readable.

This line tells MySQL that video_games.owner_ID matches owners.ID, exactly as we defined in our schema earlier in this chapter.

And that's our full query. Go ahead — try it! :)

You should get something like this:

Use Aliases!

We learned about aliases back when we looked at SQL functions. They let us create "virtual fields" to represent the result of a function.

When doing joins, aliases are strongly recommended. You can alias field names (just like before):

SELECT video_games.name AS game_name, owners.first_name AS owner_first_name
FROM owners, video_games
WHERE video_games.owner_ID = owners.ID

Now you'll get two fields: game_name and owner_first_name. Aliases make the output more readable and meaningful.

You can also assign aliases to table names — which is highly recommended to shorten things up. Usually, you use a letter or two based on the table name:

SELECT vg.name AS game_name, o.first_name AS owner_first_name
FROM owners AS o, video_games AS vg
WHERE vg.owner_ID = o.ID

Here, video_games becomes vg, and owners becomes o. You then reuse these aliases throughout the query — it's shorter, and easier to read in the end. 😄

Note: the AS keyword is optional — developers often leave it out. You can rewrite the query like this:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o, video_games vg
WHERE vg.owner_ID = o.ID

Inner Join with JOIN (New Syntax)

While you can do an inner join with WHERE (as we just saw), it's considered the old way. These days, the recommended approach is to use JOIN instead. Back then, we were used to WHERE being for filtering data — but here, we're using it to link tables and pull in more data.

Here's our previous query using WHERE:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o, video_games vg
WHERE vg.owner_ID = o.ID

Now here's the same query using a JOIN:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o
INNER JOIN video_games vg
ON vg.owner_ID = o.ID

We're fetching data from a primary table (owners) and joining it (INNER JOIN) with another table (video_games). The link between the fields is defined in the ON clause just after.

It works exactly the same — you'll get the same results as with the WHERE version.

If you want to filter results (WHERE), sort them (ORDER BY), or limit them (LIMIT), do that after the ON clause. For example:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o
INNER JOIN video_games vg
ON vg.owner_ID = o.ID
WHERE vg.console = 'PC'
ORDER BY price DESC
LIMIT 0, 10

Translation (take a deep breath before reading):

"Fetch the game name and the owner's first name from the owners and video_games tables. Link them via owner_ID and ID. Only keep the games on PC, sort them by descending price, and only return the first 10 results."

Yeah, those long queries can get a bit intense.

Outer Joins

Outer joins let you fetch all the data — even if there's no match between the tables. So you could get someone like Ash Ketchum listed, even if he doesn't own any video games.

This time, the only available syntax uses JOIN. There are two kinds to know: LEFT JOIN and RIGHT JOIN. They're almost the same, with just one subtle difference we'll explore now.

LEFT JOIN: Fetch everything from the left table

Let's go back to our INNER JOIN and just swap INNER for LEFT:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o
LEFT JOIN video_games vg
ON vg.owner_ID = o.ID

Here, owners is the "left" table and video_games is the "right" table. A LEFT JOIN tells MySQL to fetch all data from the left table (owners) — even if there's no corresponding match in video_games.

Now Ash shows up, even though he doesn't own any games. Since there's no match, the game name column is NULL.

RIGHT JOIN: Fetch everything from the right table

A RIGHT JOIN tells MySQL to fetch all data from the right table, even if there's no match on the left. Here's the query:

SELECT vg.name game_name, o.first_name owner_first_name
FROM owners o
RIGHT JOIN video_games vg
ON vg.owner_ID = o.ID

The "right" table is video_games. So we'll get all the games — even those without an associated owner.

There are two possibilities:

• The owner_ID field has a value not found in the owners table — for example, "57".
• The owner_ID field is NULL — meaning no one owns this game.

This is the case for Pokémon in the table:

Here, Pokémon doesn't belong to anyone. With the RIGHT JOIN query above, you'll still get that row from video_games, even though there's no matching entry in owners.

Here's what that might look like:

This intro to joins should help you see how splitting up information across multiple tables avoids redundancy. This separation gives us flexibility — for example, we can get a simple list of all owners, since they're stored in their own table.

To practice, try doing the same with consoles: make a consoles table with the name, release year, and manufacturer (Nintendo, Koei, Microsoft...). You could even go one step further and make a separate manufacturers table!

And just so you know — you can take things even further with MySQL. If you use InnoDB tables (instead of the default MyISAM), MySQL can track relationships between tables at all times — not just during queries. That means, for example, you could automatically delete all a user's games if you delete the owner from the other table.

Enjoyed this PHP & MySQL course?

If you liked this lesson, you can find the book "How to Build a Website in HTML and CSS" from the same authors, available on SiteRaw, in bookstores and in online libraries in either digital or paperback format. You will find a complete PHP & MySQL workshop with many exclusive bonus chapters.

More information