Why doesn’t SQL support “= null” instead of “is null”?

by

The reason why it’s off by default is that null is really not equal to null in a business sense. For example, if you were joining orders and customers:

select * from orders o join customers c on c.name = o.customer_name

It wouldn’t make a lot of sense to match orders with an unknown customer with customers with an unknown name.

Most databases allow you to customize this behaviour. For example, in SQL Server:

set ansi_nulls on
if null = null  
    print 'this will not print' 
set ansi_nulls off
if null = null  
    print 'this should print'

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. SQL is null and = null [duplicate]
  7. Why does NULL = NULL evaluate to false in SQL server
  8. NULL in MySQL (Performance & Storage)
  9. Create unique constraint with null columns
  10. Best way to check for “empty or null value”
  11. SQL QUERY replace NULL value in a row with a value from the previous known value
  12. why is null not equal to null false
  13. Return a value if no record is found
  14. Sorting null values after all others, except special
  15. Sort NULL values to the end of a table
  16. Determine Oracle null == null
  17. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  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. What are the options for storing hierarchical data in a relational database?
  26. Pivoting data in MS Access
  27. Combining the results of two SQL queries as separate columns
  28. How do I delete a fixed number of rows with sorting in PostgreSQL?
  29. Alter user defined type in SQL Server
  30. Deferrable Constraints in SQL Server

Leave a Comment