Sql Is Null – Guided Study Workspace

Introduction

In real-world databases, information is frequently incomplete. Consider a customer directory:

Employee	Phone Number	Status
Rahul	`9876543210`	Known & Available
Priya	NULL	Missing / Unknown / Not Provided
Amit	`9123456789`	Known & Available

In SQL, NULL is a special marker representing the absence of a value. It is not automatically the number zero, not an empty text string, and not the text word "NULL".

What Is NULL?

NULL signifies that a data value is missing, unknown, unassigned, or not applicable.

State	SQL Representation	What It Truly Means
NULL	`NULL`	No value exists; value is unknown or unrecorded.
Numeric Zero	`0`	An exact known numeric quantity of zero.
Empty String	`''`	A known text string containing 0 characters.
Literal Text	`'NULL'`	A 4-letter word ('N', 'U', 'L', 'L').

Diagram 1: Distinguishing NULL From Normal Values

NULL
Missing / Unknown

≠

0
Numeric Zero

≠

''
Empty String

≠

'NULL'
4-Letter Word

Why Does NULL Exist?

Relational databases require a standard way to store data when a field cannot have a regular value:

Information was not provided: A user signed up with an email but skipped the optional phone number.
Data is currently unknown: A shipment is created, but the delivery date is not yet known.
Data is not applicable: An employee is paid an hourly wage, so the annual salary column does not apply.

NULL Is Not a Normal Value: Why = NULL Fails

The #1 mistake SQL beginners make is attempting to query NULL values using standard equality:

-- ❌ THIS NEVER WORKS (Returns 0 rows):
SELECT * FROM employees
WHERE phone = NULL;

⚠️

Why does = NULL fail? In SQL logic, NULL represents an unknown. If you ask the database: "Is this unknown phone number equal to another unknown?", the database cannot say TRUE or FALSE. It answers UNKNOWN. Because a WHERE clause only returns rows where the condition is confirmed TRUE, = NULL drops every row!

The IS NULL Operator

To check if a column value is missing, SQL provides the dedicated IS NULL operator:

-- ✅ Correct way to find missing values:
SELECT name, department
FROM employees
WHERE phone IS NULL;

Diagram 2: IS NULL Evaluation

Column Value: phone = NULL

➔ IS NULL? ➔

TRUE (Row Returned)

The IS NOT NULL Operator

To find all rows where a column has a recorded, non-missing value, use IS NOT NULL:

SELECT name, phone
FROM employees
WHERE phone IS NOT NULL;

NULL vs Zero vs Empty String Deep-Dive

Confusing these values causes severe data calculation bugs. Consider an e-commerce customer table:

Column Value	Business Meaning	Matches `IS NULL`?	Matches `= 0`?	Matches `= ''`?
`NULL`	Customer never answered	TRUE	UNKNOWN	UNKNOWN
`0`	Customer explicitly has 0 orders	FALSE	TRUE	FALSE
`''` (Empty)	Blank text box submitted	FALSE	FALSE	TRUE

NULL and Comparison Operators

When comparing columns with numbers or text (e.g. >, <, =, <>), rows where the column is NULL evaluate to UNKNOWN:

SELECT * FROM employees
WHERE salary > 50000;

If Sneha's salary is NULL, NULL > 50000 produces UNKNOWN. Since WHERE requires TRUE, Sneha is dropped from the result set.

Diagram 3: Three-Valued Logic in WHERE Filtering

Condition = TRUE
✓ Row Returned

Condition = FALSE
✗ Row Excluded

Condition = UNKNOWN (NULL)
✗ Row Excluded

NULL With AND / OR / NOT

When logical operators interact with UNKNOWN values, they follow three-valued logic truth rules:

Expression	Evaluation Result	Explanation
`TRUE AND UNKNOWN`	UNKNOWN	AND requires all parts to be TRUE; unknown status leaves it unresolved.
`FALSE AND UNKNOWN`	FALSE	Since one part is already FALSE, the entire AND condition is definitely FALSE.
`TRUE OR UNKNOWN`	TRUE	Since one part is already TRUE, the OR condition is satisfied regardless!
`NOT UNKNOWN`	UNKNOWN	Negating an unknown value is still unknown.

Finding and Cleaning NULL Data

Data analysts spend a significant portion of time isolating missing records for data quality audits:

-- Audit missing contact info
SELECT id, name
FROM customers
WHERE email IS NULL OR phone IS NULL;

⚡ Live Interactive NULL Lab

📦 Candidate Row Three-Valued Evaluation8 Total Records

Name	Dept	Salary	Phone	Evaluation Status
Rahul Sharma	Sales	₹62,000	9876543210	Phone is '9876543210' ➔ FALSE (Drop)
Priya Patel	HR	₹48,000	NULL	Phone is NULL ➔ TRUE (Keep)
Amit Verma	Engineering	₹85,000	9123456789	Phone is '9123456789' ➔ FALSE (Drop)
Sneha Rao	Sales	NULL	9988776655	Phone is '9988776655' ➔ FALSE (Drop)
Vikram Singh	Intern	₹0	NULL	Phone is NULL ➔ TRUE (Keep)
Ananya Gupta	HR	₹45,000	'' (Empty)	Phone is '' ➔ FALSE (Drop)
Rohan Joshi	Sales	₹71,000	NULL	Phone is 'NULL' ➔ FALSE (Drop)
Kavita Nair	Engineering	NULL	9845123456	Phone is '9845123456' ➔ FALSE (Drop)

✍️ SQL NULL Query Editor● Three-Valued Evaluator

📋 Output Result Set2 Rows Kept (6 Dropped)

name	department	salary	phone
Priya Patel	HR	₹48,000	NULL
Vikram Singh	Intern	₹0	NULL

🔍 Interactive Tool: NULL vs 0 vs '' vs 'NULL'

Switch the sample cell value below to see how standard SQL filters evaluate against each distinct data state:

Current Cell State:

NULL (Absent)

WHERE col IS NULL:

TRUE (Matches!)

WHERE col = 0:

UNKNOWN (Does NOT match)

WHERE col = '':

UNKNOWN (Does NOT match)

Common NULL Mistakes

1. Writing = NULL or <> NULL

Never use comparison operators with NULL. Always use IS NULL or IS NOT NULL.

2. Assuming NULL Becomes Zero in Math

In SQL, salary + 1000 evaluates to NULL when salary is NULL. Math on NULL always produces NULL.

3. Searching for the String 'NULL'

Writing WHERE phone = 'NULL' searches for four literal alphabet characters, not missing data.

🧠 Interactive Challenge: Predict The Result (1 of 3)

SELECT name FROM employees WHERE phone = NULL;

How many rows will this query return?

Practical NULL Exercises

Task Goal	Target Table	Required SQL Solution	Concept Tested
1. Customers Without Phone	`customers`	`SELECT * FROM customers WHERE phone IS NULL;`	`IS NULL` syntax
2. Verified Active Emails	`users`	`SELECT * FROM users WHERE email IS NOT NULL;`	`IS NOT NULL` filter
3. Missing Product Descriptions	`products`	`SELECT * FROM products WHERE description IS NULL;`	Text column NULL detection
4. Both Contacts Missing	`contacts`	`SELECT * FROM contacts WHERE phone IS NULL AND email IS NULL;`	Multiple `IS NULL` conjunctions
5. High Earners With Known Salary	`employees`	`SELECT * FROM employees WHERE salary IS NOT NULL AND salary > 80000;`	Safe numeric filtering

NULL Best Practices

Always Use IS NULL / IS NOT NULL: Never write = NULL or != NULL.
Do Not Assume NULL Equals Zero: If an employee took 0 leave days, record 0. If their leave records are lost, record NULL.
Account for UNKNOWN in WHERE Filters: Be aware that standard comparison filters automatically exclude rows with NULL values.
Handle Missing Data Intentionally: Know your schema design and verify whether empty strings or NULL markers are used for blank user inputs.

What You Should Know Now

✓Meaning of NULL: Represents absent, missing, or unknown data
✓NULL vs 0: NULL is absence; 0 is an actual number
✓NULL vs '': NULL is absent; '' is a 0-character string
✓IS NULL: Tests if a value is missing
✓IS NOT NULL: Tests if a value is present
✓Three-Valued Logic: Comparisons with NULL produce UNKNOWN

🎯 Knowledge Check Quiz: SQL NULL

Test your understanding of missing data handling, three-valued logic, and IS NULL / IS NOT NULL operators.

1. What does NULL represent in a relational database?

2. Why does "WHERE column_name = NULL" never return any rows in standard SQL?

3. Which SQL clause correctly retrieves customers who do not have a recorded email address?

4. What is the boolean outcome of "WHERE 50000 = NULL"?

5. How does SQL WHERE treat a condition that evaluates to UNKNOWN?

6. Which query finds all employees who have a known, recorded phone number?

7. What is the critical difference between 0 and NULL in a "bonus_amount" column?

8. What is the recommended best practice when checking for missing text values in data analysis?