MySQL IF/CASE syntax that calls different SELECT statements

Question

I'm having problems using MySQL's IF or CASE syntax

The procedure goes something like this:

I have a PHP project that calls a MySQL query, let's say PHP passes variable X.

If X is not 0 then it should perform a query something like:

SELECT * FROM table
WHERE id = X

But if X is 0. then it should perform a query to just select all records:

SELECT * FROM table

I don't want to have the condition in PHP which calls a different MySQL query based on the result of the condition in PHP. I want the if / else condition to be performed inside the SQL query based on the variable passed X.

Answer 1

SELECT * FROM table
WHERE $x = 0 or id = $x

Answer 2

I assume $x is derived from your PHP, then:

SELECT * FROM t WHERE IF($x=0, 1, id=$x)

(it's to show general idea, you'll have to handle injections e t.c. by yourself)

Edit

I've found nice code in another answer here. So I was curious - which is faster? Now I'm 'happy' that mine is that as well:

My version:

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.00 sec)

Now, test data:

mysql> select * from test;
+----+-------+
| id | title |
+----+-------+
|  1 | f     |
|  3 | t     |
|  4 | s     |
+----+-------+
3 rows in set (0.02 sec)

And tests:

First: IF comparison

mysql> select @x;
+------+
| @x   |
+------+
| t    |
+------+
1 row in set (0.00 sec)

mysql> select benchmark(1E7, if(@x=0, 1, title=@x)) from test;
+---------------------------------------+
| benchmark(1E7, if(@x=0, 1, title=@x)) |
+---------------------------------------+
|                                     0 |
|                                     0 |
|                                     0 |
+---------------------------------------+
3 rows in set (1.66 sec)

mysql> set @x=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select benchmark(1E7, if(@x=0, 1, title=@x)) from test;
+---------------------------------------+
| benchmark(1E7, if(@x=0, 1, title=@x)) |
+---------------------------------------+
|                                     0 |
|                                     0 |
|                                     0 |
+---------------------------------------+
3 rows in set (1.85 sec)

Second, OR comparison

mysql> select @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select benchmark(1E7, title = @X or @X = 0) from test;
+--------------------------------------+
| benchmark(1E7, title = @X or @X = 0) |
+--------------------------------------+
|                                    0 |
|                                    0 |
|                                    0 |
+--------------------------------------+
3 rows in set, 65535 warnings (17.31 sec)

-ok, that was because of type-casting. Fixing:

mysql> set @x='0';
Query OK, 0 rows affected (0.00 sec)

mysql> select benchmark(1E7, title = @X or @X = '0') from test;
+----------------------------------------+
| benchmark(1E7, title = @X or @X = '0') |
+----------------------------------------+
|                                      0 |
|                                      0 |
|                                      0 |
+----------------------------------------+
3 rows in set (5.78 sec)

And, finally, non-zero:

mysql> set @x='t';
Query OK, 0 rows affected (0.00 sec)

mysql> select benchmark(1E7, title = @X or @X = '0') from test;
+----------------------------------------+
| benchmark(1E7, title = @X or @X = '0') |
+----------------------------------------+
|                                      0 |
|                                      0 |
|                                      0 |
+----------------------------------------+
3 rows in set (4.92 sec)

Conclusion

IF comparison seems to be much more faster in this case, than OR (about 3 times for 1E7 benchmark iterations)