Hit enter after type your search item
Home / SQL Tutorials / The SQL IN operator

The SQL IN operator

The IN operator enables you providing a list of values to be tested in the WHERE clause (generally) with SELECT, DELETE, or UPDATE statements.

In the WHERE clause, you may specify a condition for retrieving records. For example,

WHERE ID = 5 OR ID = 15 OR ID = 20;

By using IN operator, you may provide all these values as simple as this:

WHERE ID IN(5, 15, 20);

As providing the list of values in the IN operator, it simplifies using the multiple OR conditions.

You may also use a subquery for providing the list of values in the IN operator. For example:

WHERE ID IN (SELECT ID FROM some_table);

This will include all IDs from the specified table for the IN condition.

The section below shows you examples of using IN operator.

A simple example of using IN operator

The example below returns all records for employees from sto_employees table whom IDs match in the given list of IN operator. See the query and resultset:

Query:


SQL IN operator

The resultset shows only those records whom given ids are found in the specified table.

The equivalent query as using the OR operator is:


The same resultset is returned as you execute this query.

Using the text values in the IN operator

The strings or text values are enclosed in single quotes as using the IN operator. In this query, we will fetch the records based on employee names:

The query:


The resultset:

SQL IN text

The example of using the date column

Just like text values, the date values are also enclosed in the single quotes as using the IN operator. For this query, the records are retrieved based on join_date of the employees:

Query with date & IN:


SQL IN dates

The query returned only three records, though we provided four values in the IN operator. The last date record is not found in the table and even then query returned matched records. That shows, the IN operator is the shorthand of multiple OR conditions.

In the case of AND operator, all conditions have to be true.

Using multiple IN conditions example

You may use multiple IN operators in a single query. The multiple IN conditions can be combined be using β€˜AND’ and IN operators. You may also use NOT with the IN operator.

The following example uses almost the same queries except the first query used OR operator and the second used β€˜AND’ operator.

For seeing the difference, the graphic below shows complete (dummy) data in the table along with results based on these two queries:

Two queries with multiple IN conditions:

Query 1:


Query 2:

SQL IN multiple

The example of NOT IN operator

The NOT In operator works the opposite of the IN operator. So, provide the values in the NOT IN operator that you do not want to return results for.

For demonstrating that, I am using the same query as in the first example with the exception that I used NOT IN this time:


SQL IN NOT

You can see, none of the ids exist in the resultset that is provided in the IN condition.

The example of using a subquery in the IN condition

As mentioned earlier, the list of values for IN operator can also be based on a sub-query. See the example query below where I will get the employee IDs from another table that supposedly stores the salaries paid to each employee.

The sto_emp_salary_paid table contains emp_id that may be duplicate, so I used DISTINCT clause. All the emp_id values can be ID field from the sto_employees table.

The IN withthe subquery:


The resultset for subquery that is executed separately and within the IN operator:

SQL IN subquery

Can IN operator be used with the HAVING clause?

Earlier, I mentioned that the IN operator is used with the WHERE clause generally. However, you may also use it with the HAVING clause. The HAVING clause is used to filter the aggregated result (normally with GROUP BY clause).

The IN operator can be used just like in the WHERE clause. That is, enclose the values in the parenthesis and separate by commas.

See the demonstration below where I executed two queries.

The first query is a simple GROUP BY for calculating the total amount paid to each employee in the sto_emp_salary_paid table. The second query uses the HAVING clause with the IN operator for filtering the rows. Have a look:


With HAVING and IN query

The resultsets:

SQL IN HAVING

The resultset with HAVING clause and IN operator returned only two rows.

This div height required for enabling the sticky sidebar