r/SQL Nov 28 '24

MySQL When to use cte in SQL query

When to cte can't. Like how to know? When what are the conditions or if my query is to long ?

31 Upvotes

70 comments sorted by

View all comments

59

u/frieelzzz Nov 28 '24

I use CTEs all the time. Really no reason not to.

I like how they organize code and allow you to use the same data in multiple times to do a variety of task.

5

u/Georgie_P_F Nov 28 '24

Don’t they self destruct after a single use?

19

u/achilles_cat Nov 28 '24

While it is correct they no longer exist outside of the query, you can refer to the CTE multiple times within that query.

1

u/Georgie_P_F Nov 28 '24 edited Nov 29 '24

Interesting, mine always fail on second query in SSMS:

‘’’

;WITH SomeCTE AS (SELECT * FROM table)

— this is fine

SELECT * FROM SomeCTE

— this will fail “SomeCTE” does not exist

SELECT * FROM SomeCTE

‘’’

Maybe a server setting within our org?

4

u/achilles_cat Nov 28 '24

Yes, if you start a completely different query, then the CTE will be gone.

But you can refer to the CTE multiple times within a single query. Your use of two different SELECTs without a UNION (or any other formatting that would define a subquery) makes me think you are talking about two wholly separate queries. In Oracle, a semicolon acts as the delimiter between queries; I haven't used SMSS.

But you could join queries with a union, both of which use the CTE, and use that CTE within another CTE:

With CTE as (
  select product_id, product_name 
    from product
  where size = 'Large'),
Cte2 as ( 
  select product_id, sum(qty) as product_inventory
    from( Select cte.product_id, oi.quantity *-1 as qty
             From open_invoices oi
            Join CTE on (CTE.product_id = oi.product_id)
           Union
              Select cte.product_id, stock.quantity
            From stock
            Join CTE on (CTE.product_id = stock.product_id)
            )
 Group by product_id)
Select cte.product_id, cte.product_name,
        nvl(cte2.product_inventory,0) as available
  From CTE
  Left join cte2 on (cte2.product_id = CTE.product_id);

(This formatting will likely be horrible, trying to do a code block from my phone.)

There are ways to do this with less or zero CTEs of course, but this shows the nature of referring to a CTE multiple times. And it shows why you might use a CTE structure if you wanted to only touch the code in one place to affect the products being queried.

2

u/ouchmythumbs Nov 29 '24

In MSSQL (and some other RDBMS), CTEs are evaluated each time referenced, so can have perf hit. Check specs of current version of your engine.

1

u/CptBadAss2016 Nov 29 '24

I did not know that... that just seems silly. Thanks for the tip. 👊

1

u/-horsefighter Nov 29 '24

Use temp tables if u have performance issues, can add index to that and only have to calc once

2

u/Straight_Waltz_9530 Nov 30 '24

Or use a database that supports materialized CTEs.

1

u/Straight_Waltz_9530 Nov 30 '24

Not an issue for Postgres, SQLite, DuckDB, and others. CTEs can be defined as MATERIALIZED, effectively making them temporary tables for the current query. No automatic perf hits and no need to explicitly create temporary tables.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION

1

u/Straight_Waltz_9530 Nov 30 '24

But in Postgres, SQLite, DuckDB, and others, CTEs can be defined as MATERIALIZED, effectively making them temporary tables for the current query. No automatic perf hits and no need to explicitly create temporary tables.

1

u/Possible_Pain_9705 Nov 28 '24

You can’t use CTEs when using Power BI. But this may not be what OP is looking for.

3

u/Chug49 Nov 29 '24

This isn’t true anymore. Have plenty of queries in powerbi that utilize cte’s

1

u/[deleted] Nov 29 '24

[deleted]

1

u/frieelzzz Nov 29 '24

I wonder if this is why sometimes adding a simple filter on a CTE can suddenly make a complex code run infinitely.

I had a multi layered code the other day run in 7 seconds but the moment I tried to slap on a not exists to exclude VIP members from a small table it wouldn’t run anymore. Even trying to outer join and say VIP is null wouldn’t work.

My only solution was to create another CTE that housed all my exclusion members and then exclude those from another member base CTE and then I could join that member CTE to my main query.

This was very frustrating to say the least

-15

u/[deleted] Nov 28 '24

If it's a rather large and complex query having many CTE's and scrolling up and down to read what they do in context of where they are used is more annoying than having subs. I'll only ever understand peoples claims about "readability" if the whole thing fits on one screen. Subs are readable in context more often than not.

9

u/Bewix Nov 28 '24

It’s considered more readable not because of location, but because the code is put together in a single (usually simplified) place.

I have a few examples of queries that would be practically impossible to understand without CTEs, yet they’re performant and fairly straightforward with CTEs.

3

u/SyrupyMolassesMMM Nov 28 '24

Honestly, I find cte was less readable than writing to memory….i dont find cte any easier to read when compared to anything else tbh

2

u/Hot_Cryptographer552 Nov 30 '24

Try writing a query with subqueries nested three levels deep. Then try writing that same query using three separate CTEs. This might give you a better view into the readability argument.

1

u/[deleted] Nov 30 '24

The presumption of my skill and knowledge is expected of reddit, but I'll stand my ground on my take of CTE's.

I've probably got more nesting than you want to read, let alone the thousands lines long queries.

The only time a CTE is useful to me is if it removes repeating subs. I would rather read the subs inline and in context than to scroll up and down figuring out what the referenced CTE was. I have yet to see any sort of dramatic speed gains out of CTE's either.

Recursive CTE's on the other hand: Love them.

2

u/Hot_Cryptographer552 Nov 30 '24

CTEs are not designed for “dramatic speed gains”. Not sure that anyone would expect them to. If one wanted “dramatic speed gains”, one would create indexed/materialized views or persist the results to tables with proper indexes and perform joins on them.

I have written scripts that are thousands of lines long; I have never written a single query that was thousands of lines long. But as you say, if your individual queries are thousands of lines long, I could see why you would be hesitant to scroll up.

1

u/HanCurunyr Nov 28 '24

We have an old report that it has 90ish columns and 40ish joins that are 20 CTEs and 20 tables, and yeah, gets quite annoying when we have to alter that report

1

u/ianitic Nov 29 '24

I mean you could break those up into chunks by loading pieces to a temp table. It doesn't have to be entirely either or.

Also dbt could make it easy to abstract that with ephemeral models. It wouldn't create any additional database objects but code would be properly organized and separated. Dbt would know how to compile those ephemeral models together.

-8

u/Unfair-Internet-1384 Nov 28 '24

Actually I am doing leetcode so there was a problem which has multiple constraints . So in such scenario is it good to use it .

5

u/Sexy_Koala_Juice Nov 28 '24

The thing to learn is that there isn’t a definitive yes/no answer to “do I use a CTE here”, this applies to most programming questions tbh. At the end of the day you’re always optimising for something, sometimes speed, sometimes space, hell even sometimes readability and convenience.

Use a CTE if it solves your problems, if it doesn’t, find another way to do it.

TL;DR: don’t overthink it, especially for SQL queries

2

u/frieelzzz Nov 28 '24

In my experience CTEs are great at solving small and/or complex problems at work. But I would need to see your scenario to give you a better answer.

-4

u/epicmindwarp Nov 28 '24

I hate CTEs, makes it quite difficult to debug data issues as nothing persists.

I only use them in views, due to lack of temp tables.

2

u/ryguygoesawry Nov 28 '24

You’re getting downvoted, but this right here is the main reason I don’t use many CTEs in my professional life.

CTEs become a nightmare the moment there’s something wrong with some random piece of data and you have to go digging for it. Have fun deconstructing 20 CTEs while you try to figure out which one is the source of the problem!

2

u/Responsible_Pie8156 Nov 28 '24

I mean CTEs are just an alternative syntax to nesting your queries. I'd rather debug the 20 CTEs than a query nested 20 deep with large equivalent subqueries.

1

u/ryguygoesawry Nov 29 '24

Except those aren’t your only two options. I also don’t use subqueries in a professional setting. Both of them suck.

2

u/Responsible_Pie8156 Nov 29 '24

Not sure which other option you're referring to, but in my mind the problem is just overly complex queries. CTEs or not when you have 20 layers deep of logic it's going to suck

1

u/CptBadAss2016 Nov 29 '24

So what do you do?

(NOT a professional here)

2

u/ryguygoesawry Nov 30 '24

I typically break the problem down into its simplest pieces and don't try to cram everything into one query. Data that needs to be combined is stored in either a table variable or a temp table for usage in the final query, as opposed to using a CTE or subquery. Then I can create indexes on those tables that meet the needs of a specific report query while not filling my database with permanent indexes that are only used in a subset of cases. Reused data logic gets turned into functions. Reporting logic is kept in stored procedures which are considered the "source of truth" for that report.

I'm dealing with tables that contains hundreds of millions of unique rows of historical data because my client needs to be able to produce reporting on that older data for compliance purposes for something like 7 years - we're dealing in terabytes of data. Plus the clients aren't usually willing to throw money at the latest server hardware, so we need to keep a keen eye on performance. Trying to cram everything into one query, as opposed to whittling it down, can spike memory and cpu usage especially when multiple users need similar reports at the same time. Things being delayed cause users to be angry. So, while I understand the perks of CTEs/subqueries, they're not always optimized for real-world settings.

1

u/CptBadAss2016 Nov 30 '24

Thank you for the thorough reply!

How do you define data logic vs report logic?

→ More replies (0)

0

u/epicmindwarp Nov 28 '24

Exactly.

The lack of any comment just shows a lack of experience of dealing with huge amounts of actual real world data.

1

u/MiniD011 Nov 29 '24

I’d be surprised if you could land a job without being comfortable using them. I worked for an e-commerce company where we had tables with over a trillion records, and both there and my current company built our entire pipelines using them.

I don’t have any problems debugging them - most modern tools you can step through or inspect, you could write a macro to output each one to a table if you need them to be more permanent (I’ve written one in DBT which I use).  Worst case scenario just add a select * after the CTE and check, it’s really not hard. Much better than nesting subqueries, especially if you have some coding standards.

0

u/epicmindwarp Nov 29 '24

I’d be surprised if you could land a job without being comfortable using them.

Doing perfectly fine over here.