0

My objective is to find records that exist in Table 1 but do not exist in Table2. No record has a NULL value. I am using the following code, but it is not giving me the expected results.

Select  
    distinct a.fld  
from  
    tbl1 a  
left outer join 
    tbl2 b 
on  
    a.fld= b.fld
where  
    a.fld<> b.fld

Most likely the issue is with my where clause, but I can't seem to figure out what the issue is. Pls Help.

3
  • When you run the query without the where clause and add b.fld to your SELECT statement, Do you get NULL values? If so, then you can change your WHERE statement to b.fld IS NULL.
    – SQLHound
    May 21, 2015 at 19:50
  • I suggest you find a good tutorial on SQL. Your comments suggest you don't really understand how outer joins work. To have a start: stackoverflow.com/questions/6630887/… May 22, 2015 at 0:16
  • @ypercube - You are right. I have started a job that needed a "little" sql on a case to case basis. Unfortunately, little is subjective. Thanks for the tutorial link. Appreciate that.
    – Drj
    May 22, 2015 at 15:49

4 Answers 4

2

Instead of using a JOIN on your table how about using NOT EXISTS:

SELECT DISTINCT a.fld
FROM tbl1 a
WHERE NOT EXISTS (SELECT 1
                  FROM tbl2 b
                  WHERE a.fld = b.fld);

This will return all rows in tbl1 where the fld value doesn't exist in tbl2

1
  • This also works. Interesting approach. I also noticed that the query is much faster than using joins. Thanks.
    – Drj
    May 21, 2015 at 20:07
2

Assuming "Table 1" is tbl1a, this should give you what you want:

SELECT DISTINCT a.fld
FROM tbl1 a
LEFT JOIN tbl2 b ON a.fld = b.fld
WHERE b.fld IS NULL
3
  • This works, what i don't understand, is why {b.fld IS NULL} is needed because really there is a not a single record in tbl2 which is NULL.
    – Drj
    May 21, 2015 at 20:05
  • @drj A left join will give you single result set with all the records from both the tables. Where the join predicate is not true you will get NULL for the non matching rows: dummies.com/how-to/content/how-to-use-the-sql-outer-join.html
    – Spörri
    May 21, 2015 at 20:20
  • A good way to understand the OUTER JOIN/NULL concept is to remove the WHERE clause and then put a.*, b.* as your SELECT columns. You'll notice that every row where there are matching fld columns, is the resultset of an INNER JOIN. When you use LEFT OUTER JOIN you are asking SQL Server to return ALL rows of one table and information from another table regardless if it exists or not. If it doesn't exist, something must still be returned which is why NULL is returned for an unmatched row.
    – Queue Mann
    May 26, 2015 at 12:26
2

A cleaner way uses EXCEPT:

SELECT a.fld FROM tbl1 a
EXCEPT
SELECT b.fld FROM tbl2 b

Using the left join method:

Select distinct a.fld  
from  tbl1 a  
left join tbl2 b 
    on a.fld= b.fld
where b.fld IS NULL

You're currently looking for records where

a.fld= b.fld is true

AND a.fld<> b.fld is true

which, of course, is impossible.

1
  • It felt wrong yes :) Just didn't know what the right would be and why it would be right. Using EXCEPT clause example, I believe you meant ..SELECT b.fld FROM tbl2 b.
    – Drj
    May 21, 2015 at 20:10
0

Change the where clause to the following:

where b.fld IS NULL

You can think of a left outer join as connecting records from two tables together (a working table), forming a new table with columns from both tables on each record. All records from the A table are represented. Columns from the A table are populated, but where there is not corrresponding B records, the columns will be NULL.

3
  • Theoretically I understand this now. unfortunately there are no NULL records in tbl2 and thats what has been confusing me :)
    – Drj
    May 21, 2015 at 20:08
  • 2
    The NULL will show up in the work table, not the source table.
    – datagod
    May 21, 2015 at 20:16
  • b.fld will be null if the outer join does not find a corresponding row.
    – user1822
    May 21, 2015 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.