CASE or COALESCE performance in WHERE clause for MySQL

case, mysql, null, performance, sql

Solution

I would avoid both of those approaches. You should be able to accomplish what you are trying to do with a judicious use of `AND`, `OR`, `IS NULL`, and parentheses.

For example, you can rewrite this:

WHERE
    u.id = COALESCE(user_id, u.id) AND
    su.custom_id = COALESCE(student_number, su.custom_id)

Like this:

WHERE
    (user_id IS NULL OR u.id = user_id) AND
    (su.custom_id = student_number)

And you can rewrite this:

WHERE
CASE 
    WHEN user_id IS NOT NULL AND LENGTH(user_id) > 0
    THEN
        u.id = user_id 
    ELSE
        su.custom_id = student_number
    END

As this:

WHERE
(user_id IS NOT NULL AND LENGTH(user_id) > 0 AND u.id = user_id)
OR
(su.custom_id = student_number)

Problem

I'm wondering which is the better performance or best practice when dealing with multiple criteria in the WHERE clause and NULL values. ``` WHERE u.id = COALESCE(user_id, u.id) AND su.custom_id = COALESCE(student_number, su.custom_id) ``` OR ``` WHERE CASE WHEN user_id IS NOT NULL AND LENGTH(user_id) > 0 THEN u.id = user_id ELSE su.custom_id = student_number END ```

Original source