Basics
NULL Values and IS NULL
NULL represents the absence of data, distinguishing an unknown value from a zero or an empty string. Understanding how NULL interacts with logical operations is vital for preventing data loss or incorrect query filtering results. You must use specific predicates rather than equality operators to identify or exclude these missing values in your database.
The Meaning of NULL
In database systems, NULL is not a value; it is a placeholder indicating that data is missing, unknown, or inapplicable to a specific row. Beginners often confuse NULL with the number zero or an empty string, but these are fundamentally different concepts. A value of zero is a known number, and an empty string is a defined character sequence of zero length. NULL, by contrast, represents the total absence of information. Because it represents an 'unknown' state, any arithmetic or logical operation performed against it results in NULL, not a true or false outcome. If you attempt to add five to a NULL value, the result is NULL because the system cannot perform mathematics on an unknown entity. This behavior is intentional to ensure that incomplete data does not skew statistical results, which would happen if the engine arbitrarily treated missing values as zero.
-- Demonstrating that NULL is not zero or empty
SELECT
(5 + NULL) AS unknown_math, -- Result is NULL
(5 + 0) AS known_math; -- Result is 5Why Equality Fails with NULL
A common mistake in SQL programming is attempting to find missing values using the standard equality operator, such as 'WHERE column_name = NULL'. This query will consistently return zero results, even if the table contains many NULL entries. The reasoning behind this behavior is the three-valued logic system used by database engines, which recognizes True, False, and Unknown. When you compare a value to NULL, the engine considers the result to be 'Unknown' because the missing value could be anything. Since the WHERE clause only filters for records that evaluate strictly to True, an 'Unknown' result fails the test. Therefore, the equality operator is strictly reserved for comparing defined values against other defined values. To handle the absence of data, the language provides dedicated predicates designed to bypass standard Boolean evaluation and specifically detect the state of being NULL.
-- This will return no rows, even if NULLs exist
SELECT * FROM employees
WHERE phone_number = NULL; -- Incorrect: NULL is not a valueUsing the IS NULL Predicate
To correctly identify missing data, you must utilize the IS NULL predicate. This is a special tool designed to evaluate the presence of the NULL state directly. When the database engine encounters IS NULL in a WHERE clause, it treats the condition as a binary check: is the specific column currently holding an empty placeholder? This operation does not rely on standard equality; it acts as a status check for the field. It is crucial to remember that this predicate is the only reliable way to filter for unknown data in a standard query. If you are building a filter that needs to isolate records where critical information is missing, such as an account registration missing an email address, IS NULL becomes your primary mechanism. It bridges the gap between the internal storage of missing data and your need to retrieve or analyze those specific entries.
-- Correct way to identify missing entries
SELECT * FROM customers
WHERE email_address IS NULL; -- Returns records with no emailNegating NULL Checks with IS NOT NULL
Once you have mastered the concept of checking for missing data, you will often need to retrieve records where data definitely exists. The logical complement to IS NULL is the IS NOT NULL predicate. This operator filters the dataset to exclude any rows where the column is empty, ensuring that your results contain only rows with actual, defined values. The reasoning for using this instead of the inequality operator '!=' is identical to the logic for equality: comparison operators cannot evaluate the 'Unknown' state of NULL. If you use 'column_name <> NULL', the engine returns an 'Unknown' result for the rows that are NULL, causing those records to be silently ignored. By explicitly using IS NOT NULL, you provide the engine with a clear directive to return only rows where the data has been explicitly set to a known, non-NULL value, maintaining integrity in your reports.
-- Retrieve only accounts with valid contact info
SELECT * FROM customers
WHERE email_address IS NOT NULL; -- Excludes missing entriesLogical Impact on Complex Queries
The influence of NULL extends beyond simple filtering; it drastically impacts how complex logical expressions function, particularly when using operators like OR or AND. If you include a condition that evaluates to NULL alongside other filters, the entire result row may be excluded because the expression fails to resolve to True. For instance, if you query for customers where the age is greater than 18 OR the age is NULL, you must be extremely explicit. If the age column is NULL, the condition 'age > 18' becomes Unknown, and the overall row filtering may behave unexpectedly depending on the other conditions present. This is why it is best practice to always explicitly handle NULL cases within your logic, rather than relying on implicit behavior. By intentionally checking for IS NULL, you eliminate ambiguity and ensure your query logic remains robust, predictable, and maintainable across all potential data states.
-- Ensuring NULLs are handled in complex logic
SELECT name, age FROM users
WHERE age > 18 OR age IS NULL; -- Explicitly includes NULLsKey points
- NULL represents an unknown value rather than a zero or an empty string.
- Arithmetic operations involving NULL result in NULL instead of a computed value.
- Standard equality operators like '=' cannot be used to detect NULL values.
- The IS NULL predicate is required to filter for rows that lack defined data.
- The IS NOT NULL predicate is used to retrieve only rows containing defined values.
- Database systems use three-valued logic: True, False, and Unknown.
- Comparisons resulting in 'Unknown' will cause a row to be excluded from a filter.
- Explicitly checking for NULL prevents logic errors in complex multi-condition queries.
Common mistakes
- Mistake: Using = NULL to check for missing values. Why it's wrong: NULL represents an unknown value, and SQL logic dictates that NULL = NULL is unknown, not true. Fix: Always use IS NULL or IS NOT NULL.
- Mistake: Assuming COUNT(*) and COUNT(column_name) return the same result. Why it's wrong: COUNT(*) counts every row including those with NULLs, while COUNT(column_name) ignores rows where that specific column is NULL. Fix: Use COUNT(*) if you need the total row count regardless of NULL values.
- Mistake: Thinking that IS NULL works in a CASE statement's WHEN clause. Why it's wrong: While syntactically possible, developers often accidentally try to compare the result of a calculation to NULL using an equality operator. Fix: Ensure you use the IS operator or the COALESCE function.
- Mistake: Neglecting NULL behavior in arithmetic operations. Why it's wrong: Any arithmetic operation with NULL (e.g., 10 + NULL) results in NULL, which can lead to empty reports. Fix: Use the COALESCE function to provide a default value like 0 for arithmetic.
- Mistake: Expecting NOT IN to handle NULLs intuitively. Why it's wrong: If the list in a NOT IN clause contains even one NULL, the entire query may return no results because the comparison result becomes unknown. Fix: Use NOT EXISTS or filter out NULLs before performing the subquery.
Interview questions
What exactly is a NULL value in SQL, and how does it differ from an empty string or zero?
In SQL, a NULL value represents the complete absence of data or an unknown value within a specific cell. It is crucial to understand that NULL is not equivalent to an empty string, which is a character of length zero, nor is it equivalent to the number zero. While zero and empty strings are actual data values, NULL denotes a 'missing' state. If you try to perform arithmetic on NULL, the result will almost always return NULL because you cannot perform calculations on an unknown quantity.
Why can’t we use the standard equality operator '=' to check for NULL values in a WHERE clause?
You cannot use the equals operator because NULL is not a value; it is a placeholder for a missing value. In SQL logic, a comparison like 'column = NULL' evaluates to UNKNOWN rather than TRUE or FALSE. Because the database engine does not know what the NULL value is, it cannot confirm that it is equal to anything, even another NULL. Instead, you must use the 'IS NULL' operator to explicitly check for the presence of missing data.
How does the 'IS NULL' operator work differently than the 'IS NOT NULL' operator?
The 'IS NULL' operator is a predicate used in a WHERE clause to filter rows where the specified column lacks any data entries. Conversely, the 'IS NOT NULL' operator filters the result set to include only those rows where the column contains an actual, valid value. These operators are essential for data cleaning, as they allow developers to isolate records that are incomplete or verify that critical business fields have been successfully populated with data.
Can you explain how aggregate functions like COUNT(column_name) and COUNT(*) treat NULL values differently?
Aggregate functions handle NULLs in specific ways. When you use COUNT(column_name), SQL intentionally ignores any rows where that specific column contains a NULL value, effectively counting only the non-null entries. However, if you use COUNT(*), the query counts the total number of rows in the table regardless of whether the columns contain NULL values or not. This distinction is vital for accurate reporting, especially when calculating average values or row totals.
Compare the behavior of the COALESCE function versus the ISNULL function when handling NULL values in queries.
Both functions serve the purpose of replacing a NULL value with a default fallback value, but they have different levels of compatibility. The COALESCE function is part of the SQL standard, meaning it is supported across almost every major relational database system, and it can accept multiple arguments, returning the first non-null value it encounters. In contrast, ISNULL is proprietary to certain database systems like SQL Server and only accepts two arguments, making COALESCE the more versatile and portable choice for cross-platform SQL development.
How do NULL values behave when they appear in a column used for sorting with ORDER BY?
When sorting data with an ORDER BY clause, NULL values are treated as distinct entities that fall outside the standard ascending or descending order. In most relational databases, NULLs are treated as the lowest possible values, meaning they appear at the top of an ascending sort. If you need to control this, you must use the 'NULLS FIRST' or 'NULLS LAST' syntax to explicitly define whether the gaps in your data appear at the beginning or the end of your retrieved result set.
Check yourself
1. What is the result of the expression '5 + NULL' in SQL?
- A.5
- B.0
- C.NULL
- D.Error
Show answer
C. NULL
In SQL, any arithmetic operation involving NULL results in NULL because the unknown value makes the entire result unknown. Option 0 and 5 are incorrect because they assume NULL is treated as zero, which it is not in standard math. Error is incorrect as it is a valid SQL expression.
2. If you want to count the number of rows where the 'email' column is not empty, which query is most accurate?
- A.SELECT COUNT(email) FROM users
- B.SELECT COUNT(*) FROM users WHERE email IS NOT NULL
- C.SELECT COUNT(email) FROM users WHERE email != NULL
- D.SELECT COUNT(*) FROM users
Show answer
B. SELECT COUNT(*) FROM users WHERE email IS NOT NULL
Option 1 counts rows but might be ambiguous; Option 2 explicitly filters NULLs and counts the rows. Option 3 is wrong because comparison with NULL using != is invalid. Option 4 counts every row including those with NULL, failing the requirement to exclude them.
3. Why does a subquery using 'WHERE id NOT IN (SELECT parent_id FROM items)' potentially return zero rows if 'parent_id' contains a NULL?
- A.SQL ignores the NULL automatically
- B.The expression evaluates to unknown, which is treated as false
- C.The subquery creates a syntax error
- D.The database engine optimizes out the NULL values
Show answer
B. The expression evaluates to unknown, which is treated as false
When comparing against a set containing NULL, the NOT IN logic becomes unknown for those rows, and since unknown is not true, the records are excluded. Option 1 and 4 are wrong because SQL treats NULL explicitly. Option 3 is wrong because the syntax is perfectly valid.
4. Which function is the standard way to replace a NULL value with a specific default during a SELECT query?
- A.IFNULL
- B.ISNULL
- C.COALESCE
- D.NVL
Show answer
C. COALESCE
COALESCE is the standard ANSI SQL function that takes multiple arguments and returns the first non-NULL value. IFNULL, ISNULL, and NVL are vendor-specific functions (MySQL, SQL Server, and Oracle, respectively) and are not part of the standard SQL specification.
5. What is the logical result of the condition 'NULL IS NULL'?
- A.TRUE
- B.FALSE
- C.NULL
- D.Unknown
Show answer
A. TRUE
The 'IS NULL' operator is a specific predicate designed to test for the presence of NULL; it returns TRUE if the value is NULL and FALSE otherwise. Option 1 is correct. Options 2, 3, and 4 are incorrect because they confuse the 'IS' operator with equality comparison logic.