MySQL Subqueries Comparison Operators

MySQL Subqueries use comparison as the fundamental operation for combining with outer queries. A subquery is written as a part of WHERE clause in a DML statement ( INSERT, SELECT, DELETE and UPDATE).

The basic form of using a subquery is

OUTER_QUERY WHERE comparison_operand comparison_operator (SubQuery)

=,  >,  <,  >=,  <=,  <>,  != and  <=> are the comparison operators used for  comparison_operator

These operators are used when the subquery returns only single value means with a scalar subquery.

… WHERE salary = (SELECT salary FROM Employees WHERE emp_id=1001 )

MySQL Subqueries are also used with LIKE operator.

OUTER_QUERY WHERE comparison_operand LIKE (subquery)

Examples of MySQL Subqueries

Using = operator

In this example the query returns the details of the employees whose salary is equal to the minimum among all employees.

select employee_id, first_name ,salary from employees where salary=(SELECT MIN(salary) from employees);
MySQL Subqueries with =

Why can’t we do it with a single query? Why we need a subquery to get the rows where salary is minimum?

If we use the single query there is no way to compare salary field with the lowest salary as MIN  is an aggregate function that returns single value from the table. It is an aggregate function and the non aggregated fields cannot be used in query unless the data is grouped on non aggregate fields.

This query works like this

  • The subquery returns the scalar value (minimum salary) from employees table.
  • This returned value is compared with salary of each row by the outer query.
  • Outer query displays those rows (defined columns) whose salary matches the minimum salary returned by inner query.

Using < operator

This example returns the details of all those employees whose salary is less than the average salary of all the employees in the employee table.

SELECT employee_id, first_name ,salary FROM employees WHERE salary<(SELECT AVG(salary) FROM employees);
MySQL Subqueries with <

Here the subquery returns the scalar value (average  salary) of all employees. The outer query compares salary of all rows of the table with this average salary. The output is all the rows matching the WHERE condition i.e all rows having salary less than the average salary.

Using > operator

This example returns the details of all those employees whose salary is greater than the average salary of all the employees in employee table.

SELECT employee_id, first_name ,salary FROM employees WHERE salary>(SELECT AVG(salary) FROM employees);
subquery with >

Here the subquery returns the scalar value (average salary) of all employees. The outer query compares salary of all rows of the table with this average salary. The output is all the rows matching the WHERE condition i.e.  all rows having salary greater than average salary.

Using LIKE operator

This query returns the details of all those employees whose name starts with the first alphabet of first name of the employee having  employee ID 115.

SELECT employee_id, first_name ,salary FROM employees WHERE &nbsp;first_name LIKE (SELECT CONCAT(SUBSTRING(first_name,1,1),'%') FROM employees WHERE employee_id=115);
With LIKE operator

The inner query filters the row with employee ID 115. SUBSTRING function extracts first character of first_name of this row. CONCAT functions combine this character with ‘%’. The string, thus created, has a character followed by the % wild character.

It is compared with first names of all the rows and returns details of employees whose first name’s character matches first character of first name of the employee with employee ID 115.