Lesson 2: SQL Subqueries & Common Table Expressions

Executive Summary:

Next in our series of lessons, we will review subqueries and Common Table Expressions (CTEs). Both can be extremely powerful and useful tools to refine the data returned by your queries, resulting in more specific data manipulation and potentially improved performance.

Subqueries (PART 1)

Subqueries, or nested queries, allow you to utilize the result of a query as part of another query. A subquery can return data to be used in the main query as a condition to further drill down the data. See the below example to see both the original code and a subquery to gather Customer ID’s. What makes these different? (HINT: Look at the selected columns and the output)

--- Original Code
SELECT *
FROM Customers
JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID	
WHERE OrderDate = '01/01/2023'

--- Subquery to Find CustomerID
SELECT *
FROM Customers
WHERE Customers.CustomerID 
in (
    SELECT Orders.CustomerID
    FROM Orders 
    WHERE Orders.OrderDate = '01/01/2023' 
    )

Subqueries (Part II)

Just for fun, please review the below example, to show more benefits of a subquery. Please note the declared variable for centralized value changes.

DECLARE @EarliestDate DATE = '01/01/2023'
-- Customer Info 
SELECT *
FROM Customers C
WHERE C.CustomerID 
in (
	SELECT O.CustomerID
	FROM Orders O
	WHERE O.OrderDate >= @EarliestDate 
	)

Common Table Expressions (CTEs)

A CTE is a temporary named result set that can be referenced by another SQL Statement. This allows complicated SQL joins to be simplified, for queries to be broken in simpler separate parts, or to perform recursive queries. We’ll cover recursive queries in the next part.

In the below example, you will have some compilation issues. Troubleshoot it by removing a line. Why do you think this happens? Congrats, you’ve discovered a caveat of CTEs!

-- US Customers 
;WITH US_Customers AS (
  SELECT C.CustomerID, C.CustomerName, MIN(O.OrderDate) FirstOrderDate
  FROM Customers C
  JOIN Orders O on O.CustomerID = C.CustomerID
  WHERE Country = 'United States'
  GROUP BY C.CustomerID, C.CustomerName
)

SELECT US.CustomerID, US.FirstOrderDate, O.OrderID, O.OrderDate
FROM US_Customers Us
JOIN Orders O
ON Us.CustomerID = O.CustomerID
ORDER BY FirstOrderDate desc, CustomerID, OrderDate ASC

SELECT * FROM US_Customers

Optimization with Subqueries and CTEs

Properly using subqueries and CTEs may generally improve performance. You may have to do several iterative tests to truly understand what works best in your case.

• Limiting Result Length
  • In our above example, the result set is small. What if our company made 1,000 sales a day. Would we truly want to examine all records if at the end we desire to know the results for customers in California?
• Improving Readability
  • In our above example, which is the most readable to you? What do you think will be the friendliest to you when maintenance is required a year from now?
• Recursive Operations
  • They exist and can be done. The next lesson will cover them.

Sadly, there is no guidebook or one size fits all solution, but you now have several tools in your toolbelt to make your life easier.