PL/SQL IS NULL Operator

The IS NULL operator is a fundamental tool in PL/SQL used to determine the presence of NULL values in database columns. Understanding how to effectively use the IS NULL operator is crucial for database management, as it allows developers and analysts to identify and handle records with missing or undefined information. In this article, we will learn about the PL/SQL IS NULL operator with its syntax and practical applications with examples.

PL/SQL IS NULL Operator

• The IS NULL operator in PL/SQL is used to check whether a given value or expression is NULL.
  
• It indicates the absence of a value or that a value is undefined.
  
• This operator is crucial in SQL and PL/SQL as it helps identify records with missing data in database columns.

Syntax:

column_name IS NULL

Explanation:

• column_name: This represents the name of the column or expression you want to check for NULL values.
  
• IS NULL: This is the keyword used to check if the specified column or expression is NULL.

Examples of PL/SQL IS NULL Operator

Setting up an Environment

To understand the PL/SQL IS NULL Operator we will use the below tables for better understanding.

employees Table:

Explanation:

The output table shows the data inserted into the employees table. The NULL values in phone_number, department_id, and salary columns highlight the absence of data for these specific fields in the corresponding records.

Projects Table:

This query creates the projects table with columns to store project-related information, including project ID, name, start and end dates, and the ID of the project manager. The project_id serves as the primary key, ensuring each project has a unique identifier.

Output:

Explanation:

The output table displays the project data as inserted into the projects table. The NULL values in the end_date column indicate that none of these projects have been completed, and the NULL value in project_manager_id for the Gamma Project shows that it currently lacks an assigned manager.

Example 1: Selecting Employees with a NULL Phone Number

Let's write a query to retrieve the details of employees who do not have a phone number listed in the employees table. The query selects the first name, last name, and email of employees whose phone_number is NULL. This is particularly useful for identifying missing contact information in the database.

Query:

SELECT first_name, last_name, email
FROM employees
WHERE phone_number IS NULL;

Output:

Explanation:

• This query selects employees who do not have a phone number listed.
  
• The output shows that 'Jane Smith' does not have a phone number in the database.

Example 2: Finding Projects with No End Date

Let's write a query to identify ongoing projects by checking if their end_date is NULL. This query retrieves the names and start dates of projects that do not have an end date specified, indicating that these projects are still active.

Query:

SELECT project_name, start_date
FROM projects
WHERE end_date IS NULL;

Output:

Explanation:

• This query retrieves projects that do not have an end date specified, indicating they are ongoing.
  
• The output includes 'Alpha Project' and 'Gamma Project'.

Example 3: Identifying Employees Without a Department

Let's write a query to find all employees who are not currently assigned to any department by checking if their department_id is NULL

This query fetches the first and last names of employees who are not assigned to any department (i.e., their department_id is NULL).

Query:

SELECT first_name, last_name
FROM employees
WHERE department_id IS NULL;

Output:

Explanation:

• This query finds employees who are not assigned to any department.
  
• The result shows that 'Emily Johnson' does not have a department ID, indicating she is not assigned to any department.

Conclusion

Overall, the IS NULL operator in PL/SQL serves as an essential mechanism for detecting NULL values within database records. By using this operator, developers can identify missing data in critical fields, enabling better data integrity and decision-making processes. Through the examples provided, such as retrieving employees without phone numbers, identifying ongoing projects, and locating employees without departmental assignments, it is clear that the IS NULL operator is indispensable for maintaining accurate and comprehensive databases. .