PostgreSQL IN, Not IN with Examples

Details

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 conditions in SELECT, UPDATE, INSERT, or DELETE statements.

In this PostgreSQL Tutorial, you will learn the following:

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

With Character

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

Consider the following table:

Employees:

Let us run the following query against the above table: 

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

It return the following:

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. The Kate Joel was matched to one of the table's records, and its details were returned.

With Numeric

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

Consider the Price table given below:

Price:

We can run the following query against the table: 

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

This returns the following:

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.

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

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

Now let's see how the actions can be performed using pgAdmin.

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.

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.

It should return the following:

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.

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.

It should return the following:

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

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.

It should return the following:

Summary:

Download the Database used in this Tutorial

 

YOU MIGHT LIKE: