Here is how you can solve this using a single WHERE clause:
WHERE (@myParm = value1 AND MyColumn IS NULL)
OR (@myParm = value2 AND MyColumn IS NOT NULL)
OR (@myParm = value3)
A naïve usage of the CASE statement does not work, by this I mean the following:
SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
WHEN value1 THEN MyColumn IS NULL
WHEN value2 THEN MyColumn IS NOT NULL
WHEN value3 THEN TRUE
END
It is possible to solve this using a case statement, see onedaywhen's answer
Answer from Patrick McDonald on Stack OverflowHere is how you can solve this using a single WHERE clause:
WHERE (@myParm = value1 AND MyColumn IS NULL)
OR (@myParm = value2 AND MyColumn IS NOT NULL)
OR (@myParm = value3)
A naïve usage of the CASE statement does not work, by this I mean the following:
SELECT Field1, Field2 FROM MyTable
WHERE CASE @myParam
WHEN value1 THEN MyColumn IS NULL
WHEN value2 THEN MyColumn IS NOT NULL
WHEN value3 THEN TRUE
END
It is possible to solve this using a case statement, see onedaywhen's answer
You could just do something like this:
SELECT *
FROM foo
WHERE (@param = 0 AND MyColumn IS NULL)
OR (@param = 1 AND MyColumn IS NOT NULL)
OR (@param = 2)
Something like that.
IS NULL vs = NULL in where clause + SQL Server - Stack Overflow
sql server - Include in where clause if the value is not null - Stack Overflow
mysql - WHERE Clause only IF NOT NULL - Stack Overflow
sql server - Using IS NULL in CASE Expression in WHERE Clause - Database Administrators Stack Exchange
Videos
Check out the full reference on Books Online - by default ANSI_NULLS is on meaning you'd need to use the approach you have done. Otherwise, you could switch that setting OFF at the start of the query to switch the behaviour round.
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
...
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.
Here's a simple example to demonstrate the behaviour with regard to comparisons against NULL:
Copy-- This will print TRUE
SET ANSI_NULLS OFF;
IF NULL <> 'A'
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
-- This will print FALSE
SET ANSI_NULLS ON;
IF NULL <> 'A'
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
In general, you have to remember that NULL generally means UNKNOWN. That means if you say CategoryID <> '00000000-0000-0000-0000-000000000000' you have to assume that the query will only return values that it KNOWS will meet your criteria. Since there is a NULL (UNKNOWN) result, it does not actually know if that record meets your criteria and therefore will not be returned in the dataset.
select column1 from Table1
where (@param is null and column2 is null)
or (column2 = @param)
I realize this is an old question, but I had the same one, and came up with another (shorter) answer. Note: this may only work for MS SQL Server, which supports ISNULL(expr,replacement).
SELECT column1 FROM table1
WHERE ISNULL(column2,'') = ISNULL(@param,'')
This also assumes you treat NULL and empty strings the same way.
You probably need something like this:
First example:
SELECT * FROM beerImage WHERE beerBRewID = brewID AND (beerID IS NULL OR beerBrandID = beerID)
Second Example:
SELECT * FROM beerImage WHERE beerBRewID = brewID AND beerID IS NOT NULL AND beerBrandID = beerID
The first example will allow you to show records which have beerID null along with the beers which beerBrandID is equal to beerID (both).
The second one will return exactly beers which correspond to beerBrandID (excluding NULL beerIDs).
How about using with CASE statement in where clause like
WHERE
CASE WHEN beer.id IS NOT NULL
THEN beer.brand_id = 12345
ELSE TRUE
END
I think you are making it harder than it should be.
If @UserRole is 'Analyst' then also SupervisorApprovedBy should be null? Else return everything?
WHERE (@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL )
OR (ISNULL(@UserRole, '') <> 'Analyst')
The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't pass the WHERE test).
You can rewrite with nested CASE expressions:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' THEN
CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
WHEN SupervisorApprovedBy IS NOT NULL THEN 1
END
Or a complicated CASE expression:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
WHEN @UserRole = 'Analyst' THEN 0
WHEN SupervisorApprovedBy IS NOT NULL THEN 1
END
or with a bit more easy to understand AND / OR:
WHERE
@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL
OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL
OR @UserRole IS NULL AND SupervisorApprovedBy IS NOT NULL
Another issue is that SupervisorApprovedBy = SupervisorApprovedBy (and the equivalent SupervisorApprovedBy IS NOT NULL I used above) will not give you "all data". The rows where SupervisorApprovedBy is null will not be returned. If you do want them all, the conditions should be all adjusted:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' THEN
CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
ELSE 1
END
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
WHEN @UserRole = 'Analyst' THEN 0
ELSE 1
END
WHERE
@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL
OR @UserRole <> 'Analyst'
OR @UserRole IS NULL
Hi All,
I'm working in Microsoft Access 2010 and a query i've created is giving me some issues. Basically, I have set up the query to select all records that do not contain the letter 'R' by including the phrase:
SELCECT * FROM WHERE Field Is Not Like '*R*';
However, some records with are null values in Field are not being selected, but not all records. I've revised the where clause to say:
WHERE (Field IS NULL OR Field Is Not Like '*R*');
and that seems to fix the issue.
Can anyone explain why I need that extra statement? I suspect it's some weird issue with the wildcards around the R in my statement, but I would think if the value in Field doesn't have an 'R' in it anywhere, it should select that record.