SQL IN Operator

This is a detailed tutorial of SQL IN Operator. Check multiple values in the WHERE clause using the IN operator instead of using multiple OR conditions.

Use of SQL IN Operator

SQL IN Operator is used to specify multiple values in the SQL WHERE Clause. If you need to check a column value for multiple values, you either need to specify multiple conditions in the WHERE clause using the OR operator or simply you can use the IN operator and write as many as possible values in the parenthesis to check for. Therefore, IN is a shorthand operator for multiple OR conditions.

The IN operator can also be used to check-in WHERE clause the values obtained from the resultset of another query. So, it eliminates the need for running several different queries to first obtain the resultset of one query and use its values in another query. You can do the same in a single query with this operator.

IN Syntax

There are two different ways in which you make use of an IN operator as specified in the above section. So, there are two different possible syntaxes as well.

Or

In the first syntax, we’re selecting columns named col_1, col_2, col_3 and so on from table_name where the value of col_x is equal to any one of the values written in parenthesis (val_1, val_2, ...) after the IN operator.

In the second syntax, we’re similarly selecting the columns but instead of putting values inside the parenthesis after the IN operator, we’re directly writing in a SQL SELECT Statement that will fetch the values for us.

IN Operator Examples

Several examples for IN Operator are given below. For demonstration, we’ll run the queries in our demo database table named employees. The following screenshot gives a quick look at the table structure and data.

Basic Usage For Checking Multiple Values

The following query will simply return the list of records that has hire_date matching with any of the values specified in the parathesis after the IN operator.

NOT IN Operator

We’ll modify the query use in the above example. Instead of IN, we’ll use the keyword NOT IN. This will basically select all the records for which the column hire_date does not have the values specified in the parenthesis. Find more about NOT operator here.

IN Operator With SELECT Statement

Instead of manually writing values in the parenthesis, you can also directly put in a SELECT statement that will automatically fill in the values obtained in its resultset. The following query fetches all the records of the employees having their emp_no fetched from the second query.

So, basically there another database table senior_employees that contains the emp_no of senior employees and the SELECT query written in parenthesis will fetch those emp_no and will put the values in there for the IN statement to check along with the WHERE clause.

To run this query, I created another table senior_employees and added column emp_no with 4 values. Therefore, this query fetches four different records.

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Helpful Associated Guide.

• PHP MySQL CRUD Tutorial For Beginners
• Rename A Column In MySQL
• Import SQL File (MySQL Command Line & GUI)

Also, don’t forget to Subscribe to WTMatter!