In MySQL, IS NULL and IS NOT NULL are used to test for NULL values in columns. These operators help filter records based on whether a column contains NULL or non-NULL values.

IS NULL Operator

The IS NULL operator is used to check if a column contains a NULL value. NULL represents the absence of a value or an unknown value in SQL.

Syntax

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

Example

To find all employees whose commission field is not set (i.e., is NULL):

SELECT name, commission
FROM employees
WHERE commission IS NULL;

In this query, only employees with a NULL value in the commission column will be included in the results.

IS NOT NULL Operator

The IS NOT NULL operator is used to check if a column does not contain a NULL value. It filters out rows where the column has NULL and returns rows where the column has a defined value.

Syntax

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

Example

To find all employees who have a set commission value (i.e., not NULL):

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

In this query, only employees with a non-NULL value in the commission column will be included in the results.

Key Points

1. NULL Representation:

   • NULL is different from an empty string ('') or zero (0). It represents the absence of a value, not a value itself.

2. Comparison with NULL:

   • Standard comparison operators (=, <>, <, >, etc.) do not work with NULL. You must use IS NULL or IS NOT NULL to test for NULL values.

3. Logical Operations:

   • When combining conditions involving NULL values, be mindful that NULL can affect the result. For example, any comparison with NULL results in NULL (unknown), which can influence logical operations.

4. NULL Handling:

   • Use functions like COALESCE() or IFNULL() to handle NULL values in queries. For example, COALESCE(column, 'default_value') returns a default value if column is NULL.

Examples

1. Finding Records with NULL Values

   To find customers who have not provided an email address:

   SELECT customer_id, name
   FROM customers
   WHERE email IS NULL;
   

2. Finding Records with Non-NULL Values

   To find orders where a shipping address is provided:

   SELECT order_id, shipping_address
   FROM orders
   WHERE shipping_address IS NOT NULL;
   

3. Using COALESCE() to Handle NULL Values

   To display a default message if the phone_number is NULL:

   SELECT name, COALESCE(phone_number, 'No phone number provided') AS contact
   FROM contacts;
   

In this example, if phone_number is NULL, the query returns 'No phone number provided' instead.

Best Practices

• Always Handle NULL: Be explicit about handling NULL values to avoid unexpected results.
• Use Functions for NULL Handling: Functions like COALESCE() or IFNULL() can help manage NULL values more gracefully.
• Understand NULL in Joins: NULL values can affect joins and aggregations. Ensure proper handling to avoid issues in queries.