What is the difference between “INNER JOIN” and “OUTER JOIN”?

What is the difference between “INNER JOIN” and “OUTER JOIN”

Also, LEFT OUTER JOIN, RIGHT OUTER JOIN

Let’s say we have to table A here:

id	boy
1	Alex
2	Bruce
3	Conor
4	Dicky

and table B

id	girl
1	Alice
2	Brunet
5	Emma
6	Fabia

1. 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.

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

2. A LEFT OUTER 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.

SELECT
	*
FROM
	a
	LEFT OUTER 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

3. A RIGHT OUTER 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.

SELECT
	*
FROM
	a
	RIGHT OUTER JOIN b ON a.id = b.id;

It will return:

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

4. A FULL OUTER JOIN of A and B 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.

This FUL OUTER JOIN can potentially return a huge results-set.

SELECT
	*
FROM
	a
	FULL OUTER JOIN b ON a.id = b.id;

Note: MySQL does not support FULL JOIN, so you have to combine JOIN, UNION and LEFT 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 CLient for relational databases? 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.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.