SQL - JOIN

I'm currently teaching database & SQL to a couple of students and I think I'll blog a little bit about the caveats of SQL based on that experience.

There are things that we can solve (quite easily) with SQL for which you normally wouldn't use SQL. We want to know all combinations of two integers 1..9 that add up to a total of ten. To do that, we create a table containing the numbers one to nine:

mysql> SELECT * FROM `NUMBERS`;
+--------+
| NUMBER |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
+--------+

Now we join this table with itself without using a join condition and then we select all rows where left plus right equals ten:

mysql> SELECT * FROM `NUMBERS` `L` 
       JOIN `NUMBERS` `R` 
       WHERE `L`.`NUMBER` + `R`.`NUMBER` = 10;
+--------+--------+
| NUMBER | NUMBER |
+--------+--------+
|      9 |      1 |
|      8 |      2 |
|      7 |      3 |
|      6 |      4 |
|      5 |      5 |
|      4 |      6 |
|      3 |      7 |
|      2 |      8 |
|      1 |      9 |
+--------+--------+

However, we can get the same result by using a join condition instead of the WHERE:

mysql> SELECT * FROM `NUMBERS` `L` 
       JOIN `NUMBERS` `R` 
        ON `L`.`NUMBER` + `R`.`NUMBER` = 10;
+--------+--------+
| NUMBER | NUMBER |
+--------+--------+
|      9 |      1 |
|      8 |      2 |
|      7 |      3 |
|      6 |      4 |
|      5 |      5 |
|      4 |      6 |
|      3 |      7 |
|      2 |      8 |
|      1 |      9 |
+--------+--------+

We can also easily adapt this query to find all the combinations of three integers that add up to ten. Or can we? Let's try it:

mysql> SELECT * FROM `NUMBERS` `L` 
       JOIN `NUMBERS` `R` JOIN `NUMBERS` 
       `W` WHERE `L`.`NUMBER` + `R`.`NUMBER` + `W`.`NUMBER` = 10 
       LIMIT 5;
+--------+--------+--------+
| NUMBER | NUMBER | NUMBER |
+--------+--------+--------+
|      8 |      1 |      1 |
|      7 |      2 |      1 |
|      6 |      3 |      1 |
|      5 |      4 |      1 |
|      4 |      5 |      1 |
+--------+--------+--------+

Ok, fine. But that was easy. Now let's say we don't want that a number can be used more than once. We don't want the (8,1,1) anymore:

mysql> SELECT * FROM `NUMBERS` `L` 
       JOIN `NUMBERS` `R` 
       JOIN `NUMBERS` `W` 
       WHERE `L`.`NUMBER` + `R`.`NUMBER` + `W`.`NUMBER` = 10 
        AND `L`.`NUMBER` != `R`.`NUMBER` 
        AND `L`.`NUMBER` != `W`.`NUMBER` 
        AND `W`.`NUMBER` != `R`.`NUMBER`;
+--------+--------+--------+
| NUMBER | NUMBER | NUMBER |
+--------+--------+--------+
|      7 |      2 |      1 |
|      6 |      3 |      1 |
|      5 |      4 |      1 |
|      4 |      5 |      1 |
|      3 |      6 |      1 |
|      2 |      7 |      1 |
|      7 |      1 |      2 |
|      5 |      3 |      2 |
|      3 |      5 |      2 |
|      1 |      7 |      2 |
|      6 |      1 |      3 |
|      5 |      2 |      3 |
|      2 |      5 |      3 |
|      1 |      6 |      3 |
|      5 |      1 |      4 |
|      1 |      5 |      4 |
|      4 |      1 |      5 |
|      3 |      2 |      5 |
|      2 |      3 |      5 |
|      1 |      4 |      5 |
|      3 |      1 |      6 |
|      1 |      3 |      6 |
|      2 |      1 |      7 |
|      1 |      2 |      7 |
+--------+--------+--------+

We can also use join conditions instead:

mysql> SELECT * FROM `NUMBERS` `L` 
       JOIN `NUMBERS` `R` 
        ON `L`.`NUMBER` != `R`.`NUMBER` 
       JOIN `NUMBERS` `W` 
        ON `W`.`NUMBER` != `L`.`NUMBER` AND `W`.`NUMBER` != `R`.`NUMBER` 
       WHERE `L`.`NUMBER` + `R`.`NUMBER` + `W`.`NUMBER` = 10;
+--------+--------+--------+
| NUMBER | NUMBER | NUMBER |
+--------+--------+--------+
|      7 |      2 |      1 |
|      6 |      3 |      1 |
|      5 |      4 |      1 |
|      4 |      5 |      1 |
|      3 |      6 |      1 |
|      2 |      7 |      1 |
|      7 |      1 |      2 |
|      5 |      3 |      2 |
|      3 |      5 |      2 |
|      1 |      7 |      2 |
|      6 |      1 |      3 |
|      5 |      2 |      3 |
|      2 |      5 |      3 |
|      1 |      6 |      3 |
|      5 |      1 |      4 |
|      1 |      5 |      4 |
|      4 |      1 |      5 |
|      3 |      2 |      5 |
|      2 |      3 |      5 |
|      1 |      4 |      5 |
|      3 |      1 |      6 |
|      1 |      3 |      6 |
|      2 |      1 |      7 |
|      1 |      2 |      7 |
+--------+--------+--------+