MySQL JOINs Explained

For all the years I've worked with MySQL, it took a poor interview to realize that I've been flying by the seat of my pants when it comes to the depth of my knowledge of MySQL. The difference between the different JOINs in MySQL is a handy thing to fully grasp.

Here is a real quick definition of "LEFT" in your queries: The "left" table is the table that is left of the JOIN phrase in your query. So, in this example...

SELECT * FROM contact c, phone_number p WHERE c.id=p.contact_id

...the LEFT table is "contact" and the right table is the "phone_number" table. It is actually pretty simple.

In JOINs, if you are not specific about LEFT or RIGHT, a simple JOIN will return only rows matching in the ON equation of the JOIN.

Sample Tables and Data

Here are the two tables I will be referencing in the samples below (* denotes primary key):

contact
id * name city
1AndyTempe
2DanTampa
3NadinePhoenix
4AaronDetroit
5BethAtlanta
6BrittMiami
7PhilipChicago
8AdeChicago
9GeneAtlanta
10JohnReno

phone_number
id * number contact_id number_type
148055544881Mobile
248055512341Home
361555551502Mobile
460255522223Office
548055512343Home
624855512124Mobile
786455598765Home
824855510005Mobile

Simple JOIN

A VERY simple JOIN is one where you just find rows from one table that have a match in another table. You don't really even need to use the JOIN syntax. Take a look:

SELECT * FROM contact c, phone_number p WHERE c.id=p.contact_id

Results:

1	Andy	Tempe	1	4805554488	1	Mobile
1	Andy	Tempe	2	4805551234	1	Home
2	Dan	Tampa	3	6155555150	2	Mobile
3	Nadine	Phoenix	4	6025552222	3	Office
3	Nadine	Phoenix	5	4805551234	3	Home
4	Aaron	Detroit	6	2485551212	4	Mobile
5	Beth	Atlanta	7	8645559876	5	Home
5	Beth	Atlanta	8	2485551000	5	Mobile

Simple JOIN using the JOIN keyword

This will accomplish the same thing as the simple JOIN above:

SELECT * FROM contact c
JOIN phone_number p ON c.id=p.contact_id

Results:

1	Andy	Tempe	1	4805554488	1	Mobile
1	Andy	Tempe	2	4805551234	1	Home
2	Dan	Tampa	3	6155555150	2	Mobile
3	Nadine	Phoenix	4	6025552222	3	Office
3	Nadine	Phoenix	5	4805551234	3	Home
4	Aaron	Detroit	6	2485551212	4	Mobile
5	Beth	Atlanta	7	8645559876	5	Home
5	Beth	Atlanta	8	2485551000	5	Mobile

RIGHT JOIN

RIGHT JOINs give you all the matching rows for the right table and link in rows from the left table that match. NULL is given for missing left table columns.

SELECT * FROM contact c
RIGHT JOIN phone_number p ON c.id=p.contact_id

1	Andy	Tempe	1	4805554488	1	Mobile
1	Andy	Tempe	2	4805551234	1	Home
2	Dan	Tampa	3	6155555150	2	Mobile
3	Nadine	Phoenix	4	6025552222	3	Office
3	Nadine	Phoenix	5	4805551234	3	Home
4	Aaron	Detroit	6	2485551212	4	Mobile
5	Beth	Atlanta	7	8645559876	5	Home
5	Beth	Atlanta	8	2485551000	5	Mobile

For ODBC compatibility, you can also stuff "OUTER" in between the "RIGHT" and the "JOIN". It will give the exact same results.

LEFT JOIN

LEFT JOIN retrieves ALL of the rows matching your WHERE and ties to each row any matching row from the JOINed table (the "right" table). Where there are no rows in the right table that match, NULL is returned.

SELECT * FROM contact c
LEFT JOIN phone_number p ON c.id=p.contact_id

Results:

1	Andy	Tempe	1	4805554488	1	Mobile
1	Andy	Tempe	2	4805551234	1	Home
2	Dan	Tampa	3	6155555150	2	Mobile
3	Nadine	Phoenix	4	6025552222	3	Office
3	Nadine	Phoenix	5	4805551234	3	Home
4	Aaron	Detroit	6	2485551212	4	Mobile
5	Beth	Atlanta	7	8645559876	5	Home
5	Beth	Atlanta	8	2485551000	5	Mobile
6	Britt	Miami	[NULL]	[NULL]	[NULL]	[NULL]
7	Philip	Chicago	[NULL]	[NULL]	[NULL]	[NULL]
8	Ade	Chicago	[NULL]	[NULL]	[NULL]	[NULL]
9	Gene	Atlanta	[NULL]	[NULL]	[NULL]	[NULL]
10	John	Reno	[NULL]	[NULL]	[NULL]	[NULL]

For ODBC compatibility, you can also stuff "OUTER" in between the "LEFT" and the "JOIN". It will give the exact same results.