rocky_498 (9/28/2016)
So, what should I do?Technical I want to exclude those records when BOTH COLUMNS are NULL
Any advise?
Just think about what you're asking SQL Server to return to you... If either CID is not null OR MID is not null (you don't care which), you want the row returned... So, that means you need to use the OR logic...
SET NOCOUNT ON;
--Create Test Table
IF OBJECT_ID('tempdb..#Null_Table', 'U') IS NOT NULL
DROP TABLE #Null_Table;
GO
CREATE TABLE #Null_Table (
CID INT NULL,
MID INT NULL
);
GO -- execute the create table batch.
--Insert Sample Data --- note the semicolons!
INSERTINTO #Null_Table (CID,MID) VALUES
(123,456),
(123,NULL),
(123,0),
(NULL,NULL);
--========================
PRINT ('-- no filter');
SELECT * FROM #Null_Table nt
PRINT('-- AND logic');
SELECT
nt.CID,
nt.MID
FROM
#Null_Table nt
WHERE
nt.CID IS NOT NULL
AND nt.MID IS NOT NULL;
PRINT('-- OR logic');
SELECT
nt.CID,
nt.MID
FROM
#Null_Table nt
WHERE
(
nt.CID IS NOT NULL
OR
nt.MID IS NOT NULL
);
Results...
-- no filter
CID MID
----------- -----------
123 456
123 NULL
123 0
NULL NULL
-- AND logic
CID MID
----------- -----------
123 456
123 0
-- OR logic
CID MID
----------- -----------
123 456
123 NULL
123 0