Home » Core Java » sql » SQL IN – SQL NOT IN

About Simran Koul

Simran has graduated as a Bachelor of Engineering in Computer Science from Chitkara University. She has undergone a 6-months long comprehensive industrial training at the reputed Centre for Development of Advanced Computing (C-DAC), where she worked on a project including the likes of Java, JSP, Servlets while the UI-UX through the pioneering HTML, CSS and JS. Her inquisitive nature and the seed of curiosity keeps her on the toes to find material to write about. Along with her interests in Software Development, she is an ardent reader and always ready-to-write writer.

SQL IN – SQL NOT IN

This article focuses on the functionality and usage of the SQL In and the SQL Not In operators. The first operator filters out rows from the multiple values mentioned in the Where clause. While SQL Not In operator excludes the rows with any of the values from the Where clause.

1. About SQL In and SQL Not In Operators

Let us first read about these operators individually.

1.1 What is SQL In operator ?

Let us say you want to include all rows with the names ‘Alex’, or ‘Ayaka’, or ‘Bandana’. You can do this by using multiple OR operators. SQL In operator is just like using multiple OR operators.

1.2 What is SQL Not In operator?

The SQL Not In operator works in an opposite fashion when compared to the SQL In operator. It excludes all the values given in the Where clause from the resultant set of rows.

2. Syntax

Now, we will look at the syntax for both the operators. The syntax for both the operators is quite similar.

2.1 SQL In operator syntax

 SELECT columnName(s) FROM tableName WHERE columnName IN (value1, value2, ... valueN);

2.2 SQL Not In operator syntax

SELECT columnNames(s) FROM tableName WHERE columnName NOT IN (value1, value2... valueN);

3. SQL In and SQL Not In Example

Moving ahead, let us now use both the operators in an example.

  1. We will start off by creating a database named – data – and a table named – information. The table information consists of three columns – id (of integer type), name (of varchar type), and country ( of varchar type).
SQL NOT IN - Creating database
Creating database – data – and table – information
SQL NOT IN - Inserting data
Inserting data into the table

2. Let us first use SQL In operator as an example.

The first example selects the two columns name, and country from the table information where the name is either ‘Marie’, or ‘Kumta’.

IN example 1

Now, the second example selects all the names which includes the countries for which the value of id is 1, 22 or 31.

SQL NOT IN - in 2
IN example 2

3. Now, let us use SQL Not In operator as an example.

The first example below selects id and country from the table which does not include names with an id greater than or equal to 22.

SQL NOT IN 1
NOT IN example 1

The example below selects id and country from the table which doesn’t include the names “Alex”.

NOT In example 2

4. Summary

This article discusses the usage and functionality of the SQL In and SQL Not In operators. SQL In operator focuses on including either of the values present in the where clause. While the SQL Not In operator focuses on excluding either of the values present in the where clause.

5. Download the source code

To implement the same example about SQL IN – SQL NOT IN on your local system, or online compiler, download the source code.

Download
You can download the full source code of this example here: SQL IN – SQL NOT IN

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments