September 28, 2016 at 7:01 pm
Hi,
--Create Test Table
CREATE TABLE [dbo].[Null_Table](
[CID] [int] NULL,
[MID] [int] NULL
) ON [PRIMARY]
--Insert Sample Data
insert into Null_Table(CID,MID)
valueS (123,456)
insert into Null_Table(CID,MID)
valueS (123,Null)
insert into Null_Table(CID,MID)
valueS (123,0)
select * from Null_Table--Should be 3 Records.
SELECT * FROM Null_Table
WHERE CID IS NOT NULL
AND MID IS NOT NULL
Note:- Please help me to understand, I should receive 3 records after I run the final query, why I am getting 2 records. Please help me to understand.
Thank You.
September 28, 2016 at 7:13 pm
Proper punctuation is a beautiful thing. Why no semi-colons at the end of your statements?
This worked for me:
--Create Test Table
CREATE TABLE [dbo].[Null_Table](
[CID] [int] NULL,
[MID] [int] NULL
) ON [PRIMARY];
GO -- execute the create table batch.
--Insert Sample Data --- note the semicolons!
insert into Null_Table(CID,MID)
valueS (123,456);
insert into Null_Table(CID,MID)
valueS (123,Null);
insert into Null_Table(CID,MID)
valueS (123,0);
September 28, 2016 at 7:41 pm
I am using 2008 R2 and 2014 and the SQL didn't work for me
SELECT * FROM Null_Table
WHERE CID IS NOT NULL
AND MID IS NOT NULL
Any advise what would be the cause ?
September 28, 2016 at 7:59 pm
rocky_498 (9/28/2016)
...
SELECT * FROM Null_Table
WHERE CID IS NOT NULL
AND MID IS NOT NULL
Note:- Please help me to understand, I should receive 3 records after I run the final query, why I am getting 2 records. Please help me to understand.
Thank You.
Why would you expect to get 3 rows back base on the data and the 2nd query?
AND MID IS NOT NULL is going to filter out the 2nd row with MID = NULL.
September 28, 2016 at 8:24 pm
I am using AND not OR. Correct me If I am wrong.
WHERE CID IS NOT NULL
AND MID IS NOT NULL
This means when both conditions met
if my query is
WHERE (CID IS NOT NULL
OR
MID IS NOT NULL)
In OR case if either one is Null
am I missing something ?
September 28, 2016 at 9:28 pm
rocky_498 (9/28/2016)
I am using AND not OR. Correct me If I am wrong.WHERE CID IS NOT NULL
AND MID IS NOT NULL
This means when both conditions met
if my query is
WHERE (CID IS NOT NULL
OR
MID IS NOT NULL)
In OR case if either one is Null
am I missing something ?
When using AND, both conditions need to be met... The 2nd row fails the 2nd condition... which is why it was filtered out.
If you use the OR only one of the conditions needs to be met... So, in this case CID is not null, preventing the row from being filtered out by the "MID IS NOT NULL".
September 28, 2016 at 9:35 pm
So, what should I do?
Technical I want to exclude those records when BOTH COLUMNS are NULL
Any advise?
September 28, 2016 at 10:29 pm
You can't test your SQL properly, because your table doesn't contain the right records. I added a new record to the table so that both CID and MID are both NULL. I selected them using the below query and it worked fine.
use tempdb;
GO
--Create Test Table
CREATE TABLE [dbo].[Null_Table](
[CID] [int] NULL,
[MID] [int] NULL
) ON [PRIMARY];
GO -- execute the create table batch.
--Insert Sample Data --- note the semicolons!
insert into Null_Table(CID,MID)
valueS (123,456);
insert into Null_Table(CID,MID)
valueS (123,Null);
insert into Null_Table(CID,MID)
valueS (123,0);
INSERT INTO NULL_TABLE(CID,MID) VALUES (Null, Null);
-- return all records where both CID and MID are null
SELECT *
FROM dbo.Null_Table
WHERE CID IS NULL
AND MID IS NULL;
September 29, 2016 at 7:06 am
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
September 30, 2016 at 4:53 pm
Another way to approach this is if you have a rowID column in your table , you can derive one into a temp table from the base table if you don't, then use that to make the query condition into an AND condition as follows
CREATE TABLE #Null_Table(
Rowid int identity,
[CID] [int] NULL,
[MID] [int] NULL
)
--Insert Sample Data
insert into #Null_Table(CID,MID)
valueS (123,456)
GO
insert into #Null_Table(CID,MID)
valueS (123,Null)
GO
insert into #Null_Table(CID,MID)
valueS (123,0)
GO
insert into #Null_Table(CID,MID)
valueS (NULL,NULL)
GO
SELECT *
FROM #Null_Table
WHERErowID not in
(Select rowID
from #null_table
Where cid is null and
mid is null
)
drop table #null_table
I think this appears more readable if you are not used to dealing with the OR condition.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply