SUBQUERIES AND CTE

Subqueries this are queries inside another query. Many of the times a problem cant be solved using a single query now thus where subqueries comes in .

Subqueries can appear in a few different places ;

1. Subquery inside a where clause.

example of use case

1. subquery on select clause used a computed column when used in the select clause.

3.Subquery on the from
this is treated as a temporary table.
example

CTE
CTE is an abbreviation for Common Table Expression it typically does everything that a subquery can do but in a more presentable way.
Most of the time I have used cte rather than a subquery .
how you write a cte is you use a with clause to define it then followed by () after that you call it like just a table but it should be immediately after writing it
example of cte

You can also have mulitple CTE
EXAMPLE

When to Use Each

Use a subquery when:

You need a quick calculated value
The logic is simple enough to read in a single nested line
You're only using that intermediate result once

Use a CTE when:

The query has multiple steps that build on each other
You want to reference the same intermediate result more than once
You're working with recursive data
Readability matters CTEs make complex queries much easier for someone else to follow especially when you are working in a large organization .