Except query Help !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Except query Help !!!

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-07 : 03:04:48

Hi All,
I have a situation where i have to run a except qery.These tables might have columns which are null.I wanted to know what will happen to the except query when it encounters this situation.
Thanks.........

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 03:17:47

declare @table1 table (id int, col varchar(2))
declare @table2 table (id int, col varchar(2))

insert into @table1 (id, col)
values (1, null), (2, 'te')
insert into @table2 (id, col)
values (1, 'ex'), (2, 'te')

select *
from @table1
except
select *
from @table2

Returns:

1 null

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-07 : 07:40:51

To add to what flamblaster showed: In most cases, NULL is not equal to NULL (or anything else), but this is one of those cases where one can interpret that SQL Server is treating NULL as being equal to NULL. Adding one more row to the example, the except query:

declare @table1 table (id int, col varchar(2))
declare @table2 table (id int, col varchar(2))

insert into @table1 (id, col)
values (1, null), (2, 'te'),(3,NULL)
insert into @table2 (id, col)
values (1, 'ex'), (2, 'te'),(3,NULL);


select *
from @table1
except
select *
from @table2

This still returns one row.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-07 : 11:22:49

There are two kinds of equivalent relations in SQL. The first is scalar equality, which treats NULLs as unlike an value or each other. The second is grouping, which treats all NULLs as one group. UNION [ALL], INTERSECT [ALL] and EXCEPT [ALL] use grouping, not scalar equality. The same thing applies to aggregate functions and some other places in SQL. (A INTERSECT [ALL] B) and (A EXCEPT [ALL] B) match each value in set A with a value in B and then either drop or retain the value.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources