SQL NOT IN not working

sql not exists
sql not in subquery
if not exists in sql
sql not exists join
in vs not in sql
sql in and not in same query
sql not in list of strings
not working in sql

I have two databases, one which holds the inventory, and another which contains a subset of the records of the primary database.

The following SQL statement is not working:

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

The not in does not work. Removing the NOT gives the correct results, i.e. products that are in both databases. However, using the NOT IN is not returning ANY results at all.

What am I doing wrong, any ideas?

Consider using [NOT] EXISTS instead of [NOT] IN with a subquery , Phil Factor explains why you should prefer use of [NOT] EXISTS over [NOT] There are many ways of working out the differences between two data In short, the SQL Server optimizer treats either query in the same way, and  Hi Friends, A very common issue that I see on forums is that SQL Server NOT IN clause not working. Here is a quick repro: Note: These are dummy tables just for a quick demo so please do not try to find the logic between the two tables 🙂

If NOT IN does not work, you may always try to do LEFT JOIN. Then filter by WHERE using one of the values from the joined table, which are NULL. Provided, the value you were joining by does not contain any NULL value.

Typical solutions to avoid using "NOT IN" on SQL Server, It's very common to use the operator NOT IN to retrive rows in table (or SQL statement) that are not in another table or other SQL Statement. This operator sometimes can slow It won't work as expected. INSERT INTO T2 VALUES (​NULL)  AFAIK (and i am not an expert - check my rep ;-) left outer join is a good technique for this.. it will may perform a not in and does not require a sub-select. @onedaywhen kindly points out that this is not always the case e.g. in SQL Server EXISTS() can be more efficient.

You can also use Case clause to tackle such issues

SELECT  stock.IdStock
        ,stock.Descr        
FROM    [Inventory].[dbo].[Stock] stock
WHERE   (Case when stock.IdStock IN
        (SELECT foreignStockId FROM
        [Subset].[dbo].[Products]) then 1 else 0 end) = 0 

this syntax works in SQL Server, Oracle and postgres

SQL: NOT Condition, This SQL tutorial explains how to use the SQL NOT condition with syntax and examples. The SQL NOT condition (sometimes called the NOT Operator) is used to  Example of MySQL NOT IN using two tables . If you want to fetch those rows from the table book_mast which does not contain those pub_id's which are not exist in publisher table, the following sql can be used. SELECT * FROM book_mast WHERE pub_id NOT IN( SELECT pub_id FROM publisher); Sample Output:

Ask TOM "IN (vs) EXISTS and NOT IN (vs) NOT EXISTS", IN and NOT EXISTS will work better than NOT IN (read this is Oracle SQL> select count(*) from emp where empno not in ( select mgr from emp ); COUNT(*) The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. The syntax for the NOT condition in SQL is: Parameters or Arguments. This is the condition to negate. The opposite of the condition be must be met for the record to be included in the

SQL IN Operator, WHERE Country NOT IN ('Germany', 'France', 'UK');. Try it Yourself ». The following SQL statement selects all customers that are from the same countries as the  A recommendation to prefer use of [NOT] EXISTS over [NOT] IN is included as a code analysis rule in SQL Prompt . Which performs better: EXISTS or IN….? There are many ways of working out the differences between two data sets, but two of the most common are to use either the EXISTS or the IN logical operator.

MySQL 8.0 Reference Manual :: 13.2.11.6 Subqueries with , This is actually an unlikely example because a [NOT] EXISTS subquery almost always contains correlations. Here are some more realistic examples: What kind of  The IN operator compares a value with a list of values. It must however be used with. care when we are dealing with nulls. Let’s create a table containing three city names and a null value. whether a city is in the list or not. -- By default ANSI_NULLS is off so null comparisons follows the SQL-92 standard.

Comments
  • Are you sure you have stock.IdStock which are not in foreignStockId at all?
  • Do you really have any items in Inventory that is not in Subset?
  • Explain what the query should return. Currently it returns all the stock items where there is no product in the subset referencing it.
  • This fixed it for me. Select * From x Where x.y not in( Select Distinct ISNULL(c,0))