NULL Values: Intermediate

COALESCE: NULL won

COALESCE: NULL won't fix itself

Category

SQL

Difficulty

intermediate

Duration

31 minutes

Challenges

0 hands-on challenges

Topics covered: Sentinel values and fake NULLs, NULLs in INNER and LEFT JOINs, COALESCE for defaults, NULLIF for sentinel values, COALESCE and NULLIF patterns

Lesson Sections

1. Sentinel values and fake NULLs (concepts: sqlNullif)

   Sentinel Values Many software systems are forced to produce values even when the data isn't known. This happens for several reasons: When a system must produce a value but doesn't know the real answer, developers choose "placeholder" values. These sentinel values become embedded in the data and persist for years. Common Sentinel Values These placeholders may seem harmless, but they complicate every query that touches the column. Practical Implications Consider why these patterns emerge in actual

2. NULLs in INNER and LEFT JOINs (concepts: sqlNullInJoins)

   Understanding NULL in JOINs JOIN Type Behaviors LEFT JOIN and NULL Advanced JOIN Patterns

3. COALESCE for defaults (concepts: sqlCoalesce)

   Understanding COALESCE Basic Syntax Simple Example A common use case is selecting the best available name for display purposes. Practical Applications A common use case is providing default values for calculations: COALESCE in Aggregations COALESCE with JOINs

4. NULLIF for sentinel values

   Understanding NULLIF Basic Syntax Sentinel Conversion Advanced NULLIF Patterns Cleaning Empty Strings

5. COALESCE and NULLIF patterns

   Combining Functions