IS DISTINCT FROM for NULL-safe comparison in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IS DISTINCT FROM with NULL values

Consider the following SQL query in PostgreSQL:

SELECT 1 IS DISTINCT FROM 1 AS result1,
       1 IS DISTINCT FROM NULL AS result2,
       NULL IS DISTINCT FROM NULL AS result3;

What is the output of this query?

PostgreSQL

SELECT 1 IS DISTINCT FROM 1 AS result1,
       1 IS DISTINCT FROM NULL AS result2,
       NULL IS DISTINCT FROM NULL AS result3;

A result1: false, result2: true, result3: false

B result1: true, result2: true, result3: true

C result1: false, result2: false, result3: true

D result1: true, result2: false, result3: false

💡 Hint

Remember that IS DISTINCT FROM treats NULLs as comparable values.

IS DISTINCT FROM returns false if both values are equal or both are NULL, true otherwise. So 1 IS DISTINCT FROM 1 is false, 1 IS DISTINCT FROM NULL is true, and NULL IS DISTINCT FROM NULL is false.

🧠 Conceptual

intermediate

Purpose of IS DISTINCT FROM in SQL

What is the main advantage of using IS DISTINCT FROM over the standard = or <> operators when comparing columns that may contain NULL values?

A It converts NULL values to zero before comparison.

B It ignores NULL values and only compares non-NULL values.

C It treats NULL values as equal, avoiding unknown results in comparisons.

D It raises an error if NULL values are present.

💡 Hint

Think about how NULL behaves in normal equality comparisons.

Standard equality operators return unknown (NULL) when comparing NULLs, but IS DISTINCT FROM treats NULLs as equal, so it returns false if both sides are NULL.

📝 Syntax

advanced

Identify the valid use of IS DISTINCT FROM

Which of the following SQL WHERE clauses correctly uses IS DISTINCT FROM to filter rows where column col is different from 10, including NULL-safe comparison?

A WHERE col != 10 IS DISTINCT FROM TRUE

B WHERE col IS DISTINCT FROM (10)

C WHERE col IS NOT DISTINCT FROM 10

D WHERE col IS DISTINCT FROM 10

💡 Hint

Check the syntax and meaning of IS DISTINCT FROM.

Option D uses the correct syntax to check if col is different from 10, treating NULLs safely. Option D is invalid syntax. Option D checks for equality, not difference. Option D is syntactically valid but the parentheses are unnecessary and do not change meaning; however, it is valid SQL but option D is the canonical form.

🔧 Debug

advanced

Debugging unexpected results with NULL comparison

A developer writes this query:

SELECT * FROM users WHERE last_login <> NULL;

They expect to get all users who have a non-NULL last_login. However, the query returns zero rows. What is the reason?

A The query syntax is invalid and causes an error.

B The expression 'last_login <> NULL' always evaluates to NULL, so no rows match.

C The database treats NULL as zero, so no rows match.

D The query returns all rows because NULL is ignored.

💡 Hint

Think about how NULL behaves in standard comparisons.

Comparisons with NULL using standard operators return NULL (unknown), which is treated as false in WHERE clauses, so no rows match.

❓ optimization

expert

Optimizing NULL-safe comparisons in large datasets

You have a large table with millions of rows and a nullable column status. You want to find rows where status is different from 'active', including NULL-safe comparison. Which query is likely to perform best in PostgreSQL, assuming an index on status exists?

A SELECT * FROM table WHERE status <> 'active' OR status IS NULL;

B SELECT * FROM table WHERE NOT (status = 'active');

C SELECT * FROM table WHERE COALESCE(status, '') <> 'active';

D SELECT * FROM table WHERE status IS DISTINCT FROM 'active';

💡 Hint

Consider how indexes work with IS DISTINCT FROM and NULLs.

Option A explicitly checks for inequality or NULL, which can use the index efficiently. IS DISTINCT FROM (option A) may not use the index as effectively. Option A fails to include NULLs properly. Option A uses COALESCE which can prevent index usage.