Oracle

The IS NULL operator in Oracle is used in Data Query Language (DQL) to check whether a column contains a NULL value. A NULL value in SQL represents missing or unknown data. The IS NULL condition helps you find rows where a column has no value assigned, and it's often used in SELECT statements to filter data. Using this operator ensures that queries handle missing information properly when retrieving results from a table.

Here’s a breakdown of how the IS NULL operator works, followed by an example using a library-related table.

Understanding the IS NULL Operator

1. Basic Syntax
   The basic syntax of the IS NULL operator in Oracle is:

   SELECT column_name(s)
   FROM table_name
   WHERE column_name IS NULL;
   

2. Example Query
   Suppose you have a table books in your library system, and you want to find all books that don't have an assigned publication year (publication_year is NULL):

   SELECT book_id, title, author_id
   FROM books
   WHERE publication_year IS NULL;
   

   • This query will return rows where the publication_year column has no value (i.e., it's NULL).

3. Checking Multiple Columns for NULL
   You can use the IS NULL operator with multiple columns in your WHERE clause:

   SELECT book_id, title
   FROM books
   WHERE author_id IS NULL OR publication_year IS NULL;
   

   • This will return rows where either author_id or publication_year is NULL.

4. Combining with Other Conditions
   The IS NULL operator can be combined with other logical conditions like AND and OR. For example:

   SELECT membership_id, member_name
   FROM membership
   WHERE membership_type IS NULL AND join_date > '2023-01-01';
   

   • This query finds memberships where the membership_type is missing and the join_date is after January 1, 2023.

5. Best Practices

   • Always use IS NULL (not = NULL) to check for NULL values.
   • Ensure you handle NULL values appropriately in queries to avoid incorrect results, especially in calculations or comparisons.
   • When working with NULL values, consider how they affect query performance, particularly with indexes and filtering.

By understanding how to use the IS NULL operator effectively, you can retrieve data that is missing or incomplete, improving the accuracy of your queries.

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

SELECT *
FROM act_order
WHERE shipped_date IS NULL;

SELECT *
FROM org_employee
WHERE date_of_birth IS NULL;

Example 1:

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

Example 1 - Raw data from employee table

Example 1 - Query

SELECT *
FROM org_employee
WHERE date_of_birth IS 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 NULL operator on string data. The query retrieves all rows from the products table where the description is null.

Example 2 - Raw data from product table

Example 2 - Query

SELECT *
FROM prd_product
WHERE description IS 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 NULL'. As demonstrated below, employing '= NULL' results in an empty result set.

SELECT *
FROM prd_product
WHERE description = NULL;

Example 3 - Empty result set