What is a Subquery?
A subquery is simply a select statement written inside another select statement. The inside query runs first and results is handed to outer query for use.
Example:
Explanation
i)First it finds the Average Score
ii)It finds all rows which are greater than the Average score.
Types of Subqueries
1. Scalar Subquery
It returns a single value
It will output the single highest score recorded.
2. List Subquery
It returns a column of values. e.g List of IDs. It is used with IN or NOT IN.
It will output the names of members who scored above 80.
3. Subquery IN FROM
You can place subquery in the from clause. SQL treats subquery as if it were a temporary table.
When to use Subqueries
When logic is simple and needs no re-use
when you want to filter data based on aggregated values
when you need a quick, one time calculation
CTEs or Common Table Expressions
It is a temporary result set defined at the start of a query using the WITH keyword.…