Menu

1 / 3

Understanding Subquery and Common Table Expressions(CTEs)

DEV Community·Gabriel Njoroge·7 months ago
#UWrDp86T
#beginners#database#sql#tutorial#subquery
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.