CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
user2185412
user2185412

Reputation: 91

Finding records in table 1 that do not exist in table 2 and returning all fields

I have 2 tables like this

+------+-------+------+------+---------+
| NAME |SURNAME|DEPART| POST |EMPLOYEE#|
+------+-------+------+------+---------+
| JACK | LONDON| 111  |WRITER| 12345678|
|......|.......|......|......|.........|
+------+-------+------+------+---------+

and 

+------+-------+------+------+---------+
| NAME |SURNAME|DEPART| POST |  LOGIN  |
+------+-------+------+------+---------+
| MARK | TWAIN | 222  |WRITER| MTWAIN  |
|......|.......|......|......|.........|
+------+-------+------+------+---------+

I need to find records in table 1 that do not exist in table 2, and return all fields for such records.

I use code like this... 

SELECT name,depart FROM tb1 EXCEPT SELECT name,depart FROM table2

As expected the result is only 2 fields NAME,DEPART.

Upvotes: 5

Views: 12172

Answers (3)

New_Bee
New_Bee

Reputation: 1

select *
from tb1
minus
select *
from tb2

Upvotes: 0

user359040
user359040

Reputation:

Assuming name, surname and depart are sufficient to uniquely identify people, try:

select t1.*
from tb1 t1
where not exists
(select 1
 from table2 t2
 where t1.name = t2.name and 
       t1.surname = t2.surname and 
       t1.depart = t2.depart)

Upvotes: 1

John Woo
John Woo

Reputation: 263893

Basically, you can use LEFT JOIN on this. When a record on Table1 doesn't find any matches on Table2, the result on the values of the columns on Table2 will be NULL, so to filter out non matching values, add a condition which checks for NULL values on table2.

SELECT  a.*
FROM    Table1 a
        LEFT JOIN Table2 b
            ON a.name = b.name AND
                a.depart = b.depart
WHERE   b.Name IS NULL

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 8

Related Questions