MySQL IS Operator

MySQL IS Operator

The MySQL IS operator is used to compare a value with NULL. This operator is essential for querying data that involves NULL values in SQL queries.

---

Syntax

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

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

The IS operator has the following components:

• column1, column2, ...: The columns to be retrieved.
• table_name: The name of the table from which to retrieve the data.
• column1 IS NULL: The condition to filter the records where column1 is NULL.
• column1 IS NOT NULL: The condition to filter the records where column1 is not NULL.

---

Example MySQL IS Operator

Let's look at some examples of the MySQL IS operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

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.

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.

Step 4: Using IS NULL with WHERE Clause

Use the IS NULL operator to filter records where a column value is NULL:

SELECT * 
FROM employees 
WHERE bonus IS NULL;

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

Step 5: Using IS NOT NULL with WHERE Clause

Use the IS NOT NULL operator to filter records where a column value is not NULL:

SELECT * 
FROM employees 
WHERE bonus IS NOT NULL;

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

Step 6: Using IS with Multiple Columns

Use the IS operator with multiple columns:

SELECT first_name, last_name, bonus 
FROM employees 
WHERE bonus IS NULL OR salary IS NOT NULL;

This query retrieves the first_name, last_name, and bonus columns from the employees table where the bonus is NULL or the salary is not NULL.

---

Conclusion

The MySQL IS operator is a powerful tool for filtering records that involve NULL values. Understanding how to use the IS operator is essential for effective data querying and analysis in MySQL.