minte9
LearnRemember   ANTIPATTERNS





Antipattern

Using NULL like an ordinary value. Unlike most programming languages, SQL treats null as a special value. In SLQ null is different from zero, false, or empty string. Programmers are surprise when performing arithmetic on a column that is null. 
 
SELECT hours * 10 FROM bugs; // NULL if hours is NULL

Confusion

Boolean expressions with AND, OR, NOT produce results that people find confusing. Any comparision to NULL returns unknown, not true or false. Even the negation of NULL is still NULL. Using NULL is not the antipattern. The antipattern is using NULL like an ordinary value. 
 
SELECT * FROM bugs WHERE assigned_to <> NULL; // unknown

Solution

Use NULL as unique value. Suppose Stan is 30 years old, while Oliver's age is unknown. Is Stan older than Oliver? You don't know. Older SQL standars define the IS NULL predicate. IS NULL returns true if its single operand is null. In addition, the SQL-99 standard defines IS DISTINCT FROM. Like an ordinary inequlity operator <>, except that it always returns TRUE or FALSE. 
 
SELECT * FROM bugs WHERE assigned_to IS NULL OR assigned_to <> 1; 
    // equivalent to:

SELECT * FROM bugs WHERE assigned_to IS DISTINCT FROM 1;






Questions and answers:
Clink on Option to Answer




1. SQL treats NULL as ...

  • a) special value, unknown
  • b) empty

2. NULL * 10 is ...

  • a) unknown
  • b) zero

3. Which one is correct?

  • a) assigned <> 1
  • b) assigned IS DISTINCT FROM 1


References: