SQL select where not in subquery returns no results

Question

Disclaimer  I have figured out the problem  I think   but I wanted to add this issue to Stack Overflow since I couldn t  easily  find it anywhere   Also  someone might have a better answer than I do   I have a database where one table  Common  is referenced by several other tables   I wanted to see what records in the Common table were orphaned  i e   had no references from any of the other tables    I ran this query   select   from Common where common id not in  select common id from Table1  and common id not in  select common id from Table2    I know that there are orphaned records  but no records were returned   Why not    This is SQL Server  if it matters

User · Answer

select   from Common c where not exists  select t1 commonid from table1 t1 where t1 commonid   c commonid  and not exists  select t2 commonid from table2 t2 where t2 commonid   c commonid

User · Answer

Let s suppose these values for common id   Common - 1 Table1 - 2 Table2 - 3  null   We want the row in Common to return  because it doesn t exist in any of the other tables   However  the null throws in a monkey wrench   With those values  the query is equivalent to   select   from Common where 1 not in  2  and 1 not in  3  null    That is equivalent to   select   from Common where not  1 2  and not  1 3 or 1 null    This is where the problem starts   When comparing with a null  the answer is unknown   So the query reduces to   select   from Common where not  false  and not  false or unkown    false or unknown is unknown   select   from Common where true and not  unknown    true and not unkown is also unkown   select   from Common where unknown   The where condition does not return records where the result is unkown  so we get no records back   One way to deal with this is to use the exists operator rather than in   Exists never returns unkown because it operates on rows rather than columns    A row either exists or it doesn t  none of this null ambiguity at the row level    select   from Common where not exists  select common id from Table1 where common id   Common common id  and not exists  select common id from Table2 where common id   Common common id

User · Answer

I had an example where I was looking up and because one table held the value as a double  the other as a string  they would not match  or not match without a cast   But only NOT IN  As SELECT     IN     worked  Weird  but thought I would share in case anyone else encounters this simple fix

User · Answer

Just off the top of my head     select c commonID  t1 commonID  t2 commonID from Common c      left outer join Table1 t1 on t1 commonID   c commonID      left outer join Table2 t2 on t2 commonID   c commonID where t1 commonID is null       and t2 commonID is null   I ran a few tests and here were my results w r t   patmortech s answer and  rexem s comments   If either Table1 or Table2 is not indexed on commonID  you get a table scan but  patmortech s query is still twice as fast  for a 100K row master table    If neither are indexed on commonID  you get two table scans and the difference is negligible   If both are indexed on commonID  the  not exists  query runs in 1 3 the time

User · Answer

select     select COUNT ID   from ProductMaster where ProductMaster CatID   CategoryMaster ID  as coun  from CategoryMaster

User · Answer

this worked for me     select   from Common where common id not in  select ISNULL common id  dummy-data   from Table1  and common id not in  select ISNULL common id  dummy-data   from Table2

User · Answer

Please follow the below example to understand the above topic   Also you can visit the following link to know Anti join  select department name department id from hr departments dep where not exists       select 1 from hr employees emp     where emp department id dep department id       order by dep department name    DEPARTMENT NAME DEPARTMENT ID Benefits    160 Construction    180 Contracting 190           But if we use NOT IN in that case we do not get any data   select Department name department id from hr departments dep  where department id not in  select department id from hr employees         no data found   This is happening as  select department id from hr employees  is returning a null value and the entire query is evaluated as false  We can see it if we change the SQL slightly like below and handle null values with NVL function   select Department name department id from hr departments dep  where department id not in  select NVL department id 0  from hr employees     Now we are getting data   DEPARTMENT NAME DEPARTMENT ID Treasury    120 Corporate Tax   130 Control And Credit  140 Shareholder Services    150 Benefits    160        Again we are getting data as we have handled the null value with NVL function

User · Answer

Update   These articles in my blog describe the differences between the methods in more detail    NOT IN vs  NOT EXISTS vs  LEFT JOIN   IS NULL  SQL Server NOT IN vs  NOT EXISTS vs  LEFT JOIN   IS NULL  PostgreSQL NOT IN vs  NOT EXISTS vs  LEFT JOIN   IS NULL  Oracle NOT IN vs  NOT EXISTS vs  LEFT JOIN   IS NULL  MySQL     There are three ways to do such a query    LEFT JOIN   IS NULL   SELECT    FROM    common LEFT JOIN         table1 t1 ON      t1 common id   common common id WHERE   t1 common id IS NULL  NOT EXISTS   SELECT    FROM    common WHERE   NOT EXISTS                   SELECT  NULL         FROM    table1 t1         WHERE   t1 common id   common common id            NOT IN   SELECT    FROM    common WHERE   common id NOT IN                   SELECT  common id         FROM    table1 t1              When table1 common id is not nullable  all these queries are semantically the same   When it is nullable  NOT IN is different  since IN  and  therefore  NOT IN  return NULL when a value does not match anything in a list containing a NULL   This may be confusing but may become more obvious if we recall the alternate syntax for this   common id   ANY   SELECT  common id FROM    table1 t1     The result of this condition is a boolean product of all comparisons within the list  Of course  a single NULL value yields the NULL result which renders the whole result NULL too   We never cannot say definitely that common id is not equal to anything from this list  since at least one of the values is NULL   Suppose we have these data   common  -- 1 3  table1  -- NULL 1 2   LEFT JOIN   IS NULL and NOT EXISTS will return 3  NOT IN will return nothing  since it will always evaluate to either FALSE or NULL    In MySQL  in case on non-nullable column  LEFT JOIN   IS NULL and NOT IN are a little bit  several percent  more efficient than NOT EXISTS  If the column is nullable  NOT EXISTS is the most efficient  again  not much    In Oracle  all three queries yield same plans  an ANTI JOIN    In SQL Server  NOT IN   NOT EXISTS are more efficient  since LEFT JOIN   IS NULL cannot be optimized to an ANTI JOIN by its optimizer   In PostgreSQL  LEFT JOIN   IS NULL and NOT EXISTS are more efficient than NOT IN  sine they are optimized to an Anti Join  while NOT IN uses hashed subplan  or even a plain subplan if the subquery is too large to hash

User · Answer

If you want the world to be a two-valued boolean place  you must prevent the null  third value  case yourself   Don t write IN clauses that allow nulls in the list side   Filter them out   common id not in     select common id from Table1   where common id is not null

User · Answer

SELECT T common id   FROM Common T        LEFT JOIN Table1 T1 ON T common id   T1 common id        LEFT JOIN Table2 T2 ON T common id   T2 common id  WHERE T1 common id IS NULL    AND T2 common id IS NULL

User · Answer

Table1 or Table2 has some null values for common id   Use this query instead   select   from Common where common id not in  select common id from Table1 where common id is not null  and common id not in  select common id from Table2 where common id is not null

[sql] SQL "select where not in subquery" returns no results

Examples related to sql

Examples related to sql-server

Examples related to tsql