retrieve Identical records between two sub query

[ilangoraj]

I use below query to retrieve unmatch record

SELECT *
FROM Emp1
WHERE NOT exists
(SELECT *
FROM Emp2
WHERE Emp1.Eid=Eid and
Emp1.Ename=Ename)



but My query display record if there is any execess records in frist table (emp1).
but i need execess records from both the tables

[iburyak]

You can do this :

SELECT *
FROM Emp1
WHERE NOT exists
(SELECT *
FROM Emp2
WHERE Emp1.Eid=Eid and
Emp1.Ename=Ename)
 
Union
 
SELECT *
FROM Emp2
WHERE NOT exists
(SELECT *
FROM Emp1
WHERE Emp2.Eid=Eid and
Emp2.Ename=Ename)

or this:

select a.*, b.*
from Emp1 a
full join Emp2 b on a.Eid = b.Eid and a.Ename = b.Ename
where a.Eid is null or b.Eid is null

Good Luck.