postgresql
  1. postgresql-not-in
PreviousNext

NOT IN - (PostgreSQL Conditions)

In PostgreSQL, the NOT IN query condition is used to retrieve records that do not appear in a specified set of values or subquery. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the NOT IN condition in PostgreSQL.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column NOT IN (value1, value2, ...);

or

SELECT column1, column2, ...
FROM table_name
WHERE column NOT IN (SELECT ... FROM ...);
  • column1, column2,...: The columns to retrieve from the table.
  • table_name: The name of the table to retrieve records from.
  • column: The column to test against the set of values.
  • value1, value2,...: The set of values to test against.
  • SELECT ... FROM ...: A subquery to retrieve the set of values to test against.

Example

Let's use the NOT IN condition to retrieve all records from a table that do not match a set of values.

SELECT * FROM customers
WHERE country NOT IN ('USA', 'Canada', 'Mexico');

The output of this query would be all records from the customers table where the country column is not USA, Canada, or Mexico.

Explanation

In this example, we used the NOT IN condition to retrieve all records from the customers table where the country column is not in a list of specified countries. The countries specified were USA, Canada, and Mexico.

Use

The NOT IN condition is useful for filtering records from a table based on a set of values that do not appear in the specified set of values.

Important Points

  • The NOT IN condition is equivalent to using multiple != or <> operators.
  • The NOT IN condition may not be as efficient as using a NOT EXISTS or LEFT JOIN subquery.
  • The set of values used with the NOT IN condition can be a subquery.

Summary

In this tutorial, we discussed the NOT IN condition in PostgreSQL. We covered the syntax, example, output, explanation, use, important points, and summary of the NOT IN condition. With this knowledge, you can now use the NOT IN condition to filter records from a table based on a set of values that do not appear in the specified set of values.

InBetween
Published on: