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

Tagged: , ,

Leave a Reply

What’s this?

You are currently reading SQL Joins Simply Explained at Utopian Labs Blog.

meta