---
description: 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. In Operation helps to reduce the need for multiple OR condi
title: PostgreSQL IN, Not IN with Examples
image: https://www.guru99.com/images/postgresql-in-not-in-with-examples.png
---

 

[Skip to content](#main) 

**⚡ 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 ](javascript:void%280%29;) 

![PostgreSQL IN, NOT IN with Examples](https://www.guru99.com/images/postgresql-in-not-in-with-examples.png)

## 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](https://www.guru99.com/postgresql-select-distinct.html) 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:**

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI1.png)

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:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI2.png)

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:**

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI3.png)

We can run the following query against the table:

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

This returns the following:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI4.png)

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

* [PostgreSQL Data Types: Numeric, Character, Byte ](https://www.guru99.com/postgresql-data-types.html "PostgreSQL Data Types: Numeric, Character, Byte")
* [PostgreSQL Array: Functions, Type, Example ](https://www.guru99.com/postgresql-array-functions.html "PostgreSQL Array: Functions, Type, Example")
* [PostgreSQL SUBSTRING() Function with Regex Example ](https://www.guru99.com/postgresql-substring.html "PostgreSQL SUBSTRING() Function with Regex Example")
* [PostgreSQL INSERT: Inserting Data into a Table ](https://www.guru99.com/postgresql-insert.html "PostgreSQL INSERT: Inserting Data into a Table")

## 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:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI5.png)

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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI6.png)

**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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI7.png)

It should return the following:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI8.png)

### 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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI9.png)

**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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI10.png)

It should return the following:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI11.png)

### 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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI12.png)

**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.

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI13.png)

It should return the following:

[](https://www.guru99.com/images/1/102319%5F0534%5FPostgreSQLI14.png)

[ Download the Database used in this Tutorial](https://drive.google.com/uc?export=download&id=1BqQSvYTmNz4hvZok7iYG9ha%5Fo48%5FVF2b)

## FAQs

🔄 Is IN or EXISTS faster in PostgreSQL?

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.

⚠️ Why can NOT IN return no rows when NULLs are present?

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.

🔁 Why use IN instead of many OR conditions?

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.

🧩 Is the IN operator the same as = ANY?

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.

🤖 How does AI help write PostgreSQL IN queries?

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.

🧾 Can an AI Copilot convert OR chains into an IN list?

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.

📥 Can I use a subquery inside an IN clause?

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.

🚀 How do I speed up NOT IN on large tables?

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:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-in-not-in-with-examples.png","url":"https://www.guru99.com/images/postgresql-in-not-in-with-examples.png","width":"700","height":"250","caption":"PostgreSQL IN, Not IN with Examples","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-in-not.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-in-not.html","name":"PostgreSQL IN, Not IN with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-in-not.html#webpage","url":"https://www.guru99.com/postgresql-in-not.html","name":"PostgreSQL IN, Not IN with Examples","dateModified":"2026-07-02T11:53:24+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-in-not-in-with-examples.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-in-not.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PostgreSQL","headline":"PostgreSQL IN, Not IN with Examples","description":"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. In Operation helps to reduce the need for multiple OR condi","keywords":"postgresql, sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"dateModified":"2026-07-02T11:53:24+05:30","image":{"@id":"https://www.guru99.com/images/postgresql-in-not-in-with-examples.png"},"copyrightYear":"2026","name":"PostgreSQL IN, Not IN with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Is IN or EXISTS faster in PostgreSQL?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Why can NOT IN return no rows when NULLs are present?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Why use IN instead of many OR conditions?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Is the IN operator the same as = ANY?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How does AI help write PostgreSQL IN queries?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can an AI Copilot convert OR chains into an IN list?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can I use a subquery inside an IN clause?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How do I speed up NOT IN on large tables?","acceptedAnswer":{"@type":"Answer","text":"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."}}]}],"@id":"https://www.guru99.com/postgresql-in-not.html#schema-1128738","isPartOf":{"@id":"https://www.guru99.com/postgresql-in-not.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-in-not.html#webpage"}}]}
```