Find same record from two tables.

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

Find same record from two tables.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-02 : 02:14:49

Dear All,

I am having two table EMP and EMP_TEMP.

EMP: SELECT A,B,C,D,E FROM EMP
EMP_TEMP: SELECT A,B,C,D,E FROM EMP_TEMP

Now I want following two things,

1.Records that exist in EMP_TEMP but not in EMP table
2.Records that exist in EMP but not in EMP_TEMP table.

Can anybody please tell me simple queries for both ?

Thanks and Regard's
Harish Patil

Thanks and Regard's
Harish Patil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 02:17:48

quote:
1.Records that exist in EMP_TEMP but not in EMP table


SELECT *
FROM   EMP_TEMP t   -- Records that exist in EMP_TEMP
WHERE  NOT EXISTS   -- but not in EMP table
       (
           SELECT * FROM EMP e WHERE e.EMP_ID = t.EMP_ID
       )

KH
[spoiler]Time is always against us[/spoiler]

sureshkk
Starting Member

21 Posts

Posted - 2011-12-02 : 02:43:54

SELECT * FROM EMP_TEMP
EXCEPT
SELECT * FROM EMP

SELECT * FROM EMP
EXCEPT
SELECT * FROM EMP_TEMP

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-02 : 02:47:20

Dear Frind,

Thnks. And for the second I need to do exactly opposite as follows.
Am I right ?

2.Records that exist in EMP but not in EMP_TEMP table.

SELECT *
FROM EMP t -- Records that exist in EMP_TEMP
WHERE NOT EXISTS -- but not in EMP table
(
SELECT * FROM EMP_TEMP e WHERE e.EMP_ID = t.EMP_ID
)

Thanks and Regard's
Harish Patil

Thanks and Regard's
Harish Patil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 03:30:44

yes. Correct

KH
[spoiler]Time is always against us[/spoiler]

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-02 : 04:17:54

Thanks. I can find the difference between both table.

Thanks and Regard's
Harish Patil

Thanks and Regard's
Harish Patil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 04:38:40