Welcome to part 2 of my Learn SQL series! Learning advanced SQL has been a fun ride so far, and to be honest, I’ve been seeing the benefits of understanding SQL so much more and more as I look into advanced data engineering and analytics.
Today’s topic is about CTEs, or also commonly known as Common Table Expressions.
The reason I want to go into CTEs is quite simple - it’s easy to read and understand. I’m sure, many of you would also somewhat agree!
What is a CTE?
A Common Table Expression is a type of temporary table which exists throughout the length of a long query. You can kind of think of it like a temporary result that you can use within main table queries via SELECT, UPDATE, or DELETE statements.
As I mentioned before, the main reason why I like CTEs is that they’re so much easier to parse out by eye compared to other temporary expressions such as derived tables.
Consider the following example.
Given the following table, write a SQL query which finds the largest number that only appears once.
For the sample table above, the query should return:
There are two ways to compute this solution. We’ll go over a derived table solution, and a solution which utilizes CTEs.
The idea behind the solution is simple. We want to do the following:
- Find the set of numbers where total count is not greater than 1
- Find the max number from the set specified in 1
Using a Derived Table
SELECT MAX(num) AS num FROM ( SELECT num FROM my_numbers GROUP BY num HAVING COUNT(*) = 1 ) valid_nums
Using a CTE
WITH ValidNums AS ( SELECT num FROM my_numbers GROUP BY num HAVING COUNT(*) = 1 ) SELECT MAX(num) AS num FROM ValidNums
Did you notice anything different between the two queries?
To be fair, both queries look relatively the same since this problem doesn’t require complicated table joins or filtering. I, however, think it’s important to see that the logical grouping (at eye level) in the CTE solution parses better than the derived query solution.
It is possible to give longer names to derived queries as well as CTEs. For example, the derived query solution calls our temporary
This method of naming can cause glance-over parsing difficulty when it involves multiple complex joins. The join names and definitions are usually cluttered on the same lines, which makes it harder to understand.
Kind of like programming best practices, right?
Ready to try it out? I promise you won’t regret it!