MySQL IS NULL

MySQL IS NULL

The IS NULL operator in MySQL is used to check if a column's value is NULL. A NULL value represents missing or unknown data, and it is different from an empty string ('') or zero (0).

Syntax

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

1. Checking for NULL Values

Example

Retrieve all employees whose phone number is not recorded:

SELECT id, name, phone_number
FROM employees
WHERE phone_number IS NULL;

Result

+----+--------+--------------+
| id | name   | phone_number |
+----+--------+--------------+
| 2  | Bob    | NULL         |
| 5  | Eve    | NULL         |
+----+--------+--------------+

2. Checking for NOT NULL Values

To find rows where a column is not NULL, use the IS NOT NULL operator.

Example

Retrieve all employees who have a phone number:

SELECT id, name, phone_number
FROM employees
WHERE phone_number IS NOT NULL;

Result

+----+--------+--------------+
| id | name   | phone_number |
+----+--------+--------------+
| 1  | Alice  | 123-456-7890 |
| 3  | Carol  | 987-654-3210 |
+----+--------+--------------+

3. NULL in Comparison Operators

NULL cannot be compared using = or !=. Instead, you must use IS NULL or IS NOT NULL.

Incorrect Query:

SELECT * FROM employees WHERE phone_number = NULL; -- No rows returned

Correct Query:

SELECT * FROM employees WHERE phone_number IS NULL;

4. Practical Example

Sample Table: orders

+----+------------+-------------+--------+
| id | customer   | order_date  | status |
+----+------------+-------------+--------+
| 1  | Alice      | 2024-01-10  | NULL   |
| 2  | Bob        | 2024-01-12  | Shipped|
| 3  | Carol      | NULL        | NULL   |
| 4  | Dave       | 2024-01-15  | Pending|
+----+------------+-------------+--------+

Query: Find orders with missing statuses:

SELECT id, customer, status
FROM orders
WHERE status IS NULL;

Result:

+----+----------+--------+
| id | customer | status |
+----+----------+--------+
| 1  | Alice    | NULL   |
| 3  | Carol    | NULL   |
+----+----------+--------+

5. NULL in Aggregate Functions

Aggregate functions like COUNT, SUM, and AVG automatically ignore NULL values.

Example

Count all orders, including and excluding NULL statuses:

-- Count all rows
SELECT COUNT(*) AS total_orders FROM orders;

-- Count rows with non-NULL statuses
SELECT COUNT(status) AS non_null_status_orders FROM orders;

Result:

+---------------+
| total_orders  |
+---------------+
| 4             |
+---------------+

+------------------------+
| non_null_status_orders |
+------------------------+
| 2                      |
+------------------------+

6. Combining IS NULL with Other Clauses

You can use IS NULL with JOIN, GROUP BY, or ORDER BY clauses.

Example: JOIN

Find customers who have not placed any orders:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

7. Common Mistakes

1. Using = for NULL:

   SELECT * FROM employees WHERE phone_number = NULL; -- Incorrect
   

   Use IS NULL instead.

2. Misinterpreting NULL: A NULL value does not mean "empty" or "zero." It indicates missing or unknown data.

8. Performance Considerations

• Ensure indexes are used on columns checked for NULL values for better query performance.
• If NULL values are frequently queried, considering their impact on storage and indexing.

9. Alternatives to NULL

In some cases, you might want to replace NULL values with a default using COALESCE or IFNULL.

Replace NULL with "N/A":

SELECT id, name, IFNULL(phone_number, 'N/A') AS phone_number
FROM employees;

Replace NULL with 0:

SELECT id, COALESCE(salary, 0) AS salary
FROM employees;

Let me know if you'd like further examples or details!