SQL Joins Simply Explained
May 1st, 2012 § Leave a Comment
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 |
Leave a Reply