SQL

IS NOT NULL in SQL

✅ The IS NOT NULL operator is used to check whether a column contains a value. It returns rows where the specified column is not NULL, making it useful for filtering records that contain valid or available data.

📖 What is IS NOT NULL?

In SQL, NULL represents a missing, unknown, or undefined value. The IS NOT NULL operator identifies rows where a column contains an actual value instead of NULL. Since NULL cannot be compared using operators such as = or <>, SQL provides the special operators IS NULL and IS NOT NULL.

Information

IS NOT NULL is supported by all major relational database systems and is the standard SQL method for checking whether a value exists.

🎯 Why Use IS NOT NULL?

The IS NOT NULL operator helps retrieve records that contain meaningful data.

📌 Find complete records.
📌 Exclude missing or unknown values.
📌 Validate required information.
📌 Improve report accuracy.
📌 Filter data before calculations and analysis.

📋 Sample Table

EmployeeID	EmployeeName	Email	ManagerID
101	Alice	alice@example.com	201
102	Bob	NULL	201
103	Charlie	charlie@example.com	NULL

📝 Basic Syntax

IS NOT NULL Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

💡 Example: Find Employees with an Email Address

Using IS NOT NULL

SELECT EmployeeID, EmployeeName
FROM Employees
WHERE Email IS NOT NULL;

This query returns only employees whose Email column contains a value.

💡 Example: Find Employees with an Assigned Manager

Find Non-NULL Manager IDs

SELECT EmployeeName
FROM Employees
WHERE ManagerID IS NOT NULL;

🚫 Incorrect Way to Compare NULL

The following query does not correctly identify rows with values.

Incorrect NULL Comparison

SELECT *
FROM Employees
WHERE Email <> NULL;

Warning

Never compare NULL using = or <>. Use IS NULL or IS NOT NULL instead.

📊 Example Output

EmployeeID	EmployeeName	Email
101	Alice	alice@example.com
103	Charlie	charlie@example.com

⚖️ IS NULL vs IS NOT NULL

Operator	Purpose
IS NULL	Finds rows where the column has no value.
IS NOT NULL	Finds rows where the column contains a value.

💼 Real-World Examples

📧 Retrieve customers who have provided an email address.
👤 Find employees assigned to a manager.
📦 Display orders that have a shipping date.
🏥 List patients with emergency contact information.
📊 Generate reports using only complete records.

🗄️ Database Compatibility

Database System	IS NOT NULL Support
MySQL	✅ Fully supported.
PostgreSQL	✅ Fully supported.
SQL Server	✅ Fully supported.
Oracle	✅ Fully supported.
SQLite	✅ Fully supported.

⚠️ Common Mistakes

❌ Using <> NULL instead of IS NOT NULL.
❌ Assuming an empty string is always the same as NULL.
❌ Forgetting that NULL values are ignored by many aggregate functions.
❌ Ignoring NULL checks when validating required data.

Best Practice

Use IS NOT NULL when retrieving only complete records. Combine it with appropriate NOT NULL constraints to ensure required columns always contain valid data, and regularly validate data quality by checking for unexpected NULL values.

🚀 Key Points to Remember

📌 IS NOT NULL returns rows containing actual values.
📌 It excludes rows where the column is NULL.
📌 Never use <> NULL to test for non-NULL values.
📌 NULL is different from an empty string or zero.
📌 Use IS NULL and IS NOT NULL for reliable NULL handling.
📌 Proper NULL checks improve data quality and query accuracy.

>>"Knowing which values exist is just as important as knowing which ones don't."

Summary

✅ The IS NOT NULL operator is the standard SQL method for finding rows that contain actual values rather than missing data. It is widely used in filtering, reporting, validation, and data analysis to ensure queries work with complete and meaningful information.