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 ??

Answer 1

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.

Answer 2

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.