Subqueries and CTEs: Asking a Question Inside a Question
Some questions can't be answered in one pass. "Which hive produced the most honey?" needs the maximum honey figure before it can find the hive that matches it. "Which keepers are above average?" needs the average before it can compare anyone to it. SQL handles this the same way you'd handle it on paper: work out the smaller number first, then use it. That's what a subquery is. A CTE does the same…
In SQL, subqueries and common table expressions (CTEs) are powerful tools for performing complex queries that cannot be answered in a single pass. A subquery is a query nested inside another query, wrapped in parentheses. The database runs the subquery first, obtaining a result that is then used by the outer query. There are three types of subqueries: scalar, IN, and correlated.
A scalar subquery returns a single value, an IN subquery returns a list of values, and a correlated subquery returns a result set that is correlated with the outer query. Examples are provided in the report, demonstrating how subqueries can be used to find the hive with the most honey, hives that have never been harvested, and each hive's most recent harvest.
Brief written by urgent.news from Dev.to's own syndicated text. Machine-written — may contain errors; check the original before relying on it.
