In MySQL, a subquery is a query nested within another query, whose intermediate result can be used by the main query as a condition or input.

When a subquery doesn't rely on the outer query, it is executed only once and is referred to as "non-correlated".

On the other hand, when a subquery references columns from the outer query, it is executed repeatedly, once for each row processed by the outer query, and is referred to as "correlated".

Subqueries in the WHERE clause

Subqueries in the WHERE clause allow you to compare the value of a column to the result of the subquery using comparison operators, such as = or >:

SELECT column_name, ...FROM table_nameWHERE column_name operator (subquery);

Note: Subqueries can also be used in INSERT, UPDATE, and DELETE statements.

Example

This SQL statement will retrieve the first_name, last_name, and salary attributes of all the records in the employees table, where the value of the salary attribute is greater than its average value across all records:

mysql> SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);+------------+-----------+--------+| first_name | last_name | salary |+------------+-----------+--------+| Jane       | Smith     |  70000 |