FrançaisGet started

NULL in SQL

What NULL means, three-valued logic, IS NULL versus = NULL, NULL in calculations, aggregates, NOT IN and ORDER BY, and the COALESCE and NULLIF functions.

  • 22 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • NULL: what is it?

    Show the answer

    The SQL marker for the absence of a value: the data is unknown or does not exist. It is neither zero nor the empty string.

    A price of 0 is a free price; a missing price is a price nobody knows. Any comparison with this marker, = included, returns "unknown": test it with IS NULL.

  • The ville column of 5 clients contains: Lyon, Paris, NULL, Lyon, NULL. What does this query return?

    ```sql
    SELECT COUNT(*), COUNT(ville) FROM client;
    `

    • 5 and 3
    • 5 and 5
    • 3 and 3
    • 5 and 2
    Show the answer

    5 and 3

    COUNT(*) counts rows, whatever their values. COUNT(ville) counts only the values that are present: Lyon, Paris, Lyon, duplicates included. To count the distinct cities, use COUNT(DISTINCT ville), which returns 2.

  • A WHERE clause keeps only the rows whose condition is …: false and unknown conditions are discarded.

    Show the answer

    A WHERE clause keeps only the rows whose condition is true: false and unknown conditions are discarded.

    That is why WHERE ville <> 'Lyon' stays silent on missing cities: the condition is unknown there, not true.

  • Three-valued logic: what is it?

    Show the answer

    SQL's logic in which a condition is true, false or unknown; a comparison with a missing value is unknown.

    WHERE keeps only the rows whose condition is true: false and unknown are both discarded. Hence ville <> 'Lyon' does not return the rows with a missing ville.

  • This query returns 0 rows although three clients have no phone number. Which fix?

    ```sql
    SELECT * FROM client WHERE telephone = NULL;
    `

    • Write WHERE telephone = '' to look for empty phone numbers
    • Write WHERE telephone IS NULL
    • Write WHERE telephone <> NULL, which is the negation of equality
    • Write WHERE telephone = 0 to look for zero numbers
    Show the answer

    Write WHERE telephone IS NULL

    Comparing to NULL with = or <> always yields unknown, hence no rows. Only IS NULL tests for absence. '' and 0 are values, not missing values.

Study this deck