Previous

SQL IS NULL

The IS NULL operator in SQL is used to filter rows where the column value is NULL. A NULL value represents missing or undefined data.

 

Basic Syntax of IS NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;
  • column_name: The column to check for NULL values.

 

Key Features of IS NULL

1. Check for NULL Values

The IS NULL operator retrieves rows where the column value is NULL.

SELECT name  
FROM employees  
WHERE department IS NULL;
  • Retrieves names of employees where the department is NULL.

2. Check for Non-NULL Values

Use IS NOT NULL to filter rows where the column does not contain NULL values.

SELECT name  
FROM employees  
WHERE department IS NOT NULL;
  • Retrieves names of employees where the department has a defined value.

3. Using IS NULL with Multiple Conditions

Combine IS NULL with other conditions using AND or OR.

SELECT name  
FROM employees  
WHERE department IS NULL  
   OR salary > 5000;
  • Retrieves employees with no department or a salary greater than 5000.

 

Example Queries

1. Find Rows with NULL Values

SELECT order_id, customer_id  
FROM orders  
WHERE customer_id IS NULL;
  • Retrieves orders where the customer_id is NULL.

2. Exclude Rows with NULL Values

SELECT product_id, product_name  
FROM products  
WHERE price IS NOT NULL;
  • Retrieves products where the price is defined.

3. Filter Rows Based on NULL and Non-NULL Conditions

SELECT id, email  
FROM users  
WHERE email IS NULL  
   AND phone_number IS NOT NULL;
  • Retrieves users whose email is missing but the phone number is defined.

 

Important Notes

NULL is Not the Same as 0 or '' (Empty String):

  • A NULL value indicates missing or unknown data.
  • Use IS NULL instead of equality operators like =.

NULL in Arithmetic Operations:

  • Any arithmetic operation involving NULL results in NULL.

 

Summary

The IS NULL operator is used to filter rows where a column value is NULL (missing or undefined). Use IS NOT NULL to filter rows with defined values. Proper handling of NULL is essential to ensure accurate query results.

Previous