SQL is null and = null [duplicate]

by

In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it’s “not true”, rather than “false”, but the effect is the same).

The reasoning is that a null means “unknown”, so the result of any comparison to a null is also “unknown”. So you’ll get no hit on rows by coding where my_column = null.

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).

Here’s some SQL showing a variety of conditions and and their effect as per above.

create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);

select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);

returns only 1 row (as expected):

TEST    X   Y
x = y   1   1

See this running on SQLFiddle

More Related Contents:

  1. Select rows which are not present in other table
  2. NULL values inside NOT IN clause
  3. Sort by column ASC, but NULL values first?
  4. Why does Oracle 9i treat an empty string as NULL?
  5. Not equal != operator on NULL
  6. Why does NULL = NULL evaluate to false in SQL server
  7. NULL in MySQL (Performance & Storage)
  8. Create unique constraint with null columns
  9. Best way to check for “empty or null value”
  10. SQL QUERY replace NULL value in a row with a value from the previous known value
  11. why is null not equal to null false
  12. Return a value if no record is found
  13. Sorting null values after all others, except special
  14. Sort NULL values to the end of a table
  15. Determine Oracle null == null
  16. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  17. Why doesn’t SQL support “= null” instead of “is null”?
  18. Combine two columns and add into one new column
  19. IN Clause with NULL or IS NULL
  20. SQL not displaying null values on a not equals query?
  21. Check if NULL exists in Postgres array
  22. Check if a parameter is null or empty in a stored procedure
  23. SQL Inner Join On Null Values
  24. Space used by nulls in database
  25. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  26. T-SQL dynamic pivot
  27. Top n records per group sql in access
  28. How to use SQL Order By statement to sort results case insensitive?
  29. Best way to do nested case statement logic in SQL Server
  30. How to count the number of occurrences of a character in an Oracle varchar value?

Leave a Comment