SQL Query Question: Select ... Not in

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.

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 for NOT IN operator

One approach is to group according to the location and count the number of different attributes. By using a case statement you could match just the relevant attributes:

SELECT   location
FROM     mytable
GROUP BY location
HAVING   COUNT(DISTINCT CASE WHEN attribute IN ('A', 'I') THEN 1 END) = 2 OR
         COUNT(DISTINCT CASE WHEN attribute IN ('A', 'I', 'Z') THEN 1 END) = 0

WHERE and WHERE NOT in SELECT

You can have multiple clauses in your where:

select * 
from [fruits] 
where Colour = 'Red'
  and Name <> 'Apple'

SELECT those not found in IN() list

You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT.

The below uses a table value constructor as a derived table (compatible with SQL Server 2008+)

SELECT CustomerId
FROM   (VALUES(1),
              (79),
              (14),
              (100),
              (123)) V(CustomerId)
EXCEPT
SELECT CustomerId
FROM   Customers 

What is wrong with using 'Not In' in this SQL query?

The problem is because one of your P values is null. Remove this by saying select distinct p from t where p is not null in at least the Not In one of your subqueries

http://sqlfiddle.com/#!6/77fb8/3

hence:

select N, 
case
when P is null then 'Root'
when N in (select distinct P from BST) then 'Inner'
when N not in (select distinct P from BST where p is not null) then 'Leaf'
end as type
from BST

the null P value gets included in the list of distinct values selected, and not in can not determine if a given value of N is equal/not equal to the null coming from the root node of P.

It's somewhat counter intuitive but nothing is ever equal to or not equal to a null, not even null. using = with one side being null results in null, not true and not false

IN can be used to check if a value IS in the list, but not if it's not, if the list includes a null

1 IN (1,2,null) --true
3 IN (1,2,null) --null, not false, null which isn't true
3 NOT IN (1,2,null) --null, not false, null which isn't true

The ELSE form is the way to go here. Or put the disctinct query in as a subquery in the FROM block and do a left join to it

SQL select where not in select statement

This is better written as a correlated NOT EXISTS subquery.

SELECT ID
  FROM PART
 WHERE NOT EXISTS 
    (
        SELECT 1
        FROM USER_DEF_FIELDS
        WHERE PROGRAM_ID = 'VMPRTMNT' 
          AND ID = 'UDF-0000029'
          AND DOCUMENT_ID = PART.ID
    )

SQL Select Query with a clause NOT CONTAINS

Use the operator NOT LIKE to exclude the values that contain the char '%'

SELECT * FROM CommissionContract 
WHERE commission NOT LIKE '%[%]%'

See the demo.

Unexpected results from SELECT FROM WHERE X NOT IN Y

If the subquery returns a null value, the NOT IN is no longer true, and no rows are returned.

Either aviod null values to be returned:

select COUNT(*) from tblAttachment a
where 
a.ContainerID = 1 AND
a.ID NOT IN
(
    SELECT d.AttachmentId from docDocument  d WHERE d.AttachmentId IS NOT NULL
)

Or switch to the "null safe" NOT EXISTS:

select COUNT(*) from tblAttachment a
where 
a.ContainerID = 1 AND
NOT EXISTS
(
    SELECT * from docDocument  d WHERE d.AttachmentId =  a.ID
)

---

Related Topics