PostgreSQL IN, Not IN with Examples

By : Juniper Willow Juniper Willow
Hours Updated

⚡ Smart Summary

PostgreSQL IN operator checks whether a value appears within a list or subquery inside a WHERE clause, replacing multiple OR conditions across SELECT, UPDATE, INSERT, and DELETE statements for cleaner, faster, and more readable filtering.

  • 📋 Syntax: value IN (list) returns true when the value matches any list item.
  • 🔤 Character Lists: Enclose text values in single quotes inside the parentheses.
  • 🔢 Numeric Lists: Compare numbers directly, without quotes, for exact matches.
  • 🚫 NOT IN: Returns rows whose value is absent from the supplied list.
  • ⚠️ NULL Trap: A NULL inside NOT IN can silently return zero rows.
  • 🤖 AI Queries: Assistants build IN clauses and flag NULL and performance risks.
Read More

PostgreSQL IN, NOT IN with Examples

What is PostgreSQL IN?

The IN operator is used in a WHERE clause that allows checking whether a value is present in a list of other values. The IN operation helps to reduce the need for multiple OR conditions in SELECT, UPDATE, INSERT, or DELETE statements.

Syntax

The IN operator takes the following syntax:

value IN (value_1, value_2, ...)

The value is the value that you are checking for in the list.

The value_1, value_2… are the list values.

If the value is found in the list, the operator will return a true.

The list can be a set of numbers or strings, or even the output result of a SELECT statement as shown below:

value IN (SELECT value FROM table-name);

The statement placed inside the parenthesis is known as a subquery.

PostgreSQL IN With Character

Let us demonstrate how you can use the IN operator with character values.

Consider the following table:

Employees:

PostgreSQL IN With Character

Let us run the following query against the above table:

SELECT * FROM Employees WHERE name IN ('James John', 'Mercy Bush', 'Kate Joel');

It returns the following:

PostgreSQL IN With Character result

We have a list of three names. We are searching for whether we can find any of these names in the name column of the Employees table. Kate Joel was matched to one of the table’s records, and its details were returned.

PostgreSQL IN With Numeric

Now, let us see how we can use the IN operator with numeric values.

Consider the Price table given below:

Price:

PostgreSQL IN With Numeric

We can run the following query against the table:

SELECT * FROM Price WHERE price IN (200, 308, 250, 550);

This returns the following:

PostgreSQL IN With Numeric result

We have created a list with 4 numeric values. We are checking whether we can match any of these values with the values contained in the price column of the Price table. Two values were matched, and their details were returned.

RELATED ARTICLES

Using the NOT Operator

The IN operator can be used together with the NOT operator. It returns the values that are not found in the specified column. We will use the Price table to demonstrate this.

SELECT * FROM Price WHERE price NOT IN (200, 400, 190, 230);

This will return the following:

PostgreSQL IN Using NOT operator

We have created a list with 4 numerical values. We are checking the price column of the Price table for values that are not part of the list. Two values, 250 and 300, were not found. Hence their details have been returned.

Using pgAdmin

Beyond the SQL shell, the same IN and NOT IN queries can be run visually through the pgAdmin interface.

With Character

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Using pgAdmin With Character

Step 3) Type the query in the query editor:

SELECT * FROM Employees WHERE name IN ('James John', 'Mercy Bush', 'Kate Joel');

Step 4) Click the Execute button.

Using pgAdmin With Character query

It should return the following:

Using pgAdmin With Character result

With Numeric

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Using pgAdmin With Numeric

Step 3) Type the query in the query editor:

SELECT * FROM Price WHERE price IN (200, 308, 250, 550);

Step 4) Click the Execute button.

Using pgAdmin With Numeric query

It should return the following:

Using pgAdmin With Numeric result

Using the NOT Operator

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Using NOT operator in pgAdmin

Step 3) Type the query in the query editor:

SELECT * FROM Price WHERE price NOT IN (200, 400, 190, 230);

Step 4) Click the Execute button.

Using NOT operator query in pgAdmin

It should return the following:

Using NOT operator result in pgAdmin

Download the Database used in this Tutorial

FAQs

In PostgreSQL, EXISTS often runs faster than IN on large subqueries because it stops at the first match. For small, constant lists, IN is simple and efficient. Test both with EXPLAIN ANALYZE on your own data.

NOT IN treats NULL as unknown, so if the list or subquery contains a NULL, the whole condition can evaluate to unknown and return zero rows. Filter out NULLs or use NOT EXISTS instead.

IN replaces long chains of OR conditions with one readable clause. WHERE id IN (1,2,3) equals WHERE id=1 OR id=2 OR id=3, but is shorter, clearer, and easier to maintain.

Yes. PostgreSQL treats x IN (a, b, c) as equivalent to x = ANY (ARRAY[a, b, c]). Both produce the same result and usually the same query plan.

AI assistants generate IN clauses from plain English, suggest the correct value list, and flag NULL risks in NOT IN. They also rewrite slow queries and add EXPLAIN hints for tuning.

Yes. AI Copilot tools detect repeated OR conditions on one column and refactor them into a single IN list, improving readability and reducing the chance of logic errors.

Yes. Place a SELECT inside the parentheses: WHERE id IN (SELECT id FROM orders). The inner query, called a subquery, supplies the list of values checked by IN.

For large tables, NOT EXISTS or a LEFT JOIN with IS NULL usually outperforms NOT IN because PostgreSQL builds an efficient anti-join. Also ensure the compared columns are indexed.

Summarize this post with: