SQL Not in Not Working

SQL NOT IN not working

SELECT foreignStockId
FROM   [Subset].[dbo].[Products]  

Probably returns a NULL.

A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. You can explicitly exclude them using IS NOT NULL as below.

SELECT stock.IdStock,
       stock.Descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  stock.IdStock NOT IN (SELECT foreignStockId
                             FROM   [Subset].[dbo].[Products]
                             WHERE  foreignStockId IS NOT NULL) 

Or rewrite using NOT EXISTS instead.

SELECT stock.idstock,
       stock.descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  NOT EXISTS (SELECT *
                   FROM   [Subset].[dbo].[Products] p
                   WHERE  p.foreignstockid = stock.idstock) 

As well as having the semantics that you want the execution plan for NOT EXISTS is often simpler as looked at here.

The reason for the difference in behaviour is down to the three valued logic used in SQL. Predicates can evaluate to True, False, or Unknown.

A WHERE clause must evaluate to True in order for the row to be returned but this is not possible with NOT IN when NULL is present as explained below.

'A' NOT IN ('X','Y',NULL) is equivalent to 'A' <> 'X' AND 'A' <> 'Y' AND 'A' <> NULL)

• 'A' <> 'X' = True
• 'A' <> 'Y' = True
• 'A' <> NULL = Unknown

True AND True AND Unknown evaluates to Unknown per the truth tables for three valued logic.

The following links have some additional discussion about performance of the various options.

• Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• Left outer join vs NOT EXISTS
• NOT EXISTS vs NOT IN

not in operator in sql server not working

This is because of how sql treats IN and NOT IN query.

NOT IN evaluates to != clause for every element in the list and your list (column p) contains NULL value. Hence, value != NULL evaluates to UNKNOWN.

Duplicate: NOT IN clause and NULL values

Try this:

select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner'
 when p is not null and (n not in (select p from BST where p IS NOT null)) then 'Leaf'
end
from BST order by n

This should give the expected result.

T-SQL not in (select not working (as expected)

NOT IN does not behave as expected when the in-list contains NULL values.

In fact, if any values are NULL, then no rows are returned at all. Remember: In SQL, NULL means "indeterminate" value, not "missing value". So, if the list contains any NULL value then it might be equal to a comparison value.

So, customerid must be NULL in the orders table.

For this reason, I strongly recommend that you always use NOT EXISTS with a subquery rather than NOT IN.

Why SQL NOT IN() operator not working?

Logically You have an asimmetric use of IN operator

the two part of the in clause must contain the same number of component

You should use

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE u.id NOT IN( 
            SELECT  id  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE (u.id1, u.id2) NOT IN( 
            SELECT  user_two,user_one  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or the result is impredictable

SQL IS NOT NULL not working

As written:

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 AND result <> NULL
GROUP BY code
HAVING Count(Student) > 1;

This query should return no rows. Why? result <> NULL returns NULL as a boolean value (to a close approximation all comparisons to NULL return NULL). AND NULL evaluates NULL -- and NULL is not true. All rows are filtered out.

The NULL comparison is actually superfluous. The result < 50 will also filter out NULL values.

Looking at the rest of the query, you have another issue. The GROUP BY is on code. It should really be on SubjectCode -- the result of the CONCAT(). In fact, when concatenating different columns, I would recommend using a separator, say CONCAT_WS(':', area, yearlevel, code). Of course a separator may not be desirable for this particular situation.

SQL: NOT IN (SUBQUERY) is not working as expected

The normal reason for problems with not in is the presence of NULL values. NOT IN always fails when the list has NULL because NULL comparisons cannot be true.

You can fix this by filtering them outer:

SELECT COUNT(DISTINCT WIDGET_ID)
FROM A
WHERE WIDGET_ID NOT IN (SELECT WIDGET_ID FROM B WHERE WIDGET_ID IS NOT NULL);

I prefer to use NOT EXISTS, because it has the semantics that you expect:

SELECT COUNT(DISTINCT WIDGET_ID)
FROM A
WHERE NOT EXISTS (SELECT WIDGET_ID FROM B WHERE B.WIDGET_ID = A.WIDGET_ID);

---

Related Topics