IS DISTINCT FROM for NULL-safe comparison in PostgreSQL - Mini Project: Build & Apply

Using IS DISTINCT FROM for NULL-safe Comparison in PostgreSQL

📖 Scenario: You are managing a customer database where some customers may not have provided their email addresses yet (NULL values). You want to find customers whose email addresses have changed compared to a previous record, but you need to handle NULL values safely without errors.

🎯 Goal: Build a SQL query using IS DISTINCT FROM to safely compare email addresses, including NULL values, to identify customers with changed emails.

📋 What You'll Learn

Create a table called customers with columns id (integer) and email (text).

Insert sample data with some NULL email values.

Create a table called previous_customers with the same structure and sample data.

Write a SQL query using IS DISTINCT FROM to find customers whose current email is different from the previous email, including NULL-safe comparison.

💡 Why This Matters

🌍 Real World

Handling NULL values safely is important in real databases where missing data is common. Using <code>IS DISTINCT FROM</code> helps avoid errors and incorrect results when comparing such data.

💼 Career

Database developers and analysts often need to compare records safely, especially when data can be incomplete. This skill is useful for data cleaning, auditing, and synchronization tasks.

1

Create the customers table and insert data

Create a table called customers with columns id (integer) and email (text). Insert these exact rows: (1, '[e-mail]'), (2, NULL), (3, '[e-mail]').

PostgreSQL

-- Create the customers table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as specified.

2

Create the previous_customers table and insert data

Create a table called previous_customers with columns id (integer) and email (text). Insert these exact rows: (1, '[e-mail]'), (2, '[e-mail]'), (3, NULL).

PostgreSQL

CREATE TABLE customers (id INTEGER, email TEXT);
INSERT INTO customers (id, email) VALUES
  (1, '[e-mail]'),
  (2, NULL),
  (3, '[e-mail]');
-- Create the previous_customers table and insert data
-- Your code here

Need a hint?

Repeat the table creation and insertion steps for previous_customers with the exact rows given.

3

Write a query using IS DISTINCT FROM for NULL-safe comparison

Write a SQL query that selects id and current email from customers joined with previous_customers on id, where customers.email IS DISTINCT FROM previous_customers.email.

PostgreSQL

CREATE TABLE customers (id INTEGER, email TEXT);
INSERT INTO customers (id, email) VALUES
  (1, '[e-mail]'),
  (2, NULL),
  (3, '[e-mail]');

CREATE TABLE previous_customers (id INTEGER, email TEXT);
INSERT INTO previous_customers (id, email) VALUES
  (1, '[e-mail]'),
  (2, '[e-mail]'),
  (3, NULL);

-- Write the query using IS DISTINCT FROM
-- Your code here

Need a hint?

Use a JOIN on id and the IS DISTINCT FROM operator in the WHERE clause to compare emails safely.

4

Complete the query with ORDER BY for clear results

Add ORDER BY customers.id at the end of the query to sort the results by id.

PostgreSQL

CREATE TABLE customers (id INTEGER, email TEXT);
INSERT INTO customers (id, email) VALUES
  (1, '[e-mail]'),
  (2, NULL),
  (3, '[e-mail]');

CREATE TABLE previous_customers (id INTEGER, email TEXT);
INSERT INTO previous_customers (id, email) VALUES
  (1, '[e-mail]'),
  (2, '[e-mail]'),
  (3, NULL);

SELECT customers.id, customers.email
FROM customers
JOIN previous_customers ON customers.id = previous_customers.id
WHERE customers.email IS DISTINCT FROM previous_customers.email
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY customers.id to sort the output by customer ID.