Relational databases, such as MySQL, give you the ability to organize data into separate tables, but link the tables together to form relationships when necessary.
MySQL joins give you the ability to link data together in a MySQL database. A join is a way to get columns from more than one table into a single set of results. This is usually much more efficient than trying to perform multiple queries and combining them later.
This article looks at the different types of joins that can be performed in MySQL and goes over the different options you have to combine data from multiple tables: inner joins, left and right joins, and full outer joins.
To further our understanding of joins, we’ll create a simple database of grocery items, each item having a category. Categories are stored in the
categories table and items are stored in a separate
CREATE TABLE categories (id int PRIMARY KEY AUTO_INCREMENT,name varchar(250) NOT NULL);
categories table populated with data:
CREATE TABLE items (id int PRIMARY KEY AUTO_INCREMENT,name varchar(512),category_id int NULL);
items table populated with data:
We’ll build on this example throughout the tutorial to explore the different types of joins and how to use them.
Now that we have items and categories stored, we may want to display the items along with the category name instead of just the
category_id, since “Deli” is more meaningful to a human than “2”.
To do this, we can use an
INNER JOIN, which selects matching records between tables. This is the default behavior for JOIN, so
INNER JOIN is the same as
If you do a join without an
ON clause, you will do what is sometimes called a
CROSS JOIN, which will show each row in the left table once for every row in the right table. This is not usually what we want — and it produces a lot more results.
Let’s look at an example of a cross join in MySQL:
-- Don’t do this unless you know what you are doing:SELECT * FROM itemsJOIN categories; -- No ON columns specified!
We end up with way more results than we bargained for! With lots of data, this would be a big mess.
To get the results we want, we must say which columns are related.
In other words, we have to say that the primary key (
categories relates to the foreign key (
items. This is what it looks like in MySQL:
SELECT * FROM items-- JOIN is the same as INNER JOINJOIN categories ON items.category_id = categories.id;
Now, we have conveniently returned the category name along with each item!
You may notice in the table above that there are now two
name fields since both tables had their own
name column. To make the query more usable, we can use MySQL aliases to output the columns AS something else.
For this example, we’ll use
SELECT * FROM items AS i -- we now refer to items as iJOIN categories AS c -- we now refer to categories as cON i.category_id = c.id;
AS is optional, so we will often see it left out.
It is also a good idea to specify all the columns we want to return instead of requesting all of them with
*, especially when using tables with many columns, as this can make queries run faster. In this example, let’s omit the category
Because the same column names are selected more than once, we should also specify which tables these columns come from. We can use the
c aliases for that.
SELECTi.id,i.name,i.category_id,c.name AS category_name -- now refer to categories.name AS category_nameFROM items iJOIN categories c ON i.category_id = c.id;
Now a useful result is returned that we can display to a user!
To recap, we use MySQL inner joins to combine the data from two tables by a relationship. There is a left table — the first table specified after
FROM (in this case,
items), and a right table, specified after the
categories) in our example.
The inner join can be represented by this Venn diagram showing that the only data returned is the data where items and categories are related.
Let’s say we add more data to our tables:
- an item without a category
- a new category (but no items that use it yet).
If we do an
INNER JOIN on this data, it looks like this in MySQL:
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iJOIN categories c ON i.category_id = c.id;
Notice anything missing?
Bread isn’t there! Why not?
When we do an inner join on
i.category_id = c.id, we are telling MySQL to return only the records with a category. Since "Bread" has a
category_id that is
NULL, it doesn’t match anything and therefore isn’t returned.
Similarly, since no items have our new “Dairy” category, this will not be present in the results either.
Often, you will still want to return all the items, even those that don’t have a matching foreign key in the table it is joined to. To achieve this, we can use
LEFT JOIN to ensure all the item records in the first (left) table are returned.
RIGHT JOIN works almost exactly the same way, except it returns all the records in the right table — in this case,
If we do a
LEFT JOIN on this data, we will get the following:
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iLEFT JOIN categories c ON i.category_id = c.id;
Now we have all the items thanks to using a
LEFT JOIN instead of
Similarly, we can use a
RIGHT JOIN to return all the categories (but not necessarily all the items).
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iRIGHT JOIN categories c ON i.category_id = c.id;
Left and right joins can be represented by these Venn diagrams.
If we want to show all the items and all the categories, we must do a special join that is sometimes called a
FULL OUTER JOIN, although this type of join is not supported in MySQL. We can, however, simulate this by doing both a
LEFT JOIN and
RIGHT JOIN, and combining them with a
To accomplish this, we have to add a
WHERE clause that only includes the records with a
id from the second part of the query. Otherwise, those items with categories will all show twice.
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iLEFT JOIN categories c ON i.category_id = c.idUNION ALLSELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iRIGHT JOIN categories c ON i.category_id = c.id-- This prevents duplicate items from showing-- as we only want categories with no items.WHERE i.id IS NULL;
This type of
OUTER JOIN is represented by this diagram.
It is sometimes helpful to query for only the records that aren’t related. We may want to find only the items that aren’t categorized — perhaps so we can find them to clean them up.
To do this, we can add an additional
WHERE clause to a
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iLEFT JOIN categories c ON i.category_id = c.idWHERE c.id IS NULL;
JOIN is represented here.
To show only the categories without items, we can use a similar
RIGHT JOIN with a
WHERE clause that only shows records with a
SELECTi.id,i.name,i.category_id,c.name AS category_nameFROM items iRIGHT JOIN categories c ON i.category_id = c.idWHERE i.id IS NULL;
JOIN is represented here.
Finally, if we want to show both the unrelated items and categories, we can use the
OUTER JOIN type of query, but look for either the
categories keys being
To make this query work, it helps to enclose the bulk of it in parentheses and apply the
WHERE clause to the outer query.
SELECT * FROM (SELECT i.id,i.name,i.category_id,c.name AS category_nameFROM items iLEFT JOIN categories c ON i.category_id = c.idUNION ALLSELECT i.id,i.name,i.category_id,c.name AS category_nameFROM items iRIGHT JOIN categories c ON i.category_id = c.idWHERE i.id IS NULL) AS all_items_all_categoriesWHERE id IS NULL OR category_id IS NULL;
JOIN of unrelated items is represented here.
You should now understand how to use MySQL joins to combine data from multiple tables and how each type of join differs. To summarize:
JOINreturns only records with matching keys in both tables.
LEFT JOINreturns records from the first table only if they also are referenced by the second table.
RIGHT JOINreturns records from the second table only if they also are referenced by the first table.
FULL OUTER JOINreturns all records from both tables, even if they don’t have a match in the other table.
WHEREcan filter results of a join to only show records with
UNIONcan combine results of two queries into one result set.
With a good understanding of joins, you are on your way to doing powerful and efficient queries in MySQL. To learn even more about joins, you can check out our overview of MySQL joins video as well as our video on indexing joins in MySQL.