Common Table Expressions Can Be A Good Thing

Most of our coding these days has Common Table Expressions, also known as CTEs. If not, you’re either working on an older version of SQL Server or you haven’t been introduced to this piece of goodness.

CTEs can make reading SQL queries a lot easier if the logic is convoluted. For example, let’s use the following in a CTE.

;WITH cteNewYork as (
      SELECT Latitude, Longitude, State
      FROM dbo.tblAddress
      WHERE State = 'NY')
SELECT *
FROM cteNewYork CG
LEFT JOIN dbo.tblMuseum M ON M.Latitude = CG.Latitude
           AND M.Longitude = CG.Longitude 

This is a contrived example, but you can easily see that we’re grabbing data from tblAddress that has a state of New York and joining it to tblMuseum based on the Latitude and Longitude.

You will also see developers writing queries like this:

;WITH cteData as (
      SELECT *
      FROM dbo.tblAddress
      WHERE State = 'NY')
SELECT * 
FROM cteData

Notice that we’re just gathering data from the CTE in the non-CTE portion of the query. No WHERE clause. No GROUP BY. No nothing.

This, my friends, should be something you strive not to do, even though I’ve seen it thousands of times. You are just making SQL Server – or Snowflake or any of the other RDBMS that support CTEs – do a bit more work. If this query runs 200K times per day, you can save several computer cycles by turning it into the following:

SELECT *
FROM dbo.tblAddress
WHERE State = 'NY'

Then, there is also the following:

;WITH cteNYState as (
      SELECT Latitude, Longitude, State
      FROM dbo.tblAddress
      WHERE State = 'NY')
   cteNYMuseums as (
      SELECT *
      FROM cteNYState NS
      LEFT JOIN dbo.tblMuseum M ON M.Latitude = NS.Latitude
           AND M.Longitude = NS.Longitude)
   cteWVState as (
      SELECT Latitude, Longitude, State
      FROM dbo.tblAddress
      WHERE State = 'WV')
   cteWVDinosaurs as (
      SELECT *
      FROM dbo.tblDinosaurs D
      LEFT JOIN cteWVState WV ON WV.Latitude = D.Latitude
           AND WV.Longitude = D.Longitude)
cteWhatIsMissing AS (
      SELECT Latitude, Longitude, State FROM cteWVState  
      WHERE DinosaurID is NULL
      UNION ALL
      SELECT Latitude, Longitude, State FROM cteNYMuseums 
      WHERE MuseumID is NULL)
SELECT DISTINCT Latitude, Longitude, State
FROM cteWhatIsMissing
ORDER BY State, Latitude      

Notice that there are 5 CTEs used in this query. That can be a lot – especially if your query runs for a bit. Sometimes the more CTEs you have in a query, the slower the overall query is. Maybe you should try pushing this data into temporary tables

In other words, just because you can use CTEs doesn’t mean you should overload your RDBMS with CTEs. It also doesn’t mean you should use them just because they’re there.

As always, be smart with where you use functions and whatnot within your favorite database tool. It can be your best friend or your worst enemy if you have to fix it not long after you implement it in Production.

With that, my friends, we have come to the end of our time together today. This time, let’s work with CTEs – but let’s not go too crazy with them.