How to use MySQL JOIN? INNER, LEFT, RIGHT, FULL JOIN Explained

How to use JOIN in MySQL?

We have to table A and B here:

id	boy
1	Alex
2	Bruce
3	Conor
4	Dicky

and table B

id	girl
1	Alice
2	Brunet
5	Emma
6	Fabia

INNER JOIN

An INNER JOIN of A and B gives the result of A intersect B. It returns all the common records between two tables. If there’s no related record, it will contain NULL.

INNER JOIN

SELECT * FROM a 
INNER JOIN b on a.id = b.id;

It will return:

id	boy	id	girl
1	Alex	1	Alice
2	Bruce	2	Brunet

LEFT JOIN

A LEFT JOIN gives all rows in A, plus any common rows in B. If a record in A doesn’t exist in B, it will return NULL for that row.

LEFT JOIN

SELECT * FROM a 
LEFT JOIN b on a.id = b.id;

It will return

id	boy	id	girl
1	Alex	1	Alice
2	Bruce	2	Brunet
3	Conor	NULL	NULL
4	Dicky	NULL	NULL

RIGHT JOIN

A RIGHT JOIN gives all rows in table B, plus any common rows in A. If a record in B doesn’t exist in A, it will return NULL for that row.

RIGHT JOIN

SELECT * FROM a 
RIGHT JOIN b on a.id = b.id;

id	boy	id	girl
1	Alex	1	Alice
2	Bruce	2	Brunet
NULL	NULL	5	Emma
NULL	NULL	6	Fabia

FULL JOIN

MySQL does not support FULL JOIN, so you have to combine JOIN, UNION and LEFT JOIN to get an equivalent. It gives the results of A union B. It returns all records from both tables. Those columns which exist in only one table will contain NULL in the opposite table.

FULL JOIN

SELECT * FROM a
LEFT JOIN b ON a.id = b.id
UNION
SELECT * FROM a
RIGHT JOIN b ON a.id = b.id

It will return:

id	boy	id	girl
1	Alex	1	Alice
2	Bruce	2	Brunet
3	Conor	NULL	NULL
4	Dicky	NULL	NULL
NULL	NULL	5	Emma
NULL	NULL	6	Fabia

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 

TablePlus