SQL Tutorial: Subqueries and common table expressions (cte)

Want to learn more? Take the full course at https://learn.datacamp.com/courses/improving-query-performance-in-postgresql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
You've learned about joins; now we will explore subqueries and common table expressions as alternative methods for combining data.

A subquery is a query inside another query, that can be used as an alternative to a join.

You can place a subquery in each main section of the query; in the SELECT, FROM, and WHERE clauses.

Subqueries are appealing because they can return a single result instead of a join's many rows. They can be more readable than a series of joins. In terms of the query planner and execution, they are SQL instructions similar to a join.

Let's start with a SELECT subquery.

On the left, is a table of all words and word length from a movie script. Each word is a unique entry. Words used more than once in the script are repeated, like goat shown here in rows one and two. On the right, is a table of all the words in the English language. Each word only has one record.

Suppose you want to know the average word length in both the script and in English. You could join the tables. To retain both the English words not included in the script and the duplicate script words, you would need a full outer join. The result would have more records than the 171,476 rows in the English table.

Instead, you could use a SELECT subquery. This example finds the average word length of the movie table in the main query. It then uses a subquery referencing the English table to find the average language word length.

Alternatively, you can place a subquery in the WHERE clause.

Because you are not listing a set of values but rather referencing another query, WHERE subqueries are dynamic filters.

This example again calculates a movie word length average. The subquery dynamically filters the English table to only those words that are found in the movie script.

You can also use a subquery in the FROM clause. Here the outer query uses the results of the subquery as its data source.

Generally, this structure decreases the query readability and limits the query planner flexibility. In the query execution plan, subqueries in SELECT and WHERE statements are analogous to joins. Subqueries in the FROM statement are not, limiting the optimization capacity.

FROM subqueries are usually best rewritten as joins.

Common table expressions, or CTEs, are another join alternative. A CTE is a standalone query that returns a temporary results set.

The query is wrapped in a WITH function and the results referenced in later SELECT statements.

CTEs are appealing for many of the same reasons as subqueries. They can return a single result and are more readable than a series of joins.

Unlike subqueries, CTEs create a temporary table that is only executed one time. We will discuss temporary tables in another lesson, but here know that they are advantageous when working with large or complex tables that are resource intensive to query.

This example creates a CTE, showing the initial WITH statement as a standalone query that finds the number of words for each word length in the English language.

The final SELECT statement counts the number of words in the movie table and joins to the english_cte to pull in the English word count aggregation.

The English table is large. Accessing it only in a CTE that itself aggregates the data improves the speed when compared to a subquery structure because the query planner materializes the CTE into a temporary table.

Now it is your turn to practice using these join alternatives.

#SQL #SQLTutorial #DataCamp #Subqueries #table #expressions #Performance #PostgreSQL