Retrieving Data Without NULL in a Certain Column

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. Null values in a database field can have a negative effect on data analysis, query results, and application logic.

In PostgreSQL, NULL is used to denote missing values or unknown states, which might at times complicate the way of queries and analysis. The handling of NULL values is particularly important when working with PostgreSQL for null values retrieving even from specific columns should not be included. This article explores various methods to achieve this objective.

IS NOT NULL Operator

The IS NOT NULL Operator in PostgreSQL is a simple and powerful way to retrieve data without the NULL values in a specified column. Using “IS NOT NULL” in the SELECT statement’s WHERE clause allows you to remove all rows containing NULL value in the specified column.

Syntax:

SELECT column1, column2
FROM table_name
WHERE column_name IS NOT NULL;

In this query, only rows where the ‘column_name‘ is not NULL will be included in the result set. This method ensures that only non-NULL values are retrieved from the specified column.

Example Queries on Retrieving Data without NULL values in a Certain Column

First, let’s create a table named workers and insert some data into it:

Create a table:

CREATE TABLE workers  (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

Insert some data:

INSERT INTO workers (name, age, email) VALUES
    ('Minal', 30, 'minal@gmail.com'),
    ('Priyanshi', NULL, 'priyanshi@gmail.com'),
    ('Vardhana', 45, NULL),
    ('Mahi', 25, 'mahi@gmail.com');

Output:

To see the content of the table you can execute the below command:

SELECT * FROM workers;

Now we have some data in our table, let’s proceed with example queries on retrieving data without NULL values in the ‘age’ column.

Workers Table

Example 1: Retrieve all rows where ‘age’ is not NULL.

Query:

SELECT * FROM workers
WHERE age IS NOT NULL;

Explanation:

SELECT: This part of the query specifies that you want to select all columns (*) from the table.
FROM workers: Here, you’re specifying the table from which you want to retrieve data. In this case, it’s the “workers” table.
WHERE age IS NOT NULL: This is the filtering condition. The WHERE clause is used to specify conditions that must be met for a row to be included in the result set. Here, you’re filtering rows where the “age” column is not NULL.

Output:

All rows where ‘age’ is not NULL.

Example 2: Retrieve names and emails of workers with non-NULL ‘age’.

SELECT name, email FROM workers
WHERE age IS NOT NULL;

Explanation:

SELECT: This part of the query specifies that you want to select name, email column from the table.
FROM workers: Here, you’re specifying the table from which you want to retrieve data. In this case, it’s the “workers” table.
WHERE age IS NOT NULL: This is the filtering condition. The WHERE clause is used to specify conditions that must be met for a row to be included in the result set. Here, you’re filtering rows where the “age” column is not NULL.

Output:

Names and Emails of workers with not-NULL ‘age’.

Example 3: Retrieve names and ages of workers where age and email both are NOT NULL and age is greater than 30.

SELECT name, age FROM workers
WHERE age IS NOT NULL
AND email IS NOT NULL
AND age >= 30;

Explanation:

SELECT: This part of the query specifies that you want to select name and age column from the table.
FROM workers: Here, you’re specifying the table from which you want to retrieve data. In this case, it’s the “workers” table.
WHERE age IS NOT NULL: Here, you’re filtering rows where the “age” column is not NULL and the age is greater than or equal to 30.

Output:

Name and Age of Worker

Conclusion

Extracting data without NULL values in a specific column in PostgreSQL reduces error in data analysis and application performance. Through using the IS NOT NULL operator and combining it with other conditions, if necessary, programmers will be able to filter NULL values from query results without affecting the actual data storage. Through studying and mastering these techniques, developers gain the ability to write detailed and straight to the point queries in PostgreSQL.

Article Tags :

Databases

PostgreSQL