*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 | +--------+--------+--------+

Click here to show comments