EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL NOT IN

PostgreSQL NOT IN

Priya Pedamkar
Article byPriya Pedamkar

Updated May 19, 2023

PostgreSQL NOT IN

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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);

PostgreSQL NOT IN-1.1

  • 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;

PostgreSQL NOT IN-1.2

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);

PostgreSQL NOT IN-1.3...

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);

Output-1.3

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;

Output-1.4

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.

  1. PostgreSQL Datetime
  2. Log Queries PostgreSQL
  3. PostgreSQL Wal
  4. PostgreSQL Compare Strings
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more