SQL NULL Value – Simplified

In SQL, NULL approximately represents an unknown value.

• An arithmetic operation involving a NULL returns NULL. For example, NULL minus NULL yields NULL, not zero and NULL plus NULL is NULL as well.
• A boolean comparison between two values involving a NULL returns neither true nor false, but unknown in SQL’s three-valued logic. For example, neither NULL equals NULL nor NULL not-equals NULL is true. Testing whether a value is NULL requires an expression such as
  IS NULL

  1

  IS NULL

  or
  IS NOT NULL

  1	IS NOT NULL

  .
• An SQL query selects only values whose
  WHERE

  1

  WHERE

  expression evaluates to true, and groups whose
  HAVING

  1

  HAVING

  clause evaluates to true.
• The aggregate
  COUNT(*)

  1

  COUNT(*)

  counts all NULL and non-NULL cells;
  COUNT

  1

  COUNT

  (attribute) counts all cells whose attribute value is not NULL. Other SQL aggregate functions ignore NULL values in their computation.

So there is a difference between IS NULL and = NULL

‘IS NULL’ check if the values posted or compared with are nulls whereas ‘= NULL’ makes literal comparison with the posted value. So when we compare an unknown value with another unknown value we get the output as unknown but if we check if a particular value is unknown then we can get a desired boolean value as output. for further insights please refer to SQL’s three-valued logic

Post comments if this helps !!!