4 ms·
CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as: with source as ( select * from wherever ), transformed as
by drx 4y ago
CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as:
with source as (
select * from wherever
),
transformed as (
...
),
joined as (
...
),
final as (
...
)
select * from final
You can switch `final` to `transformed` to see what the query is doing internally. Almost like having good control flow. Almost.
- pkoird 4y agoAlmost seems like a procedural syntax like source = ... transformed = ... joined = ... final = ...
- acjohnson55 4y agoIt doesn't seem procedural to me, as there is no rebinding. Such a sequence of assignments would look just as home in a functional language like Lisp, ML, or Haskell as Python. In procedural languages, idiomatically, you have mutation, in which variables are re-bound to new values, and side-effects, in which the external environment and the program can interact with each other in ways that are unconstrained.
- hackernewds 4y agoI prefer actually materializing the tables so then I can check the output for what the transform tables and the joined tables look like. generally can't just swap transformed in final because final depends on the output of transformed?
- Bootvis 4y agoThe trick is to change the name of the CTE in your final select. This will allow you to inspect that particular step. This works as long as your CTE’s are correct SQL and only the logic is wrong or suspect.
- Ntrails 4y agoI guess the point is you could just stick the results of each step that is currently a CTE into #table as separate queries, and you're only doing the work once to step through the stages? From a debug point of view that feels more convenient to _me_.
- Bootvis 4y agoI guess it depends, materialising all the steps might be slow and you might need to do a refactor to get a CTE-based query. If you want just want to check a few (maybe changing) records because you expect problems there a final select with an appropriate where is probably faster. Edit: also I don’t like to leave a mess behind and CTE’s don’t require cleaning up afterwards.
- dx034 4y agoWith CTEs, the optimizer won't execute the queries but create one optimized version. If you materialize them, execution times can be many magnitudes higher in cases you only end up using small parts of the queries.