PostgreSQL

In PostgreSQL, DQL (Data Query Language) is used to retrieve data from the database. One of the important conditions in DQL is checking for null values using the IS NULL operator. In SQL, a null value represents missing or unknown data. The IS NULL operator helps in identifying records where a specific column has no value (i.e., it contains NULL). Conversely, you can also use IS NOT NULL to find records where the column contains a value. Below is an explanation with a code sample, using common banking-related tables like customers and accounts.

Example:

SELECT customer_id, customer_name
FROM customers
WHERE email IS NULL;

This query retrieves the customer_id and customer_name of customers who don't have an email address.

Key points about IS NULL in PostgreSQL:

1. When to useIS NULL:
   • NULL values are used when the actual value for a column is unknown or missing.
   • Use IS NULL in the WHERE clause when you want to filter records that contain null values for a specific column.
2. HowIS NULLworks:
   • IS NULL does not compare values but checks if the column is assigned a NULL.
   • Syntax: column_name IS NULL
     • Example:

     SELECT account_id, balance
     FROM accounts
     WHERE last_transaction_date IS NULL;
     

3. Common scenarios forIS NULL:
   • Finding incomplete data, such as customers without an email or accounts without recent transactions.
   • Example:

   SELECT transaction_id, amount
   FROM transactions
   WHERE description IS NULL;
   

4. IS NOT NULL:
   • To find records where the column is not NULL, use IS NOT NULL.
   • Syntax: column_name IS NOT NULL
     • Example:

     SELECT account_id, account_type
     FROM accounts
     WHERE balance IS NOT NULL;
     

5. Important notes:
   • NULL is not the same as an empty string ('') or zero (0); it represents an absence of any value.
   • SQL's = (equals) operator cannot be used to compare with NULL. Instead, you must use IS NULL or IS NOT NULL.

Using IS NULL is a fundamental part of querying data in PostgreSQL and helps ensure you can handle missing data properly in your database operations.

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

TEST CODE

In PostgreSQL, you can use the IS NULL operator to filter rows based on a NULL value in the shipped_date column. This query retrieves all orders that have not been shipped yet:

SELECT *
FROM act_order
WHERE shipped_date IS NULL;

Try it now

In PostgreSQL, you can use the IS NULL operator to filter rows based on a NULL value in the date_of_birth column. This query retrieves all employees who have not provided their date of birth:

SELECT *
FROM org_employee
WHERE date_of_birth IS NULL;

Try it now

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