SUBQUERIES AND CTEs

#subqueries #commontableexpressions #analytics

There comes a time in the life of any SQL student when they find themselves writing queries in a certain way. Before this moment, they write their queries in a straight line, selecting this, joining that, grouping by the other. After this moment, they write queries like dragons: constructing a query from blocks, each one giving a small amount of data, naming it something understandable, and moving on. All the complex beasts of SQL that once terrified them are now simple littleizards that they can debug without fear. This moment comes when they learn about subqueries and Common Table Expressions (CTEs).

A subquery is simply a query nested within another query. A CTE is a named subquery, useful for organizing more complex SQL statements.

In this article, we will explore both, using each to solve the same problem in order to highlight their similarities and differences.

Subqueries serve many different purposes and can be used in the SELECT, FROM, WHERE, JOIN, and HAVING clauses.

1.Subqueries in SELECT

A subquery in the SELECT section of a query always has to return a scalar value.

2.Correlated subquery
A correlated subquery is a type of query that uses values from the outer query. The inner query executes, referencing these value(s), and returns the result to the outer query. This happens
row by row.

3. Subqueries in FROM/JOIN
ubqueries can be used in FROM and JOIN to create intermediate or derived tables, which can be queried again. This is particularly useful when we would like to use aggregated data along with
column data.
This is the syntax

example

4.Subqueries in WHERE/HAVING
Subqueries can be used in WHERE and HAVING to enable customized or advanced filtering of results.

Common Table Expressions
A Common Table Expression, or CTE, is a named query that exists within the context of a larger query. Its results are temporarily stored such that they can be referenced later by other queries, other CTEs, or even itself.
CTE syntax