MySQL IS NOT Operator

MySQL IS NOT Operator

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

Syntax

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

The IS NOT 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 NOT NULL: The condition to filter the records where column1 is not NULL.

Example MySQL IS NOT Operator

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

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 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.

MySQL IS NOT NULL WITH WHERE CLAUSE

Step 5: Using IS NOT with Multiple Conditions

Use the IS NOT operator with multiple conditions:

SELECT * 
FROM employees 
WHERE bonus IS NOT NULL AND department = 'IT';

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

MySQL IS NOT NULL WITH MULTIPLE CONDITIONS

Step 6: Using IS NOT with Multiple Columns

Use the IS NOT operator with multiple columns:

SELECT first_name, last_name, bonus 
FROM employees 
WHERE bonus IS NOT 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 not NULL or the salary is not NULL.

MySQL IS NOT NULL WITH MULTIPLE COLUMNS

Conclusion

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