In MySQL, a subquery has defined as a SELECT SQL statement used inside another SQL statement to calculate the results of outer queries. A subquery in SQL is an inner query that is placed within an outer SQL query using different SQL clauses like WHERE, FROM and HAVING along with statement keywords such as SELECT, INSERT, FROM, UPDATE, DELETE, SET or DO accompanied with expressional operators or logical operators.
Scalar Subqueries
When a subquery returns a single value or exactly one row and exactly one column, we call it a scalar subquery. This type of subquery is frequently used in the clause to filter the results of the main query. The subquery in our previous example is a scalar subquery, as it returns a single value (i.e. the average agency fee).
Scalar subqueries can also be used in the main query’s SELECT statement. For example, let’s say we want to see the average price of all our paintings next to the price of each painting.
code
SELECT name AS painting,
price,
(SELECT AVG(price)
FROM paintings) AS avg_price
FROM paintings;
Multiple-Row Subqueries
If your subquery returns more than one row, it can be referred to as a multiple-row subquery. Note that this subquery type includes subqueries that return one column with multiple rows (i.e. a list of values) and subqueries that return multiple columns with multiple rows (i.e. tables).
Subqueries that return one column and multiple rows are often included in the WHERE clause to filter the results of the main query. In this case, they are usually used with operators like IN, NOT IN, ANY, ALL, EXISTS or NOT EXISTS that allow users to compare a particular value with the values in the list returned by the subquery.
code
SELECT AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN (SELECT id
FROM managers);
Correlated Subqueries
There are also SQL subqueries where the inner query relies on information obtained from the outer query. These are correlated subqueries. Because of the interdependence between the main query and the inner query, this type of subquery can be more challenging to understand. Read this beginner-friendly guide to become more proficient with correlated subqueries in SQL.
Code
SELECT city,
(SELECT count(*)
FROM paintings p
WHERE g.id = p.gallery_id) total_paintings
FROM galleries g;
Single Row Subqueries
Subqueries that return a single row as an output to their parent query are called single-row subqueries. Single-row subqueries are used in a SQL SELECT statement with having clause, where clause or a from clause and a comparison operator. Single-row subqueries are used in the SELECT statement.
code
SELECT name AS painting,
price,
(SELECT AVG(price)
FROM paintings) AS avg_price
FROM paintings;
Multiple Column Subqueries
Subqueries that return multiple columns as an output to their parent query are called multiple-column subqueries. Let’s see it through an example.
code
SELECT id, name, price
FROM paintings
WHERE (name, price) IN
(SELECT name, MIN(price)
FROM paintings);