MySQL IS NULL Operator

The IS NULL operator in MySQL is a powerful tool for handling records with missing or incomplete data. It enables precise querying and data management by allowing users to identify and act upon fields where values are absent.

In this article, We will learn about the MySQL IS NULL Operator by understanding various examples and so on.

MySQL IS NULL Operator

• The IS NULL operator in MySQL efficiently identifies and manages records with missing or incomplete data.
  
• It supports accurate queries and data integrity.
  
• It Facilitates complex querying scenarios by allowing conditions based on the presence or absence of values.
  
• It Helps to maintain clean and accurate data by enabling checks for incomplete records.

Syntax

The IS NULL operator is used in SQL queries to test whether a column value is NULL. The basic syntax for using IS NULL is:

SELECT column1, column2, ...

FROM table_name

WHERE column_name IS NULL;

Examples of MySQL IS NULL

Example 1: IS NULL with SELECT Statement

Consider a table named orders with the following structure:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_name VARCHAR(50),

order_date DATE,

shipping_date DATE

);

Inserting sample data into the orders table:

INSERT INTO orders (order_id, customer_name, order_date, shipping_date) VALUES

(1, 'Alice', '2024-07-01', NULL),

(2, 'Bob', '2024-07-02', '2024-07-05'),

(3, 'Charlie', '2024-07-03', NULL),

(4, 'Dana', '2024-07-04', '2024-07-06');

Output:

To retrieve records where the shipping_date is NULL:

SELECT order_id, customer_name, order_date

FROM orders

WHERE shipping_date IS NULL;

Output:

This result shows orders that have not been shipped yet, as their shipping_date is NULL.

Example 2: IS NULL with COUNT() Function

To count the number of orders that have not been shipped:

SELECT COUNT(*)

FROM orders

WHERE shipping_date IS NULL;

Output:

This output indicates that there are 2 orders with a NULL shipping_date.

Example 3: IS NULL with UPDATE Statement

To update the shipping_date for orders that have not been shipped yet:

UPDATE orders

SET shipping_date = '2024-07-10'

WHERE shipping_date IS NULL;

Output:

After executing this query, the table orders will be updated. To view the changes, you can run:

SELECT * FROM orders;

Updated Table Data:

This result shows that the shipping_date for the orders with order_id 1 and 3, which previously had NULL values, has been updated to '2024-07-10'.

Example 4: IS NULL with DELETE Statement

To delete records where the shipping_date is NULL:

DELETE FROM orders

WHERE shipping_date IS NULL;

After running the delete statement, to view the remaining records:

SELECT * FROM orders;

Output:

This result shows that the orders with NULL shipping_date have been deleted from the table.

Conclusion

Overall, IS NULL operator is very useful in searching for records in which one or several fields may not have any data. As in this case, the procedures using IS NULL helps in finding the order that has not been shipped. This capability is essential when handling needed data as well as in cases when some operations require the absence or presence of data in certain columns.