How can i use IFNULL in Where clause

mysql sql ifnull
Manish Kumar picture Manish Kumar · Jan 16, 2015 · Viewed 17.4k times · Source

I want to use IFNULL() in such a way that I can select the record containing NULL or, if a value is present, then select the record matchinga particular value.

My query is:

SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.NumberOfLeave
FROM `CompanyLeave` CL
WHERE(IFNULL(CL.EmploymentType,0)=3);

column EmploymentType can contain either an Integer or NULL.

I want to select the record matching the specified value, or, if none matches, then the record containing NULL.

Answer

Gordon Linoff picture Gordon Linoff · Jan 16, 2015

I am interpreting the question as a prioritization. If a record with 3 exists, choose that. Otherwise, choose the one that is NULL, if it exists.

If so, this might do what you want:

SELECT (@v:=2) AS Row, CL.LeaveTypeId, CL.NumberOfLeave
FROM `CompanyLeave` CL
WHERE CL.EmployementType = 3 or CL.EmployementType IS NULL
ORDER BY (CL.EmployementType = 3) DESC
LIMIT 1;

This will return the row with 3, if present. Otherwise, it will return a row with NULL, if one exists.

Related questions

Using IFNULL to set NULLs to zero

I have a table in which a field contains an integer or NULL. parent_id 2 4 6 NULL NULL 45 2 How would I go about adding an IFNULL statement so that ans_count will be populated with 0 instead of NULL? Here is my …

Read More
MySQL, IFNULL(), COALESCE() on String not replacing

My statement should replace every empty title_column with 'no name', but it doesn't: SELECT COALESCE(main_table.title_column, 'no name') AS title FROM main_table; IFNULL() behaves the same way. What am I doing wrong ?

Read More
IFNULL() Equivalent in PostgreSQL

I am working on a project of migrating from MySQL to PostgreSQL, some function can't works well in PostgreSQL like IFNULL function. Some tutorials say that in PostgreSQL we can use NULLIF to handle it. When I try I get …

Read More