Updated May 19, 2023
Definition of PostgreSQL NOT IN
PostgreSQL NOT IN condition is the combination of NOT and IN condition, NOT IN condition in PostgreSQL will return the values which were not found in the specified column from which column we are searching. We can use the NOT IN condition with Subquery in PostgreSQL and find the result excluding values that we used in the select query. We have also used the NOT operator with IN condition to retrieve the same result from the table.
Syntax:
select column_name1, column_name2, column_name3, …, column_nameN from name_of_table Where column_name NOT IN(value1, value2, value3, …, valueN);Parameters:
- Select: We have selected single or multiple columns while using the NOT IN condition in PostgreSQL. Using select statements, we have to retrieve results by using the NOT IN condition in PostgreSQL.
- From: This keyword is used to select the table name to use the NOT IN operator. We have used from keyword to specify the column name in PostgreSQL.
- Table name: We can define the table name from which we are retrieving data using the NOT IN condition in PostgreSQL. We have used any table to retrieve data using the NOT IN condition.
- Where condition: In PostgreSQL, we use the WHERE condition to specify column conditions using the NOT IN condition.
- NOT IN: This condition is defined as a retrieving value from the table using the NOT IN condition in PostgreSQL.
- Value 1 to value N: We have excluded this value from the output to display the specified result by using the NOT IN condition.
How NOT IN Condition Works in PostgreSQL?
- We have to use the NOT IN condition; basically, we must exclude the specified value result from the output.
- If we want to exclude specified values from the column output, then we have used the NOT IN condition.
- If suppose we have to exclude some student roll no from the student table, we have used the NOT IN condition in PostgreSQL.
- To execute the NOT condition with the select statement, we need to have select privileges on the table, or we need to have super user privileges to execute the NOT IN condition statement.
- Below is the example of a NOT IN condition with a select statement require select privileges on a table or super user privileges to execute the NOT IN condition with the select statement in PostgreSQL.
psql -U db_test -d testing
select * from stud1 where id NOT IN (1, 2, 3, 4, 5);
psql -U postgres -d testing
select * from stud1 where id NOT IN (1, 2, 3, 4, 5);
- In the above first example, we have used the user as db_test, this user doesn’t have privileges of select student table or super user, so it will issue an error while executing the select statement.
- In the second example, we have selected the stud1 table rows using the username as Postgres, after using this user, we have to select the data from the stud1 table.
- The NOT IN condition is commonly employed to retrieve data from a table by excluding specified values from a column.
- We can also use the NOT IN condition with not equal and the AND operator in PostgreSQL. We can also write the NOT IN query by using the not equal and the AND operator.
- Using not equal and the AND operator returns the same output as a return by the NOT IN condition.
Examples of PostgreSQL NOT IN
We are using the stud1 table to describe the example of the NOT IN condition in PostgreSQL.
- Below is the table and data description of the stud1 table.
\d+ stud1;
select * from stud1;
1. NOT IN condition by selecting a single column
- In the below example, we have used a single column with NOT in condition. We have selected the id column from the stud1 table.
select id from stud1 where id NOT IN (1, 2, 3, 4, 5);
2. NOT IN condition by selecting all column
- In the below example, we have used all columns with NOT in condition. We have selected all columns from the stud1 table.
select * from stud1 where id NOT IN (4, 5);
3. NOT IN condition by using not equal and the AND operator
- In the below example, we have used the NOT IN condition by using not equal and the AND operator. We have selected all columns from the stud1 table.
select * from stud1 where id <> 12 AND id <> 10 AND id <> 10;
Advantages
Given below are the advantages mentioned:
- NOT IN condition excludes the specific value from a table.
- We can use equal to and the AND operator for the NOT IN condition.
- With the NOT IN condition, we can choose one or more columns.
- We can also use the NOT IN condition with Subquery.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL NOT IN” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
