Tutorial

SQL IN - SQL NOT IN

Published on August 3, 2022
Default avatar

By Pankaj

SQL IN - SQL NOT IN

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

SQL IN operator is used along with WHERE clause for providing multiple values as part of the WHERE clause.

1. SQL IN

SQL IN operator is almost like having multiple OR operators for the same column. Let’s discuss in detail about the SQL IN operator. There are two ways to define IN operator. We will discuss both the ways in details below.

1.1) Multiple values as part of IN

Syntax:

SELECT Column(s) FROM table_name WHERE column IN (value1, value2, ... valueN);

Using the above-mentioned syntax, we can define multiple values as part of IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the following Student table for example purpose.

RollNo StudentName StudentGender StudentAge StudentPercent
1 George M 14 85
2 Monica F 12 88
3 Jessica F 13 84
4 Tom M 11 78

Scenario: Get the percentage of students whose age is 12 or 13. Query:

SELECT StudentPercent FROM Student WHERE StudentAge IN ('12', '13');

Output:

StudentPercent
88
84

1.2) Select Query as part of IN

Syntax:

SELECT Column(s) FROM table_name WHERE column IN (SELECT Statement);

Using the above-mentioned syntax, we can use SQL SELECT statement for providing values as part of the IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the following Product and Supplier table for example purpose. PRODUCT Table

ProductId ProductName ProductPrice
1 Cookie 10
2 Cheese 11
3 Chocolate 15
4 Jam 20

SUPPLIER Table

ProductId ProductName SupplierName
1 Cookie ABC
2 Cheese XYZ
3 Chocolate ABC
4 Jam XDE

Scenario: Get the price of the product where the supplier is ABC. Query:

SELECT ProductPrice FROM Product WHERE ProductName IN 
    (SELECT ProductName FROM Supplier WHERE SupplierName = "ABC");

Output:

ProductPrice
10
15

1.3) SQL Nested IN

We can also use IN inside other IN operator. To understand it better, let’s consider the below-mentioned scenario. Scenario: Get the price of the product where the supplier is ABC and XDE. Query:

SELECT ProductPrice FROM Product WHERE ProductName IN 
    (SELECT ProductName FROM Supplier WHERE SupplierName IN ( "ABC", "XDE" ));

Output

ProductPrice
10
15
20

2. SQL NOT IN

SQL NOT IN operator is used to filter the result if the values that are mentioned as part of the IN operator is not satisfied. Let’s discuss in detail about SQL NOT IN operator. Syntax:

SELECT Column(s) FROM table_name WHERE Column NOT IN (value1, value2... valueN);

In the syntax above the values that are not satisfied as part of the IN clause will be considered for the result. Let’s consider the earlier defined Student table for example purpose. Scenario: Get the percentage of students whose age is not in 12 or 13. Query

SELECT StudentPercent FROM Student WHERE StudentAge NOT IN ('12', '13');

Output:

StudentPercent
85
78

2.1) Select Query as part of SQL NOT IN

Syntax:

SELECT Column(s) FROM table_name WHERE column NOT IN (SELECT Statement);

Using the above-mentioned syntax, we can use SELECT statement for providing values as part of the IN operator. We will understand the above-mentioned syntax in more detail through some examples. Let’s consider the earlier defined Product and Supplier table for example purpose. Scenario: Get the price of the product where the supplier is not ABC. Query:

SELECT ProductPrice FROM Product WHERE ProductName NOT IN 
    (SELECT ProductName FROM Supplier WHERE SupplierName = "ABC");

That’s all for SQL IN and SQL NOT IN operator examples.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel