PostgreSQL – NOT IN operator

Last Updated : 23 Oct, 2024

PostgreSQL NOT IN condition is a powerful tool for data retrieval in PostgreSQL by allowing users to filter out specific values from their query results. This condition is particularly useful when we want to exclude a defined set of values from a dataset by making our queries more efficient and targeted.

What is the NOT IN Operator?

The NOT IN operator in PostgreSQL allows us to filter out rows that match a list of specified values.
When used in a WHERE clause, it ensures that the returned results do not include any of the values within the specified list.

Syntax:

value NOT IN (value1, value2, ...)

Explanation: The syntax for using the NOT IN operator to return the matching values(except for the specified values) in contrast with the SELECT statement is as below:

value NOT IN (SELECT value FROM tbl_name)

Examples of PostgreSQL NOT IN operator

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link. Now, let’s look into a few examples of the NOT IN operator in PostgreSQL to better understand the concept.

Example 1: Excluding Specific Customer IDs in Rentals

Here we will query for all rentals where the ‘customer_id’ is not 10 or 12, from the ‘rental’ table in our sample database.

SELECT
    customer_id,
    rental_id,
    return_date
FROM
    rental
WHERE
    customer_id NOT IN (10, 12);

Output:

PostgreSQL NOT IN operator Example

Explanation: This query will return all rows from the ‘rental' table where the ‘customer_id' is neither 10 nor 12, displaying the ‘customer_id', ‘rental_id', and ‘return_date'.

Example 2: Excluding Specific Customer IDs in Customers Table

Here we will query for all customers where the ‘customer_id’ is not 10 or 12, from the ‘customer‘ table in our sample database.

SELECT
    customer_id,
    first_name,
    last_name
FROM
    customer
WHERE
    customer_id NOT IN (10, 12);

Output:

PostgreSQL NOT IN operator Example

Explanation: This query will return all rows from the customer table where the ‘customer_id' is neither 10 nor 12, displaying the ‘customer_id', ‘first_name', and ‘last_name'.

Important Points About PostgreSQL NOT IN Operator

The NOT IN operator can produce unexpected results when the list of values contains NULL.
If any value in the list is NULL, the entire NOT IN operation will return no rows because NULL comparisons result in an unknown outcome.
When dealing with subqueries, using NOT EXISTS can be more efficient and handle NULL values better than NOT IN Operator.
You can combine the NOT IN operator with other conditions in the WHERE clause to create complex filters.
PostgreSQL NOT IN Operator allows you to exclude specific values from your query results efficiently.

Conclusion

Overall, understanding the PostgreSQL NOT IN condition and its corresponding syntax enables users to perform precise data filtering within their queries. By excluding unwanted values, users can streamline their results and enhance data analysis capabilities.

FAQs

What happens if the NOT IN list contains NULL values?

If the list used with the NOT IN operator contains NULL values, the entire NOT IN operation will return no rows. This is because comparisons with NULL result in an unknown outcome, which leads PostgreSQL to disregard any results that might otherwise match the condition. Therefore, it’s important to handle NULL values carefully when using the NOT IN operator.

How does the NOT IN operator compare to the NOT EXISTS operator?

The NOT IN operator filters results based on specified values and can produce unexpected outcomes when NULLs are present. In contrast, the NOT EXISTS operator is generally more efficient and handles NULL values better, making it a preferred choice in subqueries. If your dataset includes NULLs, consider using NOT EXISTS for more reliable results.

Can I combine the NOT IN operator with other conditions in a query?

Yes, you can combine the NOT IN operator with other conditions in the WHERE clause to create more complex filters. For example, you can use it alongside AND or OR operators to refine your query results based on multiple criteria, enhancing the flexibility and power of your data retrieval.