MySQL subqueries may return values of a column with or without WHERE clause. To perform comparison with the set of the column values ANY, IN and SOME comparison operators can be used.
MySQL Subquery using ANY, IN and SOME
WHERE comparison_field/operand comparison_operator ANY (subquery)
WHERE comparison_field/operand IN (subquery)
WHERE comparison_field/operand comparison_operator SOME (subquery)
comparison_operator is one of these =, >, <, >=, <=, <> or !=
ANY, IN and SOME
ANY keyword is used with a comparison operator. It is used to compare an operand or column value with a set of values returned by a subquery. It returns all those rows whose value returns TRUE for the comparison operator.
IN keyword is not used with a comparison operator. The query returns all the rows that match any one value among the values returned by the subquery.
SOME keyword is used with a comparison operator. It can be used as an alternative keyword to ANY. The main query returns all the rows that have a TRUE relationship of comparison operator with any value among the values returned by the subquery.
Example of MySQL subquery with ANY
This query returns the details of all the employees whose managers earn salary more than 10000.
SELECT first_name, last_name, Manager_id, Job_id, salary FROM employees WHERE manager_id=ANY(SELECT employee_id FROM employees WHERE salary>10000);
The inner query returns employee IDs of all the employees who earn salary more than 10000. This is the set of all manager IDs with salary more than 10000 (A manager is also an employee with an employee id). The outer query returns all the rows whose manager ID matches any of the employee IDs returned by inner query.
Example of MySQL subquery with IN
This query returns the details of all the employees who work in departments having job IDs containing strings ‘ST’ or ‘MAN’.
SELECT first_name, last_name, department_id, Job_id FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE job_id LIKE '%ST%' OR job_id LIKe '%MAN%');
The inner query returns department IDs of all the employees whose job IDs contain strings ‘ST’ or ‘MAN’. The outer query returns all the rows where department ID lies in the set of department IDs returned by the inner query.
Example of MySQL subquery with SOME
This query returns the details of all the employees whose salary is greater than any of the salaries of employees with job IDs containing string ‘ST’.
SELECT first_name, last_name, salary, Job_id FROM employees WHERE salary>SOME (SELECT salary FROM employees WHERE job_id LIKE '%ST%');
The inner query returns salaries of all the employees whose job IDs contain string ‘ST’. The outer query returns all the rows where salary is greater than any value in the