Utopian Labs Blog

As a part of my job I’m frequently “forced” to try to explain to junior (and sometimes senior) engineers how to write SQL queries (although I in no way claim that I’m a SQL Guru). What seems to need more explanation is how to use joins and I’ve found out that using simple diagrams helps the learning process. We are going to use the same method and accompany it with a simple example of two tables with two columns each.

The tables

Table A

id	Name
1	John
2	Mary
3	Vicky
4	Mark

Table B

id	Name
1	Jim
2	John
3	Christina
4	Vicky

Inner Join

Description

The inner join will result in a table containing only the rows that exist in both tables

Diagram

Code

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.Name = TableB.Name

Result

id	Name	id	Name
1	John	2	John
3	Vicky	4	Vicky

Full Outer Join

Description

The full outer join will result in a table containing all the rows that exist in the tables. If no match is found on the other table, a null value will be returned.

Diagram

Code

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.Name = TableB.Name

Result

id	Name	id	Name
1	John	2	John
2	Mary	null	null
3	Vicky	4	Vicky
4	Mark	null	null
null	null	1	Jim
null	null	3	Christina

Left Outer Join

Description

The left outer join will result in a table containing all the rows that exist in the first table with the matching rows of of the second table. If no match is found on the other table, a null value will be returned.

Diagram

Code

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Name = TableB.Name

Result

id	Name	id	Name
1	John	2	John
2	Mary	null	null
3	Vicky	4	Vicky
4	Mark	null	null

Get entries that exist only in one table

Description

In order to get entries that exist only in one table we can perform a left outer join and keep only the entries where the tableB columns have null values

Diagram

Code

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Name = TableB.Name
WHERE tableB.Name IS NULL

Result

id	Name	id	Name
2	Mary	null	null
4	Mark	null	null

Get entries that are unique to each table

Description

In order to get entries that are unique to each table, we must perform a full outer join and keep the entries that do not have a value for both table’s columns.

Diagram

Code

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.Name = TableB.Name
WHERE TableA.Name IS NULL OR TableB.Name IS NULL

Result

id	Name	id	Name
2	Mary	null	null
4	Mark	null	null
null	null	1	Jim
null	null	3	Christina