SQL IN Operator

SQL IN operator enables you to provide 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,

SELECT * FROM tbl_employees
WHERE ID = 5 OR ID = 15 OR ID = 20;

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

SELECT * FROM tbl_employees
WHERE ID IN(5, 15, 20);

Providing the list of values in the IN operator simplifies using multiple OR conditions.

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

SELECT * FROM tbl_employees
WHERE ID IN (SELECT ID FROM some_table);

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

A simple example of using the IN operator

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

Query:

SELECT id, emp_name, emp_age, emp_salary

FROM sto_employees

WHERE id IN (2,5,7,8,10);

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

The equivalent query while using the OR operator:

SELECT id, emp_name, emp_age, emp_salary

FROM sto_employees

WHERE id = 2 OR id = 5 OR id = 7 OR ID = 8 OR ID = 10;

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:

SELECT id, emp_name, emp_age, emp_salary

FROM sto_employees

WHERE emp_name IN ('Ben','Jimmy','Jay','Imran');

The resultset:

The example of using the date column

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

Query with date & IN:

SELECT id, emp_name, emp_age, emp_salary, join_date

FROM sto_employees

WHERE join_date IN ('2016-01-01','2015-03-15','2017-08-23','2017-06-15');

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 the 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 by 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 the OR operator and the second used the ‘AND’ operator.

To see 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:

SELECT id, emp_name, emp_age, emp_salary FROM sto_employees

WHERE id IN (2,5,7,8,10)

OR

emp_name IN ('Ben','Mike','Jay','Jellani');

Query 2:

SELECT id, emp_name, emp_age, emp_salary FROM sto_employees

WHERE id IN (2,5,7,8,10)

AND

emp_name IN ('Ben','Mike','Jay','Jellani');

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.

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

SELECT id, emp_name, emp_age, emp_salary

FROM sto_employees

WHERE id NOT IN (2,5,7,8,10);

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 the 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 duplicates, so I used the DISTINCT clause. All the emp_id values can be ID field from the sto_employees table.

The IN with the subquery:

SELECT DISTINCT emp_id FROM sto_emp_salary_paid;



SELECT id, emp_name, emp_age, emp_salary FROM sto_employees

WHERE id IN (SELECT DISTINCT emp_id FROM sto_emp_salary_paid);

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

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.

Note: The HAVING clause is used to filter the aggregated result (normally with the 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:

SELECT SUM(emp_sal_paid) As "Total Paid", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name

With HAVING and IN query

SELECT SUM(emp_sal_paid) As "Total Paid", emp_name As "Employee Name"

FROM sto_emp_salary_paid

GROUP BY emp_name

HAVING SUM(emp_sal_paid) IN (4500.50, 9001.33);

The resultsets:

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