How to find different records between two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to find different records between two tables?

Author

Topic

donar
Starting Member

22 Posts

Posted - 2005-08-19 : 08:33:13

Hi, guys

I have a question here, should be easy for you guy.

I have two idential structure tables.
create table table1
(field1 int,
field2 int)
create table table2
(field1 int,
field2 int)

insert into table1
select 2001, 1 union
select 2002, 1

insert into table2
select 2001, 1 union
select 2002, 1 union
select 2005, 1 union
select 2005, 2 union
select 2002, 2

How can I find out the records in table2 while not in table1?

I know it need to use "left join" and then?

Seems in Oracle, it have "minus" operator, but in SQL server, I can not use that.

Please help, thanks!

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 08:46:12

[code]
SELECT *
FROM table2 T2
LEFT OUTER JOIN table1 T1
ON T1.field1 = T2.field1
AND T1.field2 = T2.field2
WHERE T1.field1 IS NULL
[/code]
You could use a NOT EXISTS clause instead

kristen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-19 : 10:02:10

One of my favorite questions to ask newbie DBAs ....

What does the select return. (Try it, if you think NOT IN is good for calculating relational difference).

create table sometable1 (x int)
create table sometable2 (x int)

insert into sometable1
select 1 union select 2 union select 3 union select 4

insert into sometable2
select 1 union select null union select 2

select
x
from
dbo.sometable1
where
x not in (
select
x
from
dbo.sometable2)

Jay White

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-19 : 14:08:54

Jay, it's great to see you here! Maybe it's just me, but I hadn't seen you around much lately. Good to see you haven't given up on us.

That's a neat trick question there. I've never tried it that way, but can see how one might expect it work, even though it doesn't. But, revealing my ignorance here (like I said, this isn't the approach I've ever taken) why does it not work? Because the opposite seems to work: If you eliminate the NOT and just go with IN, it shows the common rows.

---------------------------
EmeraldCityDomains.com

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-19 : 15:11:06

I've been busy. I still am, but I had to post a question the other day, and I can't help but start posting again ... I'm on vacation next week, so I'll drop out again.

One really must understand how the IN predicate works. It's more tricky than you think ...

Let me put it this way.

Let's say I have a shopping list and on it is "Ham", "Bread", "Butter". In my fridge, I know I already have "Ham", "Bread" and some other stuff. I'm getting ready to leave for the store. What do I need to buy? (What is on my list that is NOT IN the fridge?) Without opening the fridge and defining "other stuff", you'll never definitively answer the question What do I need to buy?

x not in ( {1,2} )
becomes
not(x=1 or x=2)
becomes
(x<>1 and x<>2)
if x = 3 then that becomes
(3<>1 and 3<>2)
becomes
(true and true)
becomes
TRUE

so

x not in ( {1,null} )
becomes
not(x=1 or x=null)
becomes
(x<>1 and x<>null)
if x = 3 then that becomes
(3<>1 and 3<>null)
becomes
(true and false) *comparison to null is always false
becomes
false

(I typed this quick, I hope I wrote it out right ...)

Jay White

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-19 : 16:56:57

quote:
(3<>1 and 3<>null)
becomes
(true and false) *comparison to null is always false

That's not quite true. Comparison (other then is null or is not null) to null value is always null.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-19 : 18:33:01

Okay, Jay, it's late, the sun is out (which rarely happens in Seattle) so my brain may not be fully functional... I see your logic and it makes total sense to me, except don't we have the fully defined list (like your first example with x NOT IN ({1,2}) )? Where is NULL coming into the scenario with your integer tables?

NEVERMIND! We're literally inserting NULL. I totally glossed over that.
---------------------------
EmeraldCityDomains.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-19 : 18:40:18

Funny, a coworker just ran into this problem. He's doing a double NOT IN scenario. But he found the work-around that using ISNULL() works:

create table sometable1 (x int)
create table sometable2 (x int)

insert into sometable1
select 1 union select 2 union select 3 union select 4

insert into sometable2
select 1 union select null union select 2

select
x
from
dbo.sometable1
where
x not in (
select
ISNULL(x, 0) -- make sure your replacement value won't match up. Empty string works too.
from
dbo.sometable2)

drop table sometable1
drop table sometable2

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources