MySQL ISNULL() Function

MySQL ISNULL() Function

The MySQL ISNULL() function is used to check whether an expression is NULL. This function is essential for querying and handling NULL values in SQL queries.

Syntax

SELECT ISNULL(expression) AS result
FROM table_name;

The ISNULL() function has the following components:

  • expression: The expression to be checked.
  • result: An alias for the resulting value, which is 1 if the expression is NULL and 0 if it is not NULL.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL ISNULL() Function

Let's look at some examples of the MySQL ISNULL() function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    bonus DECIMAL(10, 2) DEFAULT NULL
);

This query creates a table named employees with columns for id, first_name, last_name, department, salary, and bonus.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, salary, bonus)
VALUES ('John', 'Doe', 'HR', 50000.00, NULL),
       ('Jane', 'Smith', 'IT', 60000.00, 5000.00),
       ('Jim', 'Brown', 'IT', 55000.00, NULL),
       ('Jake', 'White', 'HR', 52000.00, 3000.00),
       ('Jill', 'Green', 'Marketing', 45000.00, 2000.00);

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using ISNULL() with SELECT Statement

Use the ISNULL() function to check if a column value is NULL:

SELECT first_name, last_name, ISNULL(bonus) AS is_bonus_null
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and checks if the bonus column is NULL. The result will be 1 if the bonus is NULL and 0 if it is not.

MySQL ISNULL() WITH SELECT STATEMENT

Step 5: Using ISNULL() with WHERE Clause

Use the ISNULL() function in a WHERE clause:

SELECT * 
FROM employees 
WHERE ISNULL(bonus);

This query retrieves all columns from the employees table where the bonus column is NULL.

MySQL ISNULL() WITH WHERE CLAUSE

Step 6: Using ISNULL() with Multiple Columns

Use the ISNULL() function with multiple columns:

SELECT first_name, last_name, ISNULL(bonus) AS is_bonus_null, ISNULL(salary) AS is_salary_null
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and checks if the bonus and salary columns are NULL.

MySQL ISNULL() WITH MULTIPLE COLUMNS

Conclusion

The MySQL ISNULL() function is a powerful tool for checking whether an expression is NULL in SQL queries. Understanding how to use the ISNULL() function is essential for effective data querying and analysis in MySQL.