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.

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.

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

Last updated on Feb. 27th, 2022

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.
Notify of

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

Inline Feedbacks
View all comments
Back to top button