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.
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.
SELECT col_1, col_2, col_3 ...
WHERE col_x IN (val_1, val_2, ...);
SELECT col_1, col_2, col_3 ...
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.
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.
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.
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!