All You Need To Know About Joins In MySQL

When To Use a Join?

There comes a time when answering a question with data that not all the information needed is within a singular table. That is where knowing how to join tables becomes invaluable.

We will go through an understanding of Joins and how to apply them in MySQL.

What are Joins?

Joins help you connect multiple tables that have a column in common. The tables below have a relationship, highlighted in red, that we can use to connect the tables.

Employees

ID	first_name	last_name	office_code	boss_ID
100	Donatello	Bianchi	001	null
101	Ugo	Cavallo	001	100
102	Gionata	Lemmi	001	100
103	Madeline	Fournier	002	100
104	Luuk	Roosa	002	100
105	Niall	McCabe	003	101
106	Rosalva	Rojas	null	101
107	Kostis	Antonis	null	101

Offices

office_ID	city	phone_number
001	Seattle	+1202-555-0183
002	Dublin	+353 01 918 3457
003	Sydney	+61 2 5550 9137
004	Busan	+82 2 407 5914

Types of Joins

• LEFT JOIN
• RIGHT JOIN
• INNER JOIN
  • SELF-JOIN

Left Join

The returned table shows all rows from the left table (employees) and rows that have a matching value with the right table (offices).

SELECT * 
FROM employees
LEFT JOIN offices
ON employee.office_code = offices.office_ID

ID	first_name	last_name	office_code	boss_ID	office_ID	city	phone_number
100	Donatello	Bianchi	001	null	1	Seattle	+1202-555-0183
101	Ugo	Cavallo	001	100	1	Seattle	+1202-555-0183
102	Gionata	Lemmi	001	100	1	Seattle	+1202-555-0183
103	Madeline	Fournier	002	100	2	Dublin	+353 01 918 3457
104	Luuk	Roosa	002	100	2	Dublin	+353 01 918 3457
105	Niall	McCabe	003	101	3	Sydney	+61 2 5550 9137
106	Rosalva	Rojas	null	101	null	null	null
107	Kostis	Antonis	null	101	null	null	null

Right Join

The returned table shows all rows from the right table (offices) and rows that have a matching value with the left table (employees).

SELECT * 
FROM employees
RIGHT JOIN offices
ON employees.office_code = offices.office_ID

ID	first_name	last_name	office_code	boss_ID	office_ID	city	phone_number
null	null	null	null	null	4	Busan	+82 2 407 5914
100	Donatello	Bianchi	001	null	1	Seattle	+1202-555-0183
101	Ugo	Cavallo	001	100	1	Seattle	+1202-555-0183
102	Gionata	Lemmi	001	100	1	Seattle	+1202-555-0183
103	Madeline	Fournier	002	100	2	Dublin	+353 01 918 3457
104	Luuk	Roosa	002	100	2	Dublin	+353 01 918 3457
105	Niall	McCabe	003	101	3	Sydney	+61 2 5550 9137

As you can see above, the second table (offices) returned the Busan office details, although there are no recorded employees at this location.

Inner Join

The table will only return rows with the same value in common from both tables.

SELECT * 
FROM employees
INNER JOIN offices
ON employees.office_code = offices.office_ID

ID	first_name	last_name	office_code	boss_ID	office_ID	city	phone_number
100	Donatello	Bianchi	001	null	1	Seattle	+1202-555-0183
101	Ugo	Cavallo	001	100	1	Seattle	+1202-555-0183
102	Gionata	Lemmi	001	100	1	Seattle	+1202-555-0183
103	Madeline	Fournier	002	100	2	Dublin	+353 01 918 3457
104	Luuk	Roosa	002	100	2	Dublin	+353 01 918 3457
105	Niall	McCabe	003	101	3	Sydney	+61 2 5550 9137

Self-Join

Duplicates a singular table, treating each version as unique and joining them into one. But why would you need a table to do this? In the case of hierarchical data.

Each row in the Employees’ table references their boss’s ID number. We may want the information of the higher-up to show on the same row as the subordinate.

-- I'm narrowing down the columns viewed after the join
SELECT emp.ID, emp.first_name, emp.last_name, emp.office_code, 
       emp.boss_ID, boss.first_name AS boss_fname, 
       boss.last_name as boss_lname, boss.office_code AS boss_office_code
FROM employees AS emp
INNER JOIN employees AS boss
ON emp.boss_ID= office_table.ID

ID	first_name	last_name	office_code	boss_ID	boss_fname	boss_lname	office_code
101	Ugo	Cavallo	001	100	Donatello	Bianchi	001
102	Gionata	Lemmi	001	100	Donatello	Bianchi	001
103	Madeline	Fournier	002	100	Donatello	Bianchi	001
104	Luuk	Roosa	002	100	Donatello	Bianchi	001
105	Niall	McCabe	003	101	Ugo	Cavallo	001
106	Rosalva	Rojas	null	101	Ugo	Cavallo	001
107	Kostis	Antonis	null	101	Ugo	Cavallo	001

Tweet