How can i use IFNULL in Where clause

Question

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.

Please edit your question with sample data and desired results. — Gordon Linoff, Jan 16 '15 at 13:20

score 5 · Accepted Answer · answered Jan 16 '15 at 13:19

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.

Thanks I used this way in query and I got the result what I want. — Manish Kumar, Jan 16 '15 at 14:46

axiac · Answer 2 · 2015-01-16T13:37:54.023

The expression IFNULL(CL.EmploymentType, 3) basically means: if CL.EmploymentType IS NULL then use 3 instead. The original value of CL.EmploymentType is used if it is not NULL.

If I understand correctly your question, you need to select the rows having NULL or 3 in the column CL.EmploymentType.
The query is:

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

Update:

If only one row must be returned (the one having 3 being preferred over those having NULL) then the rows must be sorted using a criteria that puts the NOT NULL value in front and a LIMIT 1 clause must be added.

MySQL documentation about NULL says:

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

The updated query is:

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

score 1 · Answer 3 · answered Jan 16 '15 at 13:19

You can use IF statement instead of IFNULL()

IF(condition, expres1, expres2)

It means that if condition is satisfied then return expres1 else expres2

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

How can i use IFNULL in Where clause

3 Answers3