SQL Sub-Queries: Asking a Question inside a Question
Introduction
Some questions cannot be answered at first step. For example which customer spent more than the average customer? To find this, first SQL needs to find what is the average before comparing it to anyone.
Another simple example- If a teacher wants to know which students scored above the class average? It cannot be found directly. First the class average has to be found and then each students score has to be compared against it.
This is exactly the job of a sub query. A query runs inside another query and hands its answer to the outer one.
What is a Sub query?
A subquery is a query nested inside another sql statement, acting as an inner query that runs first to supply data to the main outer query.
- Query inside another Query is subquery
- Inner query runs first and supplies the answer to the outer query
- Then Outer query runs
We can call it as subquery or nested query since one query is nested inside another query.
Note: Subquery must always be enclosed in parentheses
Question : Find the films whose rental rate is higher than the average rental rate of all films? Display rental rate and film title for first 5 results.
Query
SELECT rental_rate,title
From film
Where rental_rate>(SELECT AVG(rental_rate)From film)
Limit 5;
Output
Sub Query Types
Sub queries can be classified based on what it returns or how it returns.
Lets see each sub query type in detail
Subquery types based on returns
Scalar Subquery
A Scalar subquery(inner query) returns exactly single value(one row,one column)
Query
SELECT rental_rate
From film
Where rental_rate>(SELECT AVG(rental_rate)From film);
What is the inner query here?
(SELECT AVG(rental_rate)From film);
Output
Inner Query result is 2.98
This is the signature shape of Scalar Sub Query
Scalar Sub query can be used in Select,Where and Having clauses.
Multiple row Subquery
Multiple row subquery returns a single column but multiple rows i.e a list of values pulled from one column across many records
Query
SELECT first_name, last_name
FROM customer
WHERE customer_id IN
(
SELECT customer_id FROM rental WHERE staff_id = 1);
what is the inner query here ?
(SELECT customer_id FROM rental WHERE staff_id = 1);
Output of the inner query
Multiple rows returned in a single customer id column. This is the signature shape of a Mutliple row subquery.
Multiple row subquery commonly pairs with In, All or Any operators when nested inside a Where clause.
Table Subquery
A table subquery is a nested sql query that runs multiple rows and multiple columns which the outer query treats it as a temporary or virtual table.
Query
SELECT customer_id,total_rentals
FROM ( SELECT
customer_id, COUNT(rental_id) AS total_rentals
FROM rental
GROUP BY customer_id
) AS customer_rental_summary WHERE total_rentals > 30
LIMIT 5;
What is the inner query here?
( SELECT
customer_id, COUNT(rental_id) AS total_rentals
FROM rental
GROUP BY customer_id
)
Output
Inner query results
Multiple rows and columns returned. This is the signature shape of Table sub query
Get Apriyaannamalai’s stories in your inbox
Join Medium for free to get updates from this writer.
The subquery is written inside the FROM clause and is treated as a table by the outer query.
Subquery types based on dependency
Correlated subquery
So,In correlated sub query process flow
1.First,Main query reads one row from the table
2.Value is passed to sub query
3.Subquery runs and produces result
4.pass it back to main query, Main query checks condition(If condition met- keeps row otherwise excludes row)
5.If row kept, add result in the final table.
Repeat for row 2,row 3 until row n(until all rows are done)
So it runs for every single row until all rows are done, Hence it can be slow.
Query
SELECT a.actor_id, a.first_name,a.last_name
FROM actor a WHERE
(
SELECT COUNT(*) FROM film_actor fa
WHERE fa.actor_id = a.actor_id) > 30;
what is the inner query?
(
SELECT COUNT(*) FROM film_actor fa
WHERE fa.actor_id = a.actor_id)
Output
because the inner query is dependant of the outer query, Thats why the output did not come.
If we run the full query the output will be
Executes multiple times,can be slow,often re-written as a join or window function.
Non-corelated subquery
It runs only once and is completely independent of the outer query. The inner query has no reference to outer query columns.Inner query result can be any shape scalar, multiple or TableSince it runs only once, it is generally fast and easy to optimise
Process flow
Data is passed to subquery first -> runs and produces result ->result is saved ->main query checks each time against the inner query result->matching rows are added to the Final output table.
Query
SELECT title, rental_rate FROM film WHERE rental_rate > ( SELECT AVG(rental_rate) FROM film );
Inner query is independent,runs and produce single value,every row is matched against it.
Ouput
Now we cracked the most difficult topic of correlated sub query and Non-correlated subquery
Lets move on correlated sub query with Exists/Non-Exists
Both are correlated,inner query references the outer query’s row on every check.Instead of returning a value they simply test whether a matching row exists
EXISTS
True when the correlated subquery returns atleast one matching row
Query
SELECT c.first_name, c.last_name
FROM customer c
WHERE EXISTS (
SELECT 1 FROM rental r
WHERE r.customer_id = c.customer_id
AND r.return_date IS NULL);
What it checks: Customers who currently have an active(unreturned) rental.
The subquery only needs to find one matching row,It does not return any value,it jsut gives True or False for each customer.If it is true,that customer is kept in the output otherwise that customer is skipped.
Since it stops searching as soon as it find one match,it is faster on large tables.It is commonly used in place of IN for correlated existence checks.
Not Exists
True when the correlated sub query finds zero matching rows.It is the logical opposite of EXISTS.
Query
SELECT c.first_name, c.last_name
FROM customer c
WHERE NOT EXISTS (
SELECT 1 FROM rental r
WHERE r.customer_id = c.customer_id);
What it returns
customers who never made a single rental,the sub query must confirm no matching row exist at all.It can take more time than EXISTS because it has to go through all of that customers rental to be sure that there is no match.
Subqueries at a glance
Conclusion
A subquery is simply a question inside a question.The inner query answers first and the outer query uses that answer.Whether it returns a value or list or a table and whether it runs once or once per row,the real skill is spotting the smaller question hidden inside the big question.
“Subqueries lets you answer layered business questions in a single query”
