Sql Server - Not In

SQL Server - NOT IN

It's because of the way NOT IN works.

To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS:

SELECT  *  
FROM Table1 t1 
WHERE NOT EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t1.MAKE = t2.MAKE
    AND   t1.MODEL = t2.MODEL
    AND   t1.[Serial Number] = t2.[serial number]);

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.

An alternative for NOT IN in SQL SERVER

Try this solution:

select s.*, d.*
from B20Dept d
    cross apply (select distinct YEAR(s.DocDate) Y, MONTH(s.DocDate) THANG from B30AccDocSales s) s
    left join (
        select YEAR(s.DocDate) Y, MONTH(s.DocDate) THANG, s.DeptCode
        from B30AccDocSales s
        group by YEAR(s.DocDate), MONTH(s.DocDate), s.DeptCode) m on m.Y = s.Y and m.THANG = s.THANG and m.DeptCode = d.Code
where m.DeptCode is null 
order by s.Y, s.THANG

EDIT:
In the query below, You can find a solution for the problem, that is in You PS:

declare @Year int = 2014
select s.*, d.*
from B20Dept d
    cross apply (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) s (THANG)
    left join (
        select MONTH(s.DocDate) THANG, s.DeptCode
        from B30AccDocSales s
        where YEAR(s.DocDate) = @Year
        group by MONTH(s.DocDate), s.DeptCode) m on m.THANG = s.THANG and m.DeptCode = d.Code
where m.DeptCode is null
union 
select MONTH(s.DocDate) THANG, '', ''
from B30AccDocSales s
where YEAR(s.DocDate) = @Year
group by MONTH(s.DocDate)
having COUNT(distinct s.DeptCode) = (select count(1) from B20Dept)
order by s.THANG

Invalid results from SQL Server NOT IN clause

Don't use not in with a subquery. It doesn't work the way you expect with NULL values. If any value returned by the subquery is NULL, then no rows are returned at all.

Instead, use not exists. This has the semantics that you expect:

select wpb.[ID number]
from [Fact].[REPORT].[WPB_LIST_OF_IDS] wpb
where not exists (select 1
                  from MasterData.Dimension.Customer dc
                  where wpb.[ID number] = dc.IdNumber
                 );

Of course, the left join method also works.

SQL select where not in subquery returns no results

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)

SQL query question: SELECT ... NOT IN

SELECT distinct idCustomer FROM reservations
WHERE DATEPART ( hour, insertDate) < 2
  and idCustomer is not null

Make sure your list parameter does not contain null values.

Here's an explanation:

WHERE field1 NOT IN (1, 2, 3, null)

is the same as:

WHERE NOT (field1 = 1 OR field1 = 2 OR field1 = 3 OR field1 = null)

That last comparision evaluates to null.
That null is OR'd with the rest of the boolean expression, yielding null. (*)
null is negated, yielding null.
null is not true - the where clause only keeps true rows, so all rows are filtered.

(*) Edit: this explanation is pretty good, but I wish to address one thing to stave off future nit-picking. (TRUE OR NULL) would evaluate to TRUE. This is relevant if field1 = 3, for example. That TRUE value would be negated to FALSE and the row would be filtered.

NOT IN statement is slowing down my query

I usually prefer JOINs than INs, you can get the same result but the engine tends be able to optimize it better.

You join your main query (T1) with what was the IN subquery (T2), and you filter that T2.ID is null, ensuring that you haven't found any record matching those conditions.

SELECT distinct T1.ID 
FROM Table T1 
     LEFT JOIN Table T2 on T2.ID = T1.ID AND 
                     T2.IteamNumber in (132,434,675) AND T2.DateCreated < '2019-01-01'
WHERE T1.IteamNumber in (132,434,675) AND Year(T1.DateCreated) = 2019 AND
      T2.ID is null

UPDATE: Here is the proposal updated with your real query. Since your subquery has inner joins, I have created a CTE so you can left join that subquery. The functioning is the same, you left join your main query with the subquery and you return only the rows with no matching records found on the subquery.

with previous as (
  Select x.No_
  from [Line] c    
       inner join [Header] a on a.CollectionNo = c.CollectionNo
       inner join [Customer] x on x.No_ = a.CustomerNo
  where     c.No_ in ('2101','2102','2103','2104','2105')
        and Enrollmentdate < '2014-01-01'
        and (a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate > '2014-12-31'))
)
Select count(distinct b.No_),'2014'
from [Line] c    
     inner join [Header] a on a.CollectionNo = c.CollectionNo
     inner join [Customer] b on b.No_ = a.CustomerNo
     left join previous p on p.No_ = b.No_
where    c.No_ in ('2101','2102','2103','2104','2105')
     and year(Enrollmentdate)= 2014 
     and (a.Resignationdate < '1754-01-01 00:00:00.000' OR a.Resignationdate >= '2014-12-31')
     and p.No_ is null

SQL Query with NOT LIKE IN

You cannot combine like and in. The statement below would do the job though:

Select * from Table1 
where EmpPU NOT Like '%CSE%' 
AND EmpPU NOT Like '%ECE%' 
AND EmpPU NOT Like '%EEE%'

SQL NOT IN function not returning expected result

If you have any NULL values in the REFERENCA column from the FpsPmtOrderRQ table then the NOT IN clause will not work as expected - (the reason why)

A solution is to remove NULL values from the result returned by the subselect.

SELECT COUNT(*)
FROM i1450 j
WHERE i.BROJ NOT IN (SELECT REFERENCA FROM FpsPmtOrderRQ WHERE REFERENCA IS NOT NULL)