Guru: Debugging Common Table Expressions

October 21, 2019 Ted Holt

I cannot say enough good things about common table expressions. Words like wonderful and marvelous don’t begin to describe them. However, CTEs do add a bit of complexity to an SQL query, and when the result set doesn’t contain the correct results, any common table expression can be the culprit. Fortunately, debugging queries with common table expressions is not difficult.

To illustrate what I mean, let’s assume we have a query that retrieves shipment information for one day. It involves a few tables:

• a one-row table containing a shipment date
• a table of shipment header information
• a customer master table