NULL values inside NOT IN clause

by

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don’t get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

More Related Contents:

  1. Not equal != operator on NULL
  2. SQL where clause odd behavior [closed]
  3. How do I escape a single quote in SQL Server?
  4. NOT IN vs NOT EXISTS
  5. Simple way to transpose columns and rows in SQL?
  6. When should I use CROSS APPLY over INNER JOIN?
  7. get a comma delimited string from rows [duplicate]
  8. Find all tables containing column with specified name – MS SQL Server
  9. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  10. How to split a single column values to multiple column values?
  11. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  12. Formatting Numbers by padding with leading zeros in SQL Server
  13. When no ‘Order by’ is specified, what order does a query choose for your record set?
  14. What is a stored procedure?
  15. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  16. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  17. How do I create a foreign key in SQL Server?
  18. How do I group on continuous ranges
  19. join comma delimited data column
  20. Need to list all triggers in SQL Server database with table name and table’s schema
  21. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. Alter user defined type in SQL Server
  24. Best equivalent for IsInteger in SQL Server
  25. Store multiple bit values in a single table column
  26. Syntax of for-loop in SQL Server
  27. SQL Server equivalent to MySQL enum data type?
  28. SQL – Call Stored Procedure for each record
  29. COALESCE Function in TSQL
  30. Convert bigint to datetime

Leave a Comment