SQL - records not in another table

Question

In doing what I thought was a fairly routine task of finding records in one table that do not exist in another table, I started out like the following. Note: the common value is of type float in one table (thus the cast) and varchar(20) in the other. MEMID=float, DMID=varchar.

select cast((convert(int, MEMID)) as varchar(20))
from tempProv111315
where cast((convert(int, MEMID)) as varchar(20)) NOT IN (
     SELECT DMID
     FROM tempMemberMaster121015
)

The above returned no records (even though i knew a few existed). Therefore I tried the following (which worked).

select cast((convert(int, pv.MEMID)) as varchar(20))
from tempProv111315 pv
left outer join  tempMemberMaster121015 mm on
   cast((convert(int, pv.MEMID)) as varchar(20)) = mm.dmid
where mm.dmid is null

Although likely a simple reason, for the life of me I cannot see why the first way didn't work. I'm not a dba but have used both ways often in different circumstances so I really want to understand what I'm missing. Can anyone explain the different outcome as they seem logically equal to me.

ps - i'm familiar with several ways to cast the float. This is my typical method. I don't believe it is the issue... but if so, why does it work okay on the second statement?

Thanks!

Possibly useful reading: [NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server](http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/) — AHiggins, Dec 11 '15 at 17:04

score 4 · Accepted Answer · edited May 23 '17 at 10:27

4

Its a known behavior, your sub-query would have returned NULL which makes the not in to fail. More info can be found here

NOT IN
(
     SELECT DMID -- check whether this returns any NULL value
     FROM tempMemberMaster121015
)

I will suggest you to use NOT EXIST instead of Left Outer Join

edited May 23 '17 at 10:27

Community

1
1

answered Dec 11 '15 at 17:04

Pரதீப்

91,748
19
131
172

Correct, there is a single null in the return. Combined with the 'useful reading' AHiggins provided above in the comment, i now get it. Your suggestion to use 'not exists' is appropriate and what I intend to do. Thanks for your time. – puddleglum Dec 11 '15 at 17:25

score 1 · Answer 2 · edited Dec 11 '15 at 21:49

You can use an EXCEPT query in SQL Server to return distinct rows from the results of one query that are not in the results of a second query.

The syntax is:

{ <query_specification> | ( <query_expression> ) } 
EXCEPT 
{ <query_specification> | ( <query_expression> ) }

An example using the tables/queries in your question:

SELECT CAST((CONVERT(INT, MEMID)) AS VARCHAR(20)) from tempProv111315  
EXCEPT
SELECT DMID FROM tempMemberMaster121015

Note: for other database systems: In Oracle the keyword is MINUS instead of EXCEPT. In MySQL there is no EXCEPT operator and I don't believe PostgreSQL does either. However Gokhan Atil has a post describing how to implement the behavior of EXCEPT, which incidentally is the same as the LEFT OUTER JOIN query that you found success with in your question.

Very nice! I just tried this and it works beautifully. MM93 fully answered the question but this may be the way I go unless I find a downside. thanks for the great suggestion. — puddleglum, Dec 11 '15 at 17:28

SQL - records not in another table

2 Answers2