difference netween 2 tables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

difference netween 2 tables

Author

Topic

priyaram
Yak Posting Veteran

82 Posts

Posted - 2007-02-09 : 09:59:43

i am having table A and table B.

TableA = 800 records

TableB = 805 records

Both are having key relationship.I like to know the what are the 5 extra records in the TableB.

How can i get the records.??

Select * from TableA a, TableB b
where a.no not like b.no

no it's not retriving the differnce 5 records.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 10:03:05

[code]
select *
from TableA a left join TableB b
on a.no = b.no
where b.no is null
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 10:06:52

or using the newer EXCEPT operator:

Select * from TableB
EXCEPT
Select * from TableA

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-09 : 10:16:34

Another way is to use the NOT IN

SELECT B.*
FROM TableB B
WHERE B.No NOT IN (SELECT No FROM TableA)

Or use NOT EXISTS

SELECT B.*
FROM TableB
WHERE NOT EXISTS (SELECT 'X' FROM TableA A WHERE A.No = B.No)

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources