SQL TUTORIALS

Subqueries vs. CTEs

When writing complex queries, it may be tempting to use multi-nested Subqueries in the WHERE Clause. However, this makes it much more difficult to read the query. Common Table Expressions (CTEs) help solve this problem. Watch the following videos to better understand this concept:

When to Use a Subquery in SQL
What is a CTE?

Summary

• You should use a subquery when the results of one query need to be used in another query.
• When working with Subqueries in the WHERE Clause, the Subquery must either be a single value (when comparing aggregates) or a list of values from a single column (when working with categorical data).
• Whether your query is using Subqueries or CTEs, break down your query and write it in parts. This makes your query much more readable.
• Common Table Expressions (CTEs) have the same benefits and use cases as Subqueries, though are much easier to read.

CTE Syntax

Now that you have a basic understanding of when to use a CTE, let's learn how to write one. Watch the following video to get a better understanding of this concept:

SQL WITH Statements (Common Table Expressions - CTE)

Summary:

• CTEs are used to make reading multiple Subqueries easier. To create a CTE with a single Subquery, use the following syntax:
  WITH [alias] AS (
SELECT [col1], [col2]
FROM [table_name]
WHERE [list of conditions]
)

SELECT *
FROM [cte_alias]
WHERE [list of conditions];
• To create a CTE with multiple Subqueries, use the following syntax:
  WITH [alias1] AS (
SELECT [col1], [col2]
FROM [table_name]
WHERE [list of conditions]
),

[alias2] AS (
SELECT [col1], [col2]
FROM [alias1]
WHERE [list of conditions]
)

SELECT *
FROM [cte_alias]
WHERE [list of conditions];

Use Cases

Although we have already covered some of the common use cases behind CTEs, let's dive deeper into why they are preferred over Subqueries. Watch the following video to get a better understanding of this concept:

When to Use CTE

Summary:

• CTEs are used in-memory and avoid creating temp tables (which are created when using Subqueries).
• As a Database Administrator (DBA), CTEs make it much easier to allow new users to view previous queries without having to give them unnecessary security privileges.
• Although CTEs contain more lines of code than traditional Subqueries, they make it easier to update the Subqueries as explained in the video.
• CTEs prevent human error by only requiring updates in a single Subquery rather than multiple.

Exercise

Congratulations! You just completed the Common Table Expressions (CTEs) Tutorial! To help test your knowledge, let's complete the same questions as the previous exercise, but using CTEs rather than Subqueries. This exercise is based on the exercise of the previous tutorial.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS client.
2. Using the "Sales" database, solve the following problems:
1. Using a CTE, write a query that returns all orders with a purchase_price greater than the average purchase_price.
2. Using a CTE, write a query that returns the highest purchase_price.
3. Using a CTE, write a query that returns revenue by order date. Filter to include the following states: "CA", "CO", "CT".
3. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!