I was thinking recently that it would funny to implement FizzBuzz using only a single MySQL statement. FizzBuzz is a simple test that a programmer might run into at a job interview. They may be asked to implement FizzBuzz in a specific language or with pseudo-code, depending on the interview. For whatever reason FizzBuzz tends to be a polarizing subject with some programmers finding it insulting, and others finding it amusing.
Programmers unfamiliar with the modulo operator will usually try to solve FizzBuzz using a division operator and then test whether the result is a whole number. A surprising number of programmers will simply give up. What makes FizzBuzz interesting to me is that it’s a quick problem with multiple solutions. The particular solution a programmer chooses can help you assess their personal style of problem solving.
The logic part of a FizzBuzz solution in MySQL is almost exactly the same as a procedural language. The challenge with a declarative language like SQL is that you can’t easily loop and generate 100 numbers. Of course you can design a stored procedure which creates a table with 100 rows, but what fun would that be? A greater challenge is to accomplish the Fizz Buzz output with one single MySQL statement and leave no trace of data behind. In this case, a trick is using the UNION statement in combination with JOIN to generate the correct amount of fake data. MySQL allows you to select a value without specifying a table name, for example “select 1” will return a single row. The UNION effectively combines the single rows using addition and the JOIN multiplies them all together. I chose to create three fake tables with 5 x 5 x 4 rows, which equals 100. The same thing could be done with two fake tables of 10 x 10, or one fake table with 100 rows.
After playing around a bit and discovering the union trick online this is what I came up with:
-- FizzBuzz in MySQL SELECT IF(counter % 3 = 0, IF(counter % 5 = 0,'Fizz Buzz','Fizz'), IF(counter % 5 = 0,'Buzz',counter) ) AS RESULT FROM ( -- union trick to select 100 fake rows (1 * 5 * 5 * 4 = 100) SELECT @i := @i + 1 AS counter FROM (SELECT @i := 0) AS dummy_1, -- (resets counter w/o affecting row count) (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) dummy_5_1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) dummy_5_2, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) dummy_4 ) AS numbers
Do you have an interesting way to run FizzBuzz? Strong negative or positive feelings about FizzBuzz? Feel free to leave me a comment below.