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.
Table of Contents
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.
1 2 3 |
SELECT col_1, col_2, col_3 ... FROM table_name WHERE col_x IN (val_1, val_2, ...); |
Or
1 2 3 |
SELECT col_1, col_2, col_3 ... FROM table_name WHERE col_x IN (SELECT STATEMENT); |
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.
1 |
SELECT * FROM employees WHERE hire_date IN ('1986-08-28','1986-08-29','1986-08-30') |
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.
1 |
SELECT * FROM employees WHERE hire_date NOT IN ('1986-08-28','1986-08-29','1986-08-30') |
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.
1 |
SELECT * FROM employees WHERE emp_no IN (SELECT emp_no FROM senior_employees) |
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!