MySQL IS NULL: A Complete Guide

What is MySQL IS NULL?

The IS NULL condition in MySQL is used to check if a column contains NULL values. A NULL value represents missing or undefined data in a database table. Unlike other values, NULL is not equal to anything, not even itself, and requires the IS NULL condition for checking.

Syntax

Check for NULL

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

Check for NOT NULL

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

How Does IS NULL Work?

  • IS NULL: Returns rows where the specified column contains NULL values.
  • IS NOT NULL: Returns rows where the specified column does not contain NULL values.

    Examples

    1. Basic Example with IS NULL

SELECT id, name, email
FROM users
WHERE email IS NULL;
  • Retrieves users who have not provided an email address.

Output:

IDNameEmail
3AliceNULL

2. Basic Example with IS NOT NULL

SELECT id, name, email
FROM users
WHERE email IS NOT NULL;
  • Retrieves users who have provided an email address.

Output:

IDNameEmail
1Johnjohn@gmail.com
2Sarahsarah@gmail.com

3. Using IS NULL in JOIN Queries

SELECT orders.id, customers.name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id
WHERE customers.id IS NULL;
  • Retrieves orders without a matching customer.

4. Using IS NULL with Aggregate Functions

SELECT COUNT(*) AS null_count
FROM users
WHERE email IS NULL;
  • Counts the number of users without an email address.

Output:

Null Count
5

5. IS NULL in UPDATE Queries

UPDATE users
SET email = 'unknown@gmail.com'
WHERE email IS NULL;
  • Replaces NULL email values with a default email address.

6. IS NULL with DELETE

DELETE FROM users
WHERE email IS NULL;

Deletes rows where the email column contains NULL.

Best Practices

Avoid NULL Values When Possible:

Avoid NULL Values When Possible: Use default values or constraints to minimize the use of NULL.

Use IS NULL and IS NOT NULL Properly: Avoid using = or != to check for NULL values.

Handle NULL in Joins: Use IS NULL to identify unmatched rows in LEFT JOIN or RIGHT JOIN queries.

Indexing and NULL: Understand that some database engines may handle indexing differently for NULL values.

Common Mistakes

  1. Using = to Check for NULL
-- Incorrect
SELECT * FROM users WHERE email = NULL;
  • Always use IS NULL or IS NOT NULL.

  • Ignoring NULL in Aggregate Functions

    • Aggregate functions like SUM() or AVG() ignore NULL values. Be mindful when calculating results.

  • Default Values for NULL Columns

    • Set default values for columns to avoid unexpected NULL.

MySQL Workbench Instructions

  • Open MySQL Workbench and connect to your database.
  • Write your query using IS NULL or IS NOT NULL in the SQL editor.
  • Execute the query to identify or exclude rows with NULL values.
  • Analyze the results and adjust your query conditions as needed.
[WpProQuiz 18]