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.

Practice

1. What does the IS DISTINCT FROM operator do in PostgreSQL when comparing two values?

easy

A. It returns true if the values are different or one is NULL and the other is not.

B. It returns true only if both values are NULL.

C. It returns false if the values are different.

D. It treats NULL as equal to any value.

Solution

1. Step 1: Understand the behavior of IS DISTINCT FROM

   This operator treats NULL as a real value and compares two values safely even if one or both are NULL.

2. Step 2: Analyze the comparison results

   It returns true if the values differ or if one is NULL and the other is not, otherwise false.

3. Final Answer:

   It returns true if the values are different or one is NULL and the other is not. -> Option A

4. Quick Check:

   IS DISTINCT FROM returns true for different or NULL vs non-NULL [OK]

Hint: Think: NULL counts as a real value in IS DISTINCT FROM [OK]

Common Mistakes:

• Assuming NULL equals NULL
• Confusing IS DISTINCT FROM with = operator
• Thinking it returns true only when both are NULL

2. Which of the following is the correct syntax to check if column a is distinct from column b in PostgreSQL?

easy

A. a <> b OR a IS NULL OR b IS NULL

B. a != b

C. a IS DISTINCT FROM b

D. a IS NOT DISTINCT FROM b

Solution

1. Step 1: Recall the correct syntax for NULL-safe comparison

   The operator IS DISTINCT FROM is used as a IS DISTINCT FROM b to compare two values safely including NULLs.

2. Step 2: Evaluate other options

   a != b uses standard inequality which fails with NULLs. a <> b OR a IS NULL OR b IS NULL is a complex expression but not the standard syntax. a IS NOT DISTINCT FROM b checks for NOT DISTINCT, which is the opposite.

3. Final Answer:

   a IS DISTINCT FROM b -> Option C

4. Quick Check:

   Use IS DISTINCT FROM for NULL-safe inequality [OK]

Hint: Use IS DISTINCT FROM for NULL-safe comparisons [OK]

Common Mistakes:

• Using != which fails with NULLs
• Writing complex OR conditions instead of operator
• Confusing IS DISTINCT FROM with IS NOT DISTINCT FROM

3. Given the table items with columns id and value, what will this query return?

SELECT id FROM items WHERE value IS DISTINCT FROM NULL;

Assuming items has rows:
(1, NULL), (2, 10), (3, NULL), (4, 20)

medium

A. [1, 2, 3, 4]

B. [2, 4]

C. [1, 3]

D. [] (empty list)

Solution

1. Step 1: Understand the condition value IS DISTINCT FROM NULL

   This condition returns true if value is not NULL, because NULL is treated as a real value.

2. Step 2: Apply condition to each row

   Rows with value 10 and 20 are distinct from NULL, so ids 2 and 4 match.

3. Final Answer:

   [2, 4] -> Option B

4. Quick Check:

   IS DISTINCT FROM NULL filters non-NULL values [OK]

Hint: IS DISTINCT FROM NULL means NOT NULL [OK]

Common Mistakes:

• Thinking it returns rows with NULL values
• Confusing IS DISTINCT FROM with IS NULL
• Assuming it returns all rows

4. You wrote this query but it returns no rows:

SELECT * FROM users WHERE email IS DISTINCT FROM NULL;

What is the likely mistake?

medium

A. The email column has only NULL values

B. Using IS DISTINCT FROM NULL instead of IS NOT NULL

C. IS DISTINCT FROM cannot be used with NULL

D. The table users is empty

Solution

1. Step 1: Understand what email IS DISTINCT FROM NULL means

   This condition returns true for all rows where email is NOT NULL.

2. Step 2: Analyze why no rows are returned

   If no rows are returned, it means no rows satisfy the condition, so all email values are NULL or the table is empty.

3. Step 3: Eliminate other options

   Using IS DISTINCT FROM NULL instead of IS NOT NULL is incorrect because IS DISTINCT FROM NULL is valid. The table users is empty is possible but less likely. IS DISTINCT FROM cannot be used with NULL is false because the operator supports NULL.

4. Final Answer:

   The email column has only NULL values -> Option A

5. Quick Check:

   No rows means no non-NULL emails [OK]

Hint: No rows means condition never true; check NULL presence [OK]

Common Mistakes:

• Thinking IS DISTINCT FROM NULL is invalid syntax
• Assuming table is empty without checking
• Confusing IS DISTINCT FROM NULL with IS NULL

5. You want to find all pairs of rows in table records where col1 and col2 differ, treating NULLs as real values. Which query correctly finds these pairs?

hard

A. SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 IS NOT DISTINCT FROM r2.col1 AND r1.col2 IS NOT DISTINCT FROM r2.col2;

B. SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 <> r2.col1 OR r1.col2 <> r2.col2;

C. SELECT * FROM records r1 JOIN records r2 ON r1.id = r2.id WHERE r1.col1 IS DISTINCT FROM r2.col1 AND r1.col2 IS DISTINCT FROM r2.col2;

D. SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 IS DISTINCT FROM r2.col1 OR r1.col2 IS DISTINCT FROM r2.col2;

Solution

1. Step 1: Understand the goal

   We want pairs where col1 or col2 differ, treating NULLs as real values.

2. Step 2: Analyze each option

   SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 IS DISTINCT FROM r2.col1 OR r1.col2 IS DISTINCT FROM r2.col2; uses IS DISTINCT FROM with OR, correctly finding pairs differing in either column. SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 <> r2.col1 OR r1.col2 <> r2.col2; uses <> which fails with NULLs. SELECT * FROM records r1 JOIN records r2 ON r1.id = r2.id WHERE r1.col1 IS DISTINCT FROM r2.col1 AND r1.col2 IS DISTINCT FROM r2.col2; joins on equal ids, so no pairs. SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 IS NOT DISTINCT FROM r2.col1 AND r1.col2 IS NOT DISTINCT FROM r2.col2; finds pairs where columns are NOT distinct, opposite of goal.

3. Final Answer:

   SELECT * FROM records r1 JOIN records r2 ON r1.id <> r2.id WHERE r1.col1 IS DISTINCT FROM r2.col1 OR r1.col2 IS DISTINCT FROM r2.col2 -> Option D

4. Quick Check:

   Use IS DISTINCT FROM with OR for NULL-safe difference [OK]

Hint: Use IS DISTINCT FROM with OR to find any column differences [OK]

Common Mistakes:

• Using <> which fails with NULLs
• Joining on equal ids instead of different ids
• Using IS NOT DISTINCT FROM which finds equal rows