In this PostgreSQL Tutorial, you will learn the following:
What is PostgreSQL In ?
Syntax
With Character
With Numeric
Using NOT operator
Using pgAdmin
Syntax
The IN operator takes the following syntax: 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: 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: 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: 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. 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
It should return the following:
Summary:
The IN operator is used with the WHERE operator. It allows checking whether a particular value is present in a specific table. The IN operator helps in reducing the need for multiple OR operators in SELECT, UPDATE, INSERT, or DELETE statements. When creating a character list to check for the presence of a value, each value in the list should be enclosed within single quotes. The IN operator can also be used with numeric values. When the IN operator is used together with the NOT operator, it returns all values that are not found in the specified column.
Download the Database used in this Tutorial
