SQL IN – SQL NOT IN

Filed Under: SQL
Sql In Not In Operators

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages