SQL IS NULL and IS NOT NULL Operators

---

In SQL, NULL represents a missing or undefined value. It’s important to note that NULL is not the same as an empty string or zero; it’s simply the absence of a value. When working with databases, you often need to filter or test for NULL values, especially when dealing with incomplete or optional data.

The IS NULL and IS NOT NULL operators are used to handle NULL values in SQL queries. These operators allow you to check if a column contains NULL values or if it does not, enabling you to handle missing or undefined data appropriately.

---

1. SQL IS NULL Operator

The IS NULL operator is used to test whether a column contains a NULL value. This is useful when you want to filter rows where certain columns have missing or undefined values.

Syntax:

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

• column_name: The column you want to check for NULL values.

Example:

Let's say you have a customers table, and you want to find customers who have not provided an email address (i.e., the email column contains NULL).

SELECT customer_id, first_name, last_name
FROM customers
WHERE email IS NULL;

Result:

customer_id	first_name	last_name
3	Sarah	Brown
7	John	Smith

This query will return all customers whose email column contains NULL values, indicating that no email address is available.

Important Consideration:

• When comparing a column to NULL, you cannot use the = operator. SQL does not allow direct comparisons between a value and NULL (e.g., column_name = NULL will not work).
• Always use IS NULL or IS NOT NULL for checking NULL values.

---

2. SQL IS NOT NULL Operator

The IS NOT NULL operator is used to test whether a column does not contain a NULL value. This is useful when you want to retrieve records where a column has a defined, non-null value.

Syntax:

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

• column_name: The column you want to check for non-NULL values.

Example:

Suppose you want to find all customers who have provided an email address (i.e., the email column is not NULL).

SELECT customer_id, first_name, last_name, email
FROM customers
WHERE email IS NOT NULL;

Result:

customer_id	first_name	last_name	email
1	Jane	Doe	jane@example.com
2	Bob	Lee	bob@example.com

This query will return customers who have a valid email address (i.e., their email column is not NULL).

---

3. Using IS NULL and IS NOT NULL with Other Conditions

You can also use the IS NULL and IS NOT NULL operators in combination with other conditions (such as AND, OR, LIKE, etc.) to create more complex queries.

Example:

Suppose you want to find customers who have either not provided an email address or have a last name that starts with the letter "S":

SELECT customer_id, first_name, last_name, email
FROM customers
WHERE email IS NULL
   OR last_name LIKE 'S%';

Result:

customer_id	first_name	last_name	email
3	Sarah	Smith	NULL
5	John	Stewart	NULL
6	Emily	Scott	emily@example.com

This query returns customers who either have a NULL email address or whose last name starts with the letter "S".

---

4. Handling NULL in Aggregation Functions

When working with aggregate functions (e.g., COUNT(), SUM(), AVG()), it's important to understand how NULL values are treated. By default, most aggregate functions ignore NULL values, but they can be included or excluded based on your query logic.

Example with COUNT():

Let's say you want to count how many customers have provided an email address (i.e., how many non-NULL values exist in the email column):

SELECT COUNT(email) AS email_count
FROM customers;

Result:

email_count
6

The COUNT() function only counts non-NULL values, so it will return the number of customers who have a valid email address.

Example with COUNT(*) and IS NULL:

If you want to count all customers (including those with NULL email addresses), you can use COUNT(*) and filter based on IS NULL:

SELECT COUNT(*) AS total_customers,
       COUNT(email) AS email_count,
       COUNT(*) - COUNT(email) AS no_email_count
FROM customers;

Result:

total_customers	email_count	no_email_count
10	6	4

Here:

• COUNT(*) counts all rows (including NULL values),
• COUNT(email) counts only the non-NULL email addresses,
• The difference between these counts gives the number of customers who do not have an email address.

---

5. NULL Values in Joins

When performing SQL JOIN operations, NULL values can sometimes affect the results, especially when using LEFT JOIN or RIGHT JOIN. The IS NULL and IS NOT NULL operators are often used in the ON or WHERE clause to filter rows based on NULL values in join conditions.

Example:

Suppose you have two tables, orders and customers, and you want to find all orders where the customer information is missing (i.e., no matching record in the customers table).

SELECT orders.order_id, orders.order_date
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.customer_id IS NULL;

Result:

order_id	order_date
1001	2024-01-01
1002	2024-01-02

This query returns orders that do not have a corresponding customer record in the customers table (i.e., customer_id is NULL).

---

---