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

join, sql, sql-server, sql-server-2008

Solution

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:

- Visual Representation of SQL Joins

Problem

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.

Original source