Understanding Subquery and Common Table Expressions(CTEs)

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 WITHkeyword. It acts like a named query you can reference later.

Types of CTEs and their uses
Recursive CTEs
it references itself independently
Uses
for organization of data in a systematic way
used in finding paths in different places
to create lists or series of data
Non-Recursive CTEs
frequently used in sql and refers to a set of script you can use anytime.
Uses
Used in referencing e.g reusing codes
Used in listing multiple CTEs
for simplifying results e.g AVG

Comparisons between Subquery and CTEs
Subquery is nested inside while CTE is nested outside
Subquery cannot be reused while CTE are commonly used within
same query.
Subquery is used for simple filtering while CTEs for complex ones
When it comes to maintenance subqueries are quite challenging to
maintain due to logic while CTEs are easier.