losing null values filtering sql query results using where

Question

I have a complex query which joins more than 7 tables. After the joins, I would like to filter the result of my query .

Here is something that I observed.

When I do a where clause

where X.Name != 'xxx'
and  XY.Product != 1

I get the filtered results , but all the null values for the X.Name and XY.Product also disappear from my result. I would like to retain the null values.

I also tried :

and X.Name != 'xxx'
and  XY.Product != 1

I removed the where clause totally and put in an and , but I dont see the filtering at all by this approach.

Is there a way I can filter my results without losing the null values ??

Null has interesting equality properties. Try switching too AND NOT X.Name == 'xxx'. — zebediah49
– zebediah49, Commented Sep 23, 2013 at 16:40
Have you verified that you have all the NULLs after your joins? Common mistake is when doing multiple joins is to do INNER JOIN after OUTER JOIN. This could negate OUTER JOIN effect — PM 77-1
– PM 77-1, Commented Sep 23, 2013 at 16:41
Suspect you have outer joins. Checking for = or != in the where kills the outer. See solution from das. — paparazzo
– paparazzo, Commented Sep 23, 2013 at 16:49

Ian Preston · Accepted Answer · 2013-09-23 16:40:32Z

10

Try something like:

where (X.Name <> 'xxx' or X.Name is null)
  and (XY.Product <> 1 or XY.Product is null)

Since, by definition NULL is an unknown value (bit simplified but OK for this explanation), it will neither equal or not equal a given value - that's why the IS NULL is required here.

answered Sep 23, 2013 at 16:40

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CRAFTY DBA · Accepted Answer · 2013-09-23 17:32:29Z

This quote is taken from 70-461 training kit.

"T-SQL—again, based on standard SQL—implements only one general purpose mark called NULL for any kind of missing value. This leads to three-valued predicate logic."

Therefore, here are the three logic conditions you can have.

1 - Value matches condition
2 - Value does not match condition
3 - Value is missing.

Here is some sample code to play around with.

-- Create sample table
create table #products
( 
  my_id int identity (1, 1),
  my_name varchar(16)
);

-- Load with sample data
insert into #products (my_name) values
(NULL),
('iPad'),
('Windows Surface');

-- Show the data
select * from #products

-- Show just ipads
select * from #products where my_name = 'iPad'

-- Show just surfaces
select * from #products where my_name <> 'iPad'

-- Show ipads & unknowns
select * from #products where my_name = 'iPad' or my_name is null

Here is the output from the last three select statements.

enter image description here

jeff · Accepted Answer · 2014-10-16 20:27:29Z

0

Why not use this: where isnull(X.Name,'') != 'xxx' and isnull(XY.Product,0) != 1

answered Oct 16, 2014 at 20:27

jeff

312 bronze badges

Comments

mohan111 · Accepted Answer · 2015-06-17 01:01:38Z

0

SELECT X.NAME,XY.PRODUCT FROM

(SELECT ID,NAME FROM TABLE_X WHERE NAME!=1)X
OUTER JOIN
(SELECT ID,PRODUCT FROM TABLE_XY WHERE PRODUCT!=1)XY
ON X.ID=XY.ID

edited Jun 17, 2015 at 1:01

mohan111

8,9054 gold badges35 silver badges61 bronze badges

answered Sep 23, 2013 at 17:30

Srinivasa Reddy R

111 bronze badge

Comments

mohan111 · Accepted Answer · 2015-06-17 01:01:47Z

0

where (X.Name != 'xxx' or X.name Is Null)
and  (XY.Product != 1 or XY.Product Is Null)

edited Jun 17, 2015 at 1:01

mohan111

8,9054 gold badges35 silver badges61 bronze badges

answered Sep 23, 2013 at 16:50

Pratik Kaje

7151 gold badge8 silver badges24 bronze badges

Collectives™ on Stack Overflow

losing null values filtering sql query results using where

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related