MySQL: Using IF in a WHERE clause ~ Tech Blog

I recently needed to use an IF statment in a WHERE clause with MySQL. This isn't the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.

How IF works

If works like this:

IF(<condition>, <value if true>, <value if false>)

So as an example, the first query below would return 1 and the second 0:

SELECT IF( 'a' = 'a', 1, 0 );
SELECT IF( 'a' = 'b', 1, 0 );

Using IF in a WHERE query

The following example shows how to use IF in a WHERE query.

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', 1, 0) = 1

In the above example, if the value from the column "myfield" matches "somevalue" then the IF function will evaluate to 1. This is then compared with the value 1 which returns true or false for this part of the where condition depending whether the IF function returns 1 or 0.

This simple example would be obviously be better represented like this and would be more efficient because it's not using an IF function:

SELECT ...
WHERE ...
AND myfield = 'somevalue'

Our actual code had a nested IF which simplified the original query and looked something like this:

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', IF(otherfield = 12345, 1, 0), 0) = 1

An alternative to the above without using IF functions would look something like this:

SELECT ...
WHERE ...
AND ( (myfield = 'somevalue' AND otherfield = 12345) OR myfield != 'somevalue' )

But should you use IF in a WHERE clause?

Probably not. It's probably not the most efficient way of doing things. Ideally you should have already filtered with other WHERE statements otherwise it will have to do the IF function on every row in the database.

I've posted this mainly as a reference to show how it can be done. I'll leave whether or not it should be done up to you :)

Tech Blog

Tuesday, 11 September 2018