One of the parts of SQL that gets overlooked by many is the use of the JOIN statement.
This little bit of SQL syntax can lead to some quite amazing results from your data.
In this article I look at how to discover if data is missing from a table that is dependant on another table as well as how you can use the JOIN, LEFT JOIN and RIGHT JOIN syntaxes in this endeavour.
Let’s use the following tables:
tblNames
| NameID |
Name |
| 1 |
Frank |
| 2 |
Eva |
| 3 |
Irene |
| 4 |
Adam |
| 5 |
Jane |
|
tblNameFruit
| NameID |
FruitID |
| 1 |
1 |
| 1 |
2 |
| 2 |
1 |
| 3 |
3 |
| 4 |
4 |
| 3 |
4 |
|
tblFruit
| FruitID |
Fruit |
| 1 |
Apple |
| 2 |
Pear |
| 3 |
Apricot |
|
Example One
SELECT
N.Name, NF.FruitID
FROM
tblNames N
INNER JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
This query is an equivalent to doing the following more recognisable query. This may look like a bit of overkill for the same result, but the feature of using a JOIN will become apparent as you see the more interesting results.
SELECT N.Name, NF.FruitID
FROM tblNames N, tblNameFruit NFWHERE N.NameID = NF.NameID
Result
| Name |
FruitID |
| Frank |
1 |
| Frank |
2 |
| Eva |
1 |
| Irene |
3 |
| Irene |
4 |
| Adam |
4 |
Example Two
SELECT
N.Name, NF.NameID, NF.FruitID, F.Fruit
FROM
tblNames N
JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
JOIN
tblFruit F
ON
NF.FruitID = F.FruitID
This example finds the fruit that are liked by people. If there are missing fruit from the fruit table, or a person is missing from the people table, or there are no entries in the favourites, then these results are left out.
Result
| Name |
NameID |
FruitID |
Fruit |
| Frank |
1 |
1 |
Apples |
| Frank |
1 |
2 |
Pear |
| Eva |
2 |
1 |
Apples |
| Irene |
3 |
3 |
Apricot |
Example Three
SELECT
N.Name, NF.NameID, NF.FruitID
FROM
tblNames N
RIGHT JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
Result
| Name |
NameID |
FruitID |
| Frank |
1 |
1 |
| Frank |
1 |
2 |
| Eva |
2 |
1 |
| Irene |
3 |
3 |
| Irene |
3 |
4 |
| Adam |
4 |
4 |
Example Four
This example will list ALL people even if they have no entries in the NameFruit table,
SELECT
N.Name, NF.NameID, NF.FruitID
FROM
tblNames N
LEFT JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
Results
| Name |
NameID |
FruitID |
| Frank |
1 |
1 |
| Frank |
1 |
2 |
| Eva |
2 |
1 |
| Irene |
3 |
3 |
| Irene |
3 |
4 |
| Adam |
4 |
4 |
| Jane |
NULL |
NULL |
Example Five
SELECT
N.Name, NF.NameID, NF.FruitID, F.Fruit
FROM
tblNames N
RIGHT JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
RIGHT JOIN
tblFruit F
ON
NF.FruitID = F.FruitID
Result
| Name |
NameID |
FruitID |
Fruit |
| Frank |
1 |
1 |
Apples |
| Eva |
2 |
1 |
Apples |
| Frank |
1 |
2 |
Pear |
| Irene |
3 |
3 |
Apricot |
Example Six
SELECT
N.Name, NF.NameID, NF.FruitID, F.Fruit
FROM
tblNames N
LEFT JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
LEFT JOIN
tblFruit F
ON
NF.FruitID = F.FruitID
Results
| Name |
NameID |
FruitID |
Fruit |
| Frank |
1 |
1 |
Apples |
| Frank |
1 |
2 |
Pear |
| Eva |
2 |
1 |
Apples |
| Irene |
3 |
3 |
Apricot |
| Irene |
3 |
4 |
NULL |
| Adam |
4 |
4 |
NULL |
| Jane |
NULL |
NULL |
NULL |
As you can see, this shows the people and their fruit, even if they do not have a real Fruit name, and in the case of Jane, she has no entries at all.
Example Seven
One of the best parts of this is that you can do a search for missing data…
SELECT
N.Name, NF.NameID, NF.FruitID, F.Fruit
FROM
tblNames N
LEFT JOIN
tblNameFruit NF
ON
N.NameID = NF.NameID
WHERE
NF.FruitID IS NULL
Results
| Name |
NameID |
FruitID |
| Jane |
(null) |
(null) |
