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