IF... ELSE in WHERE clause MySQL

Question

I'm trying to use a IF...ELSE function in a MySQL WHERE statement without success.

I have this query:

SELECT id 
FROM mytable 
WHERE restrcountry NOT LIKE '%*nl*%' 
  AND (IF languages LIKE '%*nl*%', 1, 0) = 1;

Ok, this is my query with IF statement.

However, how can I use also "ELSE"?

Example, I would like to do something similar:

IF language match nl ---> select id field where language is nl

ELSE IF language NOT match nl ---> select id field where language is en

How can I do this in a MySQL query, please?

Thanks to all!

Answer 1

The syntax for IF is :

 IF(test_expr, then_expr, else_expr)

so you could do something like IF(test1, result1, IF(test2, result2, else_result)) but it would not be very readable, so there's the CASE expression for that purpose.

CASE WHEN  test1 THEN result1
WHEN test2 THEN result2
ELSE else_result END

If you want to condition a select column, you can use the IF in the select fields directly:

SELECT IF(match, nl_column en_column) AS lang 
FROM table

Note that an expression in a where clause is either TRUE or FALSE, so writing

IF(expr, TRUE, FALSE)

is the same as

expr

Answer 2

use CASE instead

CASE
    WHEN languages LIKE '%*nl*%' THEN 1 
    WHEN languages NOT LIKE '%*nl*%' THEN 0
 END as languages