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.
- 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).
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’.
Now, the second example selects all the names which includes the countries for which the value of id is 1, 22 or 31.
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.
The example below selects id and country from the table which doesn’t include the names “Alex”.
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
