How to Use IN in SQL

In this SQL article, you will learn how to use IN keyword in the queries of the SQL database.

What is IN keyword in SQL?

The IN is a logical operator in the Structured Query Language that allows the database users to define more than one value in the WHERE clause.

The WHERE clause with IN operator shows those records in the result which are matched with the given set of values. We can also specify the subquery in the parenthesis of IN operator.

We can use the IN operator with the INSERT, SELECT, UPDATE, and DELETE queries in the SQL database.

The IN operator in SQL replaces the process of multiple OR conditions in queries.

Syntax of IN operator:

If you want to use the IN operator in the SQL statements, you have to follow the steps given below in the same sequence:

1. Create a database in SQL.
2. Create the new SQL table.
3. Insert the data in the table.
4. View the Inserted data.
5. Use the SQL IN operator to display the table's data.

Now, we are going to explain each step one by one in brief with the best SQL example:

Step 1: Create a simple new database

The first step is to create a new database in Structured Query Language.

The following CREATE statement creates the new Mechanical_College Database in SQL server:

Step 2: Create a new table

Now, use the following SQL syntax that helps in creating the new table in the database:

The following CREATE statement creates the Faculty_Info table in the Mechanical_College Database:

Step 3: Insert the Data into the table

The following INSERT queries insert the records of Faculties in the Faculty_Info table:

Step 4: View the Inserted Data

The following SELECT statement displays the data of the Faculty_Info table:

Step 5: Use the IN Operator to view the data of the Faculty_Info table in different ways

The following query uses numeric values with IN operator:

This query shows only the record of those Faculties whose salary is passed in the IN operator of WHERE clause.

Output:

The following query uses text or character values with IN logical operator:

This query shows only the record of those Faculties whose city is included in the parenthesis of IN operator in the WHERE clause.

Output:

The following query uses DATEformat with IN logical operator:

This query shows only those records of Faculties whose joining date is passed in the IN operator of the WHERE clause.

Output:

The following query uses SQL UPDATE command with IN logical operator:

This query updates the salary of those Faculties whose Dept_Id is passed in the IN operator of the WHERE clause.

To check the result of the above query, type the following SELECT query in SQL:

SQL IN Operator with Subquery

In Structured Query Language, we can also use the subquery with the IN logical operator.

The syntax of IN operator with subquery is given below:

If you want to understand IN operator with subquery, you have to create the two different tables in the Structured Query Language using the CREATE statement.

The following query creates the Faculty_Info table in the Database:

The following query creates the Department_Info table in the Database:

The following INSERT queries insert the records of Faculties in the Faculty_Info table:

The following INSERT queries insert the records of departments in the Department_Info table:

The following SELECT statement displays the data of the Faculty_Info table:

The following query shows the records of the department from the Department_Info table:

The following query uses IN operator with a subquery:

This query displays the record of those Faculties whose Dept_ID of Faculty_Info table is matched with the Dept_Id of Department_Info table.

Output:

What is NOT IN in SQL?

The NOT IN is another operator in Structured Query Language, which is just opposite to the SQL IN operator. It allows you to access those values from the table, which does not pass in the parenthesis of IN operator.

The NOT IN operator can be used in the INSERT, UPDATE, SELECT, and DELETE SQL queries.

Syntax of NOT IN operator:

If you want to use the NOT IN operator in the statements of SQL, you have to follow the given steps in the same sequence:

1. Create a database in the SQL system.
2. Create the new table in the database.
3. Insert the data in the table
4. View the Inserted data
5. Use the NOT IN operator to view the data.

Now, we are going to explain each step one by one in brief with the best SQL example:

Step 1: Create the Simple new database

The following query creates the new Civil_Industry Database in SQL server:

Step 2: Create the New table

The following query creates the Worker_Info table in theCivil_Industry Database:

Step 3: Insert the Values

The following INSERT queries insert the records of workers in the Worker_Info table:

Step 4: View the Table's Data

The following query displays the data of the Worker_Info table.

Step 4: Use NOT IN operator

The following query uses the NOT IN operator with numeric data:

This SELECT query shows all those workers in the output whose salary is not passed in the NOT IN operator.

The result of the above statement is shown in the following table:

The following query uses NOT IN logical operator with character or text values:

This query shows the record of all those Workers whose address is not passed in the NOT IN operator.

Output: