- 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);
- 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:
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.
SELECT id, emp_name, emp_age, emp_salary FROM sto_employees WHERE emp_name IN ('Ben','Jimmy','Jay','Imran');
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:
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');
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
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.
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 resultset with the HAVING clause and IN operator returned only two rows.