Problems with NOT IN operator in pSQL

In contrast to the IN operator, the pSQL NOT IN operator does not work as we expect, but PostgreSQL does not recommend not using this operator.

Using the NOT IN operator with any sort of query is simply not a good idea since it behaves strangely with NULL values.

SELECT * FROM uber.user WHERE isPaidUser NOT IN (1, NULL);

Whatever values appear in the isPaidUser column, this query will never return any rows. This is due to how NULL values are interpreted when combined with an IN condition. The value of isPaidUser IN (1, NULL) is TRUE if isPaidUser is 1, and FALSE otherwise. Not(NULL) is still NULL if you apply a NOT to that. The query will never return any rows because NULL is not TRUE.

Syntax

In order to find a boolean value against a list of values which returns a NOT IN operator with a WHERE clause, the syntax of the syntax depends on the match.

value NOT IN (val1, val2, …)

The syntax for an operator to return the matching values in opposite with the SELECT is:

value NOT IN (SELECT value FROM tbl_name);

Lets go through this example to understand this better.

There is a shop that provides vehicle rental services. Now we are going to query all the available rentals except on the return date 6 and 5 from the database. In the below example Customer name is mentioned as customer_name, Vehicle number as veh_no and the return date as return date.

SELECT
    customer_name,
    veh_no,
    return_date
FROM
    rental
WHERE
    customer_name NOT IN (5, 6);

In output it will exclude the customer name that is stored in the date 5 and 6.

NOT IN operator could be used only with a static list with constant values. It can also be used to compare a constant small list of values.

Further Reading

1. [SOLVED] psql: FATAL database 'root' does not exist error
2. [SOLVED] psql: Could not connect to server: No such file or directory
3. OSI Model: Layers and its functions