Omit rows where multiple columns contain NULL-CodePudding

I'm trying to figure out how to build a query that omits rows where both columns of interest are NULL. If only one or the other is NULL, then I would like to include that data.

In this example table, I would like to have a query that includes everything except row 3.

ID      Col1        Col2
0       10.0        5.0
1       NULL        10.0
2       20.0        15.0
3       NULL        NULL
4       25.0        20.0
5       50.0        NULL

My thought would be to build a query as so:

SELECT *
FROM table
WHERE Col1 & Col2 IS NOT NULL

This however omits any row where either Col1 or Col2 contains a NULL, yielding:

ID      Col1        Col2
0       10.0        5.0
2       20.0        15.0
4       25.0        20.0

I want to include row 1 and 5.

If someone could point me in the right direction to form a WHERE clause that could perform such logic conditional to values across more than one column.

Thanks!

CodePudding user response：

;with cte as (
SELECT 0 AS ID, 10.0 AS Col1, 5.0 AS Col2 
UNION SELECT 1, NULL, 10.0
UNION SELECT 2, 20.0, 15.0 
UNION SELECT 3, NULL, NULL 
UNION SELECT 4, 25.0, 20.0 
UNION SELECT 5, 50.0, NULL 
) 
SELECT * FROM cte  
WHERE Col1 is not null
   or Col2 is not null

CodePudding user response：

You cannot shorted the where clause like that as & is a bitwise and, so this should do what you are asking for

SELECT *
FROM table
WHERE Col1 IS NOT NULL 
   OR Col2 IS NOT NULL