MySQL

The IS NOT NULL operator in MySQL is part of the Data Query Language (DQL) and is used to filter rows where a column contains non-NULL values. Unlike NULL, which represents missing or unknown data, non-NULL values are actual data entries. The IS NOT NULL operator allows you to retrieve only the rows where specific columns have valid data. This is especially useful when you want to ignore incomplete records in your queries.

Here’s an overview of how to use IS NOT NULL with examples and tips for new students:

1. Basic Syntax of IS NOT NULL:

   • The IS NOT NULL operator checks if a column contains a value that is not NULL.
   • Syntax:

   SELECT * 
   FROM employees 
   WHERE email IS NOT NULL;

   • This query retrieves all employees who have an email address (i.e., where the email column is not NULL).

2. Combining IS NOT NULL with Other Conditions:

   • You can combine IS NOT NULL with other operators like AND, OR, or BETWEEN to further refine the results.
   • Example:

   SELECT * 
   FROM employees 
   WHERE salary IS NOT NULL 
   AND department_id = 3;

   • This query returns employees who belong to department 3 and have a salary value (i.e., salary is not NULL).

3. Using IS NOT NULL in Joins:

   • When performing joins, IS NOT NULL can help filter out rows where a column from one table may not have a corresponding value in the other table.
   • Example:

   SELECT e.employee_id, e.first_name, d.department_name 
   FROM employees e 
   JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_id IS NOT NULL;

   • This query returns employees who belong to departments with a valid department_id (i.e., not NULL).

4. Using IS NOT NULL in Subqueries:

   • IS NOT NULL can be used in subqueries to exclude NULL values when performing complex queries.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id IN (SELECT department_id FROM departments WHERE manager_id IS NOT NULL);

   • This query retrieves employees whose departments have a manager assigned (i.e., the manager_id is not NULL).

5. Handling NULL in Aggregate Queries:

   • When performing aggregate queries like COUNT() or SUM(), you may want to exclude NULL values to ensure accurate results.
   • Example:

   SELECT COUNT(*) 
   FROM employees 
   WHERE salary IS NOT NULL;

   • This query counts the number of employees who have a recorded salary (i.e., salary is not NULL).

6. Performance Considerations:

   • Using IS NOT NULL can be resource-intensive when dealing with large tables, especially if the column being filtered is not indexed. To optimize performance, make sure to index frequently queried columns.
   • Example:

   SELECT * 
   FROM employees 
   WHERE manager_id IS NOT NULL;

   • If manager_id is indexed, this query will perform faster by quickly finding non-NULL values.

7. Using IS NOT NULL with Multiple Columns:

   • You can check for multiple non-NULL columns by combining conditions.
   • Example:

   SELECT * 
   FROM employees 
   WHERE email IS NOT NULL 
   AND phone_number IS NOT NULL;

   • This query returns employees who have both an email and phone number (i.e., neither column is NULL).

The IS NOT NULL operator is essential when you want to filter out rows with missing data and focus on entries that have complete or valid information. This can improve the quality of your results, especially when dealing with real-world datasets that often contain incomplete or inconsistent data.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM act_order
WHERE shipped_date IS NOT NULL;

SELECT *
FROM org_employee
WHERE date_of_birth IS NOT NULL;

Example 1:

Let's explore the procedure of extracting information from a designated table using the IS NOT NULL SQL operator. This query retrieves employees who have provided their date of birth.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT * FROM org_employee WHERE date_of_birth IS NOT NULL;

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Let's delve into the process of retrieving information from a specified table using the SQL IS NOT NULL operator on string data. The query retrieves all rows from the products table where the description is not null.

Example 2 - Raw data from product table

Example 2 - Query

SELECT * FROM prd_product WHERE description IS NOT NULL;

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. NOTE that description value for product id 7 and 9 compose a empty string, this is not a null.

Example 2 - Query Output

Example 3:

Using '<> NULL' is not permissible; instead, you must use 'IS NOT NULL'. As demonstrated below, employing '<> NULL' results in an empty result set.

SELECT * FROM prd_product WHERE description <> NULL;

Example 3 - Empty result set