SQL WHERE IS NOT NULL: Complete Guide to Filtering NULL Values

Introduction

Handling NULL values is a fundamental aspect of SQL database management. The SQL WHERE IS NOT NULL clause is essential for filtering out NULL values from your query results. This comprehensive guide covers everything you need to know about using IS NOT NULL in SQL queries.

Understanding NULL in SQL

What is NULL?

NULL represents the absence of a value. It's important to understand:

NULL is not the same as zero (0)
NULL is not the same as an empty string ('')
NULL means "unknown" or "not applicable"
NULL requires special handling in SQL

NULL Comparison Rules

Important: You cannot use = or != with NULL:

sql

-- This does NOT work as expected
SELECT * FROM employees WHERE salary = NULL;  -- Returns nothing
SELECT * FROM employees WHERE salary != NULL; -- Returns nothing

Instead, use:

IS NULL - to check for NULL
IS NOT NULL - to check for non-NULL values

Basic Syntax

IS NOT NULL Syntax

sql

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

Simple Example

sql

SELECT employee_name, email, phone
FROM employees
WHERE email IS NOT NULL;

Result: Returns only employees who have an email address (not NULL).

Common Use Cases

1. Filtering Required Fields

sql

-- Get all customers with email addresses
SELECT customer_id, customer_name, email
FROM customers
WHERE email IS NOT NULL;

2. Excluding Incomplete Records

sql

-- Get complete order records
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE customer_id IS NOT NULL
  AND order_date IS NOT NULL
  AND total_amount IS NOT NULL;

3. Data Quality Checks

sql

-- Find records with missing critical data
SELECT *
FROM products
WHERE price IS NULL OR description IS NULL;

4. Joining Tables

sql

-- Join only when foreign key exists
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.customer_id IS NOT NULL;

Multiple Column Filtering

Using AND

sql

-- All specified columns must be NOT NULL
SELECT *
FROM employees
WHERE email IS NOT NULL
  AND phone IS NOT NULL
  AND department IS NOT NULL;

Using OR

sql

-- At least one column must be NOT NULL
SELECT *
FROM contacts
WHERE email IS NOT NULL
   OR phone IS NOT NULL
   OR address IS NOT NULL;

Combining with Other Conditions

IS NOT NULL with Comparison Operators

sql

-- Employees with salary and salary > 50000
SELECT employee_name, salary
FROM employees
WHERE salary IS NOT NULL
  AND salary > 50000;

IS NOT NULL with LIKE

sql

-- Products with description and description contains 'premium'
SELECT product_name, description
FROM products
WHERE description IS NOT NULL
  AND description LIKE '%premium%';

IS NOT NULL with IN

sql

-- Customers with email in specific domains
SELECT customer_name, email
FROM customers
WHERE email IS NOT NULL
  AND email LIKE '%@gmail.com'
   OR email LIKE '%@yahoo.com';

Aggregate Functions and NULL

COUNT with IS NOT NULL

sql

-- Count non-NULL values
SELECT COUNT(email) AS customers_with_email
FROM customers
WHERE email IS NOT NULL;

Note: COUNT(column) automatically excludes NULLs, but being explicit is clearer.

SUM, AVG with IS NOT NULL

sql

-- Calculate average salary excluding NULLs
SELECT 
    department,
    AVG(salary) AS avg_salary,
    COUNT(salary) AS employees_with_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY department;

NULL Handling in Different Databases

MySQL

sql

SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

PostgreSQL

sql

-- Standard IS NOT NULL
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

-- Can also use COALESCE
SELECT *
FROM table_name
WHERE COALESCE(column_name, '') != '';

SQL Server

sql

SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

Oracle

sql

SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

Practical Examples

Example 1: Customer Contact Information

sql

-- Get customers with complete contact info
SELECT 
    customer_id,
    customer_name,
    email,
    phone,
    address
FROM customers
WHERE email IS NOT NULL
  AND phone IS NOT NULL
  AND address IS NOT NULL;

Example 2: Product Catalog

sql

-- Products ready for sale (all required fields present)
SELECT 
    product_id,
    product_name,
    price,
    stock_quantity,
    description
FROM products
WHERE product_name IS NOT NULL
  AND price IS NOT NULL
  AND stock_quantity IS NOT NULL
  AND price > 0;

Example 3: Employee Records

sql

-- Active employees with complete records
SELECT 
    employee_id,
    first_name,
    last_name,
    email,
    department,
    salary
FROM employees
WHERE email IS NOT NULL
  AND department IS NOT NULL
  AND salary IS NOT NULL
  AND status = 'active';

Example 4: Order Processing

sql

-- Processable orders (all required data present)
SELECT 
    order_id,
    customer_id,
    order_date,
    shipping_address,
    total_amount
FROM orders
WHERE customer_id IS NOT NULL
  AND order_date IS NOT NULL
  AND shipping_address IS NOT NULL
  AND total_amount IS NOT NULL
  AND total_amount > 0;

Alternative Approaches

Using COALESCE

sql

-- Replace NULL with default value
SELECT 
    customer_name,
    COALESCE(email, 'No email') AS email
FROM customers
WHERE COALESCE(email, '') != '';

Using CASE Statement

sql

-- Handle NULL in SELECT
SELECT 
    product_name,
    CASE 
        WHEN price IS NOT NULL THEN price
        ELSE 0
    END AS price
FROM products;

Common Mistakes

❌ Mistake 1: Using = NULL

sql

-- Wrong: This never returns results
SELECT * FROM employees WHERE salary = NULL;

-- Correct
SELECT * FROM employees WHERE salary IS NULL;

❌ Mistake 2: Using != NULL

sql

-- Wrong: This never returns results
SELECT * FROM employees WHERE salary != NULL;

-- Correct
SELECT * FROM employees WHERE salary IS NOT NULL;

❌ Mistake 3: Forgetting NULL in WHERE

sql

-- Might miss records if you don't consider NULL
SELECT * FROM products WHERE price > 100;
-- This excludes NULL prices

-- If you want to include/exclude NULLs explicitly
SELECT * FROM products 
WHERE price > 100 AND price IS NOT NULL;

Performance Considerations

Indexes and NULL

Important: Most databases can index NULL values, but behavior varies:

sql

-- Create index that helps with IS NOT NULL queries
CREATE INDEX idx_email ON customers(email) WHERE email IS NOT NULL;

Query Optimization

sql

-- More efficient: Filter early
SELECT *
FROM large_table
WHERE important_column IS NOT NULL
  AND other_conditions;

-- Less efficient: Filter late
SELECT *
FROM large_table
WHERE other_conditions
  AND important_column IS NOT NULL;

Best Practices

1. Be Explicit

sql

-- Good: Explicit NULL handling
WHERE email IS NOT NULL

-- Less clear: Implicit (might work but unclear)
WHERE email != ''

2. Combine Conditions Clearly

sql

-- Good: Clear logic
WHERE column1 IS NOT NULL
  AND column2 IS NOT NULL
  AND column3 > 100

-- Confusing: Mixed conditions
WHERE column1 IS NOT NULL AND column2 > 100 OR column3 IS NOT NULL

3. Document NULL Handling

sql

-- Add comments for complex NULL logic
-- Get records where all required fields are present
SELECT *
FROM table_name
WHERE required_field1 IS NOT NULL
  AND required_field2 IS NOT NULL;

Comparison: IS NULL vs IS NOT NULL

IS NULL

sql

-- Find records with NULL values
SELECT *
FROM employees
WHERE email IS NULL;

IS NOT NULL

sql

-- Find records without NULL values
SELECT *
FROM employees
WHERE email IS NOT NULL;

Both Conditions

sql

-- This is always true (covers all cases)
SELECT *
FROM employees
WHERE email IS NULL OR email IS NOT NULL;
-- (Returns all employees)

Conclusion

Mastering SQL WHERE IS NOT NULL is essential for effective database querying. It allows you to filter out NULL values and work with complete data records.

Key Takeaways:

Use IS NOT NULL to filter non-NULL values
Never use = NULL or != NULL (they don't work)
Combine with other conditions using AND/OR
Consider performance when filtering large tables
Be explicit about NULL handling in your queries

Remember:

NULL means "unknown" or "not applicable"
IS NOT NULL finds records with actual values
Always be explicit about NULL handling
Consider indexes for frequently filtered columns

Practice these examples and you'll master NULL handling in SQL!