10 ms·

CTEs as lookup tables

177 points by preetamjinka 4y ago

acjohnson55 4y ago
Occasionally, SQL surprises with bits of composability, such as the fact that using VALUES to specify literal tuples can be used with both INSERT and the FROM clause of a SELECT. Is there any reason syntactically the SELECT needs to be required? If you use a VALUES table literal as a subselect, you have to give it column names with an AS clause (see https://www.postgresql.org/docs/current/sql-values.html https://www.postgresql.org/docs/current/sql-values.html). I can imagine a simpler syntax where you'd do WITH VALUES ... AS table_name (column_1_name, ...). Is there any reason to alias the lookup table as `codes`? There are apparently lots of other clever uses of the WITH clause, such as https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING https://www.postgresql.org/docs/current/queries-with.html#QU...
- revskill 4y ago
  Yes, it's what i'm doing, example here: https://gist.github.com/revskill10/57ecd8efb72f361b93e6d9d9f8a1bfda https://gist.github.com/revskill10/57ecd8efb72f361b93e6d9d9f... Basically, i could put with: values after the join.
  - jpgvm 4y ago
    What is that YAML query language abomination in the other snippets?
msla 4y ago
What does CTE stand for in this context?
- advisedwang 4y ago
  Common Table Expression
- johnvaluk 4y ago
  Common Table Expression (the WITH clause).
- acjohnson55 4y ago
  Common table expression. I don't know exactly where the "common" part comes from, but I'm guessing because you can use its alias multiple times in the subsequent CTEs and the main query.
- drx 4y ago
  Common table expressions: https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html
- adzm 4y ago
  common table expression. It's basically like a view that can be defined at the beginning of a query, and can be referenced within that query.
- revskill 4y ago
  Composable Table Expression.
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 ago
  Almost seems like a procedural syntax like source = ... transformed = ... joined = ... final = ...
  - acjohnson55 4y ago
    It 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 ago
  I 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 ago
    The 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 ago
      I 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_.
fphhotchips 4y ago
Fun fact, this can also be more performant, depending on the engine.
- jbrwn 4y ago
  CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.
  - garfij 4y ago
    My understanding was that Postgres fixed this back in version 12. Are there still limitations here?
    - epgui 4y ago
      Nope, there’s no performance downside to using CTEs in recent postgres versions, unless the CTE is recursive or has side effects (which would be weird).
    - masklinn 4y ago
      Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materialized by default. Also not-mat CTEs can perform a lot worse: https://stackoverflow.com/questions/64016236/postgres-12-materialized-cte-much-faster https://stackoverflow.com/questions/64016236/postgres-12-mat... But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-side-effects-to-postgres-12s-not-materialized-directive https://dba.stackexchange.com/questions/257014/are-there-sid... So the limitations are that it’s very much ymmv.
  - no-s 4y ago
    > predicate pushdown is a problem on both MSSQL and Postgresql Not sure you’re completely on target here regarding CTE performance. I don’t have deep insight into Postgresql (but “create temporary view", yeah!). MSSQL does a pretty good job in Sql2019. If the predicate is sargeable in some way pushdown is reliable. If performance is a concern, examining the residuals can lead to significant insights e.g. applying index filtering which solves obvious problems. Recursion is another story. CTE is more likely used by data analyst queries because it is an abstraction of composition. It’s not a great abstraction but it’s better than nothing, which is mostly what you get with SQL.
chaps 4y ago
That's a really neat trick. Gosh, CTEs are great.
cfeduke 4y ago
CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (That point being the size of the tables in question/index/etc.)
- bob1029 4y ago
  I have strongly encouraged reckless use of CTEs throughout our product. We use in-memory instances of SQLite to evaluate any queries which would leverage CTEs. These datasets are usually small enough to reside within L1 (and certainly within L2).
  - hobs 4y ago
    In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.
    - epgui 4y ago
      Note that there are basically no special performance considerations when using CTEs in recent versions of postgres… unless your CTE is recursive or does some weird side-effect (which is unlikely).
      - srcreigh 4y ago
        This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
ergonaught 4y ago
Why is this better than just joining an actual table that expands the codes?
- TylerE 4y ago
  There often isn’t an “actual” table.
- data-ottawa 4y ago
  In this case it isn't. It's better when you need a lookup to multiple columns but will not be needed in any other query/ETL job. Might be steps in a funnel or values in a bespoke customer discount query. Some DBs do allow struct/array fields but joining against a constant CTE is portable and has low overhead.
pophenat 4y ago
Well, if you have multiple queries, you'd still have to write the cte multiple times. You could also use views for the same thing, and those are much more reusable.
- mcdonje 4y ago
  In that case, you might as well use tables. But a lot of people don't have write access for either tables or views.
  - preetamjinka 4y ago
    Yeah. In many cases I've had to use CTEs like this in BI tools. When you're experimenting with datasets for dashboards it's much faster to work with CTEs than to try to make production DB changes.
    - NortySpock 4y ago
      I agree, I'm often using CTEs to organize sub-result-sets because asking for a production database change / view change requires a ticket, and it's just way faster to iterate in a few CTEs in a single query. If, eventually, a few CTE distillations become common enough, then yeah, that's grounds for a request to basically shove that CTE into a view so other people can use it.
pcblues 4y ago
Removing CTEs from your codebase by replacing them with the creation of a temp table and then using separate queries with as few joins as possible to populate it will give you low-locking performance hundreds of times better nearly every time. No problem with readability.
- hobs 4y ago
  I find that anyone who praises chaining CTEs has not dealt with how terrible they can become, each sub table being able to take on any dependency in the parent tables is not a feature of going fast.
  - garfij 4y ago
    I've used CTEs fairly extensively so I'm generally familiar with their pitfalls, but I don't quite grok your point here. Can you go into a little more detail about what you mean by "take on any dependency in the parent tables"?
- pjungwir 4y ago
  I see this pattern all the time in mssql code, but hardly ever elsewhere. I've always wondered if there is a reason for that. Are you coming from that background? I'm curious if anyone knows why it is so favored there? I'm not sure I agree re performance btw. I've fixed a lot of slow mssql queries by changing them in the other direction (EDIT: or just adding a join). I do see how temp tables might shorten the time you hold locks---but then you might be subverting your transaction isolation level.
  - gregw2 4y ago
    I have also seen the performance boost by using separate temp tables rather than CTEs (in my case in Redshift). My hypothesis is that while memory use my be the same either way, you don't have the same transactional/locking requirements/overhead/consistency with multi statements as you do with a single big set of CTEs. And for analytics you rarely need that transactional isolation required by a single big CTE.
charlie0 4y ago
Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL. In what world is this WITH countries (code, name) AS ( ...> SELECT \* FROM (VALUES ...> ('us', 'United States'), ('fr', 'France'), ('in', 'India') ...> ) AS codes ...> ) ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; easier to read than this SELECT code, ...> CASE code ...> WHEN 'us' THEN 'United States' ...> WHEN 'fr' THEN 'France' ...> WHEN 'in' THEN 'India' ...> END AS country ...> FROM data;
- data-ottawa 4y ago
  When you want more than one column
- rco8786 4y ago
  Yea this is a common problem with contrived code samples like these. In my experience the author is right in that real world examples of using CASE can often get out of hand - especially if you need it in more than one place. But the contrived, simple example is clearly easier than the CTE. It is perhaps not surprising that we end up with this sort of stuff in production code, because the original author only needed one simple CASE statement and then it organically grew from there.
- pjot 4y ago
  To me the first example makes me assume that there isn’t yet a `country` table to select from. Hence the `from values()` clause. If you were to run the first query on a fresh db, it’d return data. Running the second would fail.
- santialbo 4y ago
  To me the advantanges comes when you use some sort of query builder. You can pass your list in code rather than having to write the SQL.
galkk 4y ago
CTEs are great, but they often cause issues for optimizers.
VincentEvans 4y ago
For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE. ‘’’ WITH CTE AS ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SELECT * FROM CTE ‘’’ … likewise you can also do that in a subquery that you can immediately join: ‘’’ SELECT * FROM ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SUB INNER JOIN AnotherTable AT ON SUB.code = AT.code ‘’’
- reactivenz 4y ago
  those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) ) you can often alias the VALUES values like: WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) as t(col1_name, col2_name, col3_name) ) and some DB's allow you to alias via the cte name: WITH my_cte(col1_name, col2_name, col3_name) AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) )
  - acjohnson55 4y ago
    In the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.
    - TylerE 4y ago
      In any non trivial code select * is a code smell anyway. Thus parsers don’t spend cycles looking for forms that are unlikely to be useful in real code.
Izkata 4y ago
sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to calculate "b", here's how to do it with CTEs: CREATE TABLE vals (x, y); INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0); WITH m(v) AS ( SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2))) FROM vals ), b(v) AS ( SELECT (SUM(y) - (m.v * SUM(x))) / COUNT(*) FROM vals JOIN m ) SELECT x AS real_x, y AS real_y, m.v AS m, b.v AS b, x * m.v + b.v AS interp_y FROM vals JOIN m JOIN b; real_x real_y m b interp_y ------ ------ ----- ---- ------------------- 1 1 -0.24 1.12 0.88 2 0.5 -0.24 1.12 0.64 3 0.4 -0.24 1.12 0.4 4 0.1 -0.24 1.12 0.16 5 0 -0.24 1.12 -0.0800000000000001 If all you want is "m" and "b" the final query can just be "FROM m JOIN b" and the result will be 1 row.
- acjohnson55 4y ago
  I'd love to see this in blog post form! This seems like a case where it would be nice to be able to pass in a parameter `vals (x, y)`.
  - Izkata 4y ago
    I've tried to get myself writing a few times but never succeeded, so I don't have a blog. But - I am actually doing parameters like so in the view where I copied the above from: CREATE TABLE view_confs (key, value); INSERT INTO view_confs VALUE ('start', 2), ('end', 4); Now prefix it with another one: vals_range(x, y) AS ( SELECT x, y FROM vals WHERE x >= (SELECT value FROM view_confs WHERE key = 'start') AND x < (SELECT value FROM view_confs WHERE key = 'end') ), And swap the rest of the original query to use "vals_range" instead of "vals". Just gotta remember to update view_confs with the new range whenever you want to change the view. I think it should work inside a transaction to avoid multiple threads interfering with each other, but also to be clear: This is a workaround for sqlite not having stored procedures, and not wanting to implement it in code (so I can JOIN to the view in other queries). Better to use stored procedures in other databases than this workaround.
srcreigh 4y ago
This is a great idea if you disable materialization (which the author of this post does not mention). Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine. For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables. with ids as ( select u.user_id, u.token, s.customer_id from users u join stripe_customer s on s.user_id = u.user_id ), user_impressions as ( select i.*, ids.id from impressions i join ids using token ), payments as ( select p.*, ids.id from payments p join ids using customer_id ), select sum(i.views), sum(i.clicks), sum(p.amount) from payments p join user_impressions i using user_id where user_id = $1 group by date; You think, great, this gets some stats for one user since its filtered at the end, and with indexes on stripe_customer(user_id), user_impressions(token), and payments(customer_id), this will be really fast and efficient! Nope.. since ids is referenced more than once in this query, in Postgres this causes the ids CTE to be materialized onto disk with no indexes. So not only does it take up lots of extra space on disk to store all the users in your system, to join user_impressions and payments you have to O(N) search across the ids dataset. No indexes on a materialized table. It would be a lot faster to join to users or stripe_customer or both in a loop with indexes than O(N) search through all the users in your system. This is particularly dangerous because if you remove the payments part, ids only has one reference, so Postgres doesn't materialize the table, and everything is fast. This can be fixed by adding WITH foobar AS NOT MATERIALIZED to the CTE syntax. IMO it should be a syntax error to not specify AS MATERIALIZED or AS NOT MATERIALIZED.. The default has too many potential performance problems and folks should have to think about whether they want materialization or not.
- cyclotron3k 4y ago
  `NOT MATERIALIZED` is now the default in Postgres, and has been since PG12 I believe
  - srcreigh 4y ago
    Only when the CTE is referenced once. If it's referenced more than once, it is automatically materialized.
simonw 4y ago
This is a neat trick. Worth noting that "Row values were added to SQLite version 3.15.0 (2016-10-14)" - my experience is that older versions of SQLite are a lot more common than I would hope they would be. https://www.sqlite.org/rowvalue.html https://www.sqlite.org/rowvalue.html
deleted 4y ago
[deleted]
iblaine 4y ago
CTEs still give me anxiety due to their inability to be potentially not be optimized. I know many database engines today optimize for this type of thing, I'm thankful for it, but there was as time 10+ years ago when CTE expressions could take down databases. I also blame DBT for convincing people that CTEs are good. CTEs are only good if used correctly.
- emodendroket 4y ago
  Well, even if you are unmoved by the readability thing, I don't think there's a non-CTE answer to recursively finding rows.
zX41ZdbW 4y ago
ClickHouse has the `transform` function for this purpose: SELECT transform(id, [1, 2, 11], ['ClickHouse', 'Postgres', 'MongoDB'], '') It creates a lookup table. When you are using the CASE operator, in simple cases it will use the `transform` function under the hood. There are more advanced capabilities: - The `Join` table engine. It is a pre-warmed state for joining, a hash table kept in memory. - Dictionaries. Pre-warmed, automatically updated lookup data structures from various sources. For example, you can connect a dictionary of company names from your operational database while keeping only the ids in ClickHouse. And, just in case, the same CTE works as in SQLite: WITH countries AS ( SELECT c1 AS code, c2 AS name FROM VALUES( ('us', 'United States'), ('fr', 'France'), ('in', 'India'))) SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; Example: milovidov-desktop :) WITH countries AS ( SELECT c1 AS code, c2 AS name FROM VALUES( ('us', 'United States'), ('fr', 'France'), ('in', 'India'))) SELECT data.code, name FROM (SELECT 'us' AS code) AS data LEFT JOIN countries ON countries.code = data.code; ┌─code─┬─name──────────┐ │ us │ United States │ └──────┴───────────────┘ Disclaimer: I'm working on ClickHouse.
layer8 4y ago
In cases like in the example given, I would also consider using a generated column (based on the CASE expression, in the example). That way the CTE doesn’t need to be repeated on each affected query — any query on the table can directly reference the derived value. Of course, this approach is only applicable for row-based values of a single table, and requires you to be able to modify the schema to add the generated column.
emodendroket 4y ago
I've been doing this for ages and am a big evangelist of CTEs. Don't just copy and paste the same hairy sub-query over and over!
shybear 4y ago
Isn't it more readable to make the code snippet with the case when statements into a CTE? I feel like I am misunderstanding the proposed benefits here.
gunshai 4y ago
I loathe using sub queries. I use ctes for everything and do my joins at the end. This seems far more readable.
vlasky 4y ago
This also works in MySQL 8.0.19 and above but the syntax of the CTE example needs to be tweaked to use the ROW() constructor: WITH countries (code, name) AS ( SELECT * FROM (VALUES ROW('us', 'United States'), ROW('fr', 'France'), ROW('in', 'India') ) AS codes ) SELECT data.code, name FROM data LEFT JOIN countries.code = data.code;
rerdavies 4y ago
Sucks for localization though.
nurettin 4y ago
I do this with lateral joins instead.
nbevans 4y ago
CTEs are great for breaking down a large query into discrete steps. They're basically SQL's equivalent of a pipe function.
JacKTrocinskI 4y ago
CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clause. If you're using the same CTE in multiple different queries, then consider creating a view instead. I wouldn’t use a CTE for lookup values, create a lookup table instead.
- alexisread 4y ago
  Not only that, but in Postgres you can do updates in the CTEs, and chain them together eg. WITH CTE1 AS ( SELECT.... ) CTE2 AS ( INSERT....RETURNING insertid ) INSERT INTO TABLE2 insertid as FK, otherdata... The CTEs are executed in define-order as a single complete transaction, no need to use multiple calls to insert relational data
jpalomaki 4y ago
I like CTEs, but for complicated queries I find it lot easier to make them run fast by splitting into individual queries and creating temporary tables with appropriate indexes.

177 points by preetamjinka 4y ago

Occasionally, SQL surprises with bits of composability, such as the fact that using VALUES to specify literal tuples can be used with both INSERT and the FROM clause of a SELECT. Is there any reason syntactically the SELECT needs to be required? If you use a VALUES table literal as a subselect, you have to give it column names with an AS clause (see https://www.postgresql.org/docs/current/sql-values.html https://www.postgresql.org/docs/current/sql-values.html). I can imagine a simpler syntax where you'd do WITH VALUES ... AS table_name (column_1_name, ...). Is there any reason to alias the lookup table as `codes`? There are apparently lots of other clever uses of the WITH clause, such as https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING https://www.postgresql.org/docs/current/queries-with.html#QU...

revskill 4y ago
Yes, it's what i'm doing, example here: https://gist.github.com/revskill10/57ecd8efb72f361b93e6d9d9f8a1bfda https://gist.github.com/revskill10/57ecd8efb72f361b93e6d9d9f... Basically, i could put with: values after the join.
- jpgvm 4y ago
  What is that YAML query language abomination in the other snippets?

msla 4y ago

What does CTE stand for in this context?

advisedwang 4y ago
Common Table Expression
johnvaluk 4y ago
Common Table Expression (the WITH clause).
acjohnson55 4y ago
Common table expression. I don't know exactly where the "common" part comes from, but I'm guessing because you can use its alias multiple times in the subsequent CTEs and the main query.
drx 4y ago
Common table expressions: https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html
adzm 4y ago
common table expression. It's basically like a view that can be defined at the beginning of a query, and can be referenced within that query.
revskill 4y ago
Composable Table Expression.

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 ago
Almost seems like a procedural syntax like source = ... transformed = ... joined = ... final = ...
- acjohnson55 4y ago
  It 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 ago
I 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 ago
  The 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 ago
    I 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_.

fphhotchips 4y ago

Fun fact, this can also be more performant, depending on the engine.

jbrwn 4y ago
CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.
- garfij 4y ago
  My understanding was that Postgres fixed this back in version 12. Are there still limitations here?
  - epgui 4y ago
    Nope, there’s no performance downside to using CTEs in recent postgres versions, unless the CTE is recursive or has side effects (which would be weird).
  - masklinn 4y ago
    Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materialized by default. Also not-mat CTEs can perform a lot worse: https://stackoverflow.com/questions/64016236/postgres-12-materialized-cte-much-faster https://stackoverflow.com/questions/64016236/postgres-12-mat... But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-side-effects-to-postgres-12s-not-materialized-directive https://dba.stackexchange.com/questions/257014/are-there-sid... So the limitations are that it’s very much ymmv.
- no-s 4y ago
  > predicate pushdown is a problem on both MSSQL and Postgresql Not sure you’re completely on target here regarding CTE performance. I don’t have deep insight into Postgresql (but “create temporary view", yeah!). MSSQL does a pretty good job in Sql2019. If the predicate is sargeable in some way pushdown is reliable. If performance is a concern, examining the residuals can lead to significant insights e.g. applying index filtering which solves obvious problems. Recursion is another story. CTE is more likely used by data analyst queries because it is an abstraction of composition. It’s not a great abstraction but it’s better than nothing, which is mostly what you get with SQL.

chaps 4y ago

That's a really neat trick. Gosh, CTEs are great.

cfeduke 4y ago

CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (That point being the size of the tables in question/index/etc.)

bob1029 4y ago
I have strongly encouraged reckless use of CTEs throughout our product. We use in-memory instances of SQLite to evaluate any queries which would leverage CTEs. These datasets are usually small enough to reside within L1 (and certainly within L2).
- hobs 4y ago
  In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.
  - epgui 4y ago
    Note that there are basically no special performance considerations when using CTEs in recent versions of postgres… unless your CTE is recursive or does some weird side-effect (which is unlikely).
    - srcreigh 4y ago
      This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data

ergonaught 4y ago

Why is this better than just joining an actual table that expands the codes?

TylerE 4y ago
There often isn’t an “actual” table.
data-ottawa 4y ago
In this case it isn't. It's better when you need a lookup to multiple columns but will not be needed in any other query/ETL job. Might be steps in a funnel or values in a bespoke customer discount query. Some DBs do allow struct/array fields but joining against a constant CTE is portable and has low overhead.

pophenat 4y ago

Well, if you have multiple queries, you'd still have to write the cte multiple times. You could also use views for the same thing, and those are much more reusable.

mcdonje 4y ago
In that case, you might as well use tables. But a lot of people don't have write access for either tables or views.
- preetamjinka 4y ago
  Yeah. In many cases I've had to use CTEs like this in BI tools. When you're experimenting with datasets for dashboards it's much faster to work with CTEs than to try to make production DB changes.
  - NortySpock 4y ago
    I agree, I'm often using CTEs to organize sub-result-sets because asking for a production database change / view change requires a ticket, and it's just way faster to iterate in a few CTEs in a single query. If, eventually, a few CTE distillations become common enough, then yeah, that's grounds for a request to basically shove that CTE into a view so other people can use it.

pcblues 4y ago

Removing CTEs from your codebase by replacing them with the creation of a temp table and then using separate queries with as few joins as possible to populate it will give you low-locking performance hundreds of times better nearly every time. No problem with readability.

hobs 4y ago
I find that anyone who praises chaining CTEs has not dealt with how terrible they can become, each sub table being able to take on any dependency in the parent tables is not a feature of going fast.
- garfij 4y ago
  I've used CTEs fairly extensively so I'm generally familiar with their pitfalls, but I don't quite grok your point here. Can you go into a little more detail about what you mean by "take on any dependency in the parent tables"?
pjungwir 4y ago
I see this pattern all the time in mssql code, but hardly ever elsewhere. I've always wondered if there is a reason for that. Are you coming from that background? I'm curious if anyone knows why it is so favored there? I'm not sure I agree re performance btw. I've fixed a lot of slow mssql queries by changing them in the other direction (EDIT: or just adding a join). I do see how temp tables might shorten the time you hold locks---but then you might be subverting your transaction isolation level.
- gregw2 4y ago
  I have also seen the performance boost by using separate temp tables rather than CTEs (in my case in Redshift). My hypothesis is that while memory use my be the same either way, you don't have the same transactional/locking requirements/overhead/consistency with multi statements as you do with a single big set of CTEs. And for analytics you rarely need that transactional isolation required by a single big CTE.

charlie0 4y ago

Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL. In what world is this WITH countries (code, name) AS ( ...> SELECT \* FROM (VALUES ...> ('us', 'United States'), ('fr', 'France'), ('in', 'India') ...> ) AS codes ...> ) ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; easier to read than this SELECT code, ...> CASE code ...> WHEN 'us' THEN 'United States' ...> WHEN 'fr' THEN 'France' ...> WHEN 'in' THEN 'India' ...> END AS country ...> FROM data;

data-ottawa 4y ago
When you want more than one column
rco8786 4y ago
Yea this is a common problem with contrived code samples like these. In my experience the author is right in that real world examples of using CASE can often get out of hand - especially if you need it in more than one place. But the contrived, simple example is clearly easier than the CTE. It is perhaps not surprising that we end up with this sort of stuff in production code, because the original author only needed one simple CASE statement and then it organically grew from there.
pjot 4y ago
To me the first example makes me assume that there isn’t yet a `country` table to select from. Hence the `from values()` clause. If you were to run the first query on a fresh db, it’d return data. Running the second would fail.
santialbo 4y ago
To me the advantanges comes when you use some sort of query builder. You can pass your list in code rather than having to write the SQL.

galkk 4y ago

CTEs are great, but they often cause issues for optimizers.

VincentEvans 4y ago

For the sake of completeness, you can accomplish the same using UNION instead of VALUES and subquery instead of CTE. ‘’’ WITH CTE AS ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SELECT * FROM CTE ‘’’ … likewise you can also do that in a subquery that you can immediately join: ‘’’ SELECT * FROM ( SELECT ‘NY’ AS code, ‘New York’ AS state UNION SELECT ‘NJ’, ‘New Jersey’ ) SUB INNER JOIN AnotherTable AT ON SUB.code = AT.code ‘’’

reactivenz 4y ago
those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) ) you can often alias the VALUES values like: WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) as t(col1_name, col2_name, col3_name) ) and some DB's allow you to alias via the cte name: WITH my_cte(col1_name, col2_name, col3_name) AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) )
- acjohnson55 4y ago
  In the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.
  - TylerE 4y ago
    In any non trivial code select * is a code smell anyway. Thus parsers don’t spend cycles looking for forms that are unlikely to be useful in real code.

Izkata 4y ago

sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to calculate "b", here's how to do it with CTEs: CREATE TABLE vals (x, y); INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0); WITH m(v) AS ( SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2))) FROM vals ), b(v) AS ( SELECT (SUM(y) - (m.v * SUM(x))) / COUNT(*) FROM vals JOIN m ) SELECT x AS real_x, y AS real_y, m.v AS m, b.v AS b, x * m.v + b.v AS interp_y FROM vals JOIN m JOIN b; real_x real_y m b interp_y ------ ------ ----- ---- ------------------- 1 1 -0.24 1.12 0.88 2 0.5 -0.24 1.12 0.64 3 0.4 -0.24 1.12 0.4 4 0.1 -0.24 1.12 0.16 5 0 -0.24 1.12 -0.0800000000000001 If all you want is "m" and "b" the final query can just be "FROM m JOIN b" and the result will be 1 row.

acjohnson55 4y ago
I'd love to see this in blog post form! This seems like a case where it would be nice to be able to pass in a parameter `vals (x, y)`.
- Izkata 4y ago
  I've tried to get myself writing a few times but never succeeded, so I don't have a blog. But - I am actually doing parameters like so in the view where I copied the above from: CREATE TABLE view_confs (key, value); INSERT INTO view_confs VALUE ('start', 2), ('end', 4); Now prefix it with another one: vals_range(x, y) AS ( SELECT x, y FROM vals WHERE x >= (SELECT value FROM view_confs WHERE key = 'start') AND x < (SELECT value FROM view_confs WHERE key = 'end') ), And swap the rest of the original query to use "vals_range" instead of "vals". Just gotta remember to update view_confs with the new range whenever you want to change the view. I think it should work inside a transaction to avoid multiple threads interfering with each other, but also to be clear: This is a workaround for sqlite not having stored procedures, and not wanting to implement it in code (so I can JOIN to the view in other queries). Better to use stored procedures in other databases than this workaround.

srcreigh 4y ago

This is a great idea if you disable materialization (which the author of this post does not mention). Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine. For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables. with ids as ( select u.user_id, u.token, s.customer_id from users u join stripe_customer s on s.user_id = u.user_id ), user_impressions as ( select i.*, ids.id from impressions i join ids using token ), payments as ( select p.*, ids.id from payments p join ids using customer_id ), select sum(i.views), sum(i.clicks), sum(p.amount) from payments p join user_impressions i using user_id where user_id = $1 group by date; You think, great, this gets some stats for one user since its filtered at the end, and with indexes on stripe_customer(user_id), user_impressions(token), and payments(customer_id), this will be really fast and efficient! Nope.. since ids is referenced more than once in this query, in Postgres this causes the ids CTE to be materialized onto disk with no indexes. So not only does it take up lots of extra space on disk to store all the users in your system, to join user_impressions and payments you have to O(N) search across the ids dataset. No indexes on a materialized table. It would be a lot faster to join to users or stripe_customer or both in a loop with indexes than O(N) search through all the users in your system. This is particularly dangerous because if you remove the payments part, ids only has one reference, so Postgres doesn't materialize the table, and everything is fast. This can be fixed by adding WITH foobar AS NOT MATERIALIZED to the CTE syntax. IMO it should be a syntax error to not specify AS MATERIALIZED or AS NOT MATERIALIZED.. The default has too many potential performance problems and folks should have to think about whether they want materialization or not.

cyclotron3k 4y ago
`NOT MATERIALIZED` is now the default in Postgres, and has been since PG12 I believe
- srcreigh 4y ago
  Only when the CTE is referenced once. If it's referenced more than once, it is automatically materialized.

simonw 4y ago

This is a neat trick. Worth noting that "Row values were added to SQLite version 3.15.0 (2016-10-14)" - my experience is that older versions of SQLite are a lot more common than I would hope they would be. https://www.sqlite.org/rowvalue.html https://www.sqlite.org/rowvalue.html

deleted 4y ago

[deleted]

iblaine 4y ago

CTEs still give me anxiety due to their inability to be potentially not be optimized. I know many database engines today optimize for this type of thing, I'm thankful for it, but there was as time 10+ years ago when CTE expressions could take down databases. I also blame DBT for convincing people that CTEs are good. CTEs are only good if used correctly.

emodendroket 4y ago
Well, even if you are unmoved by the readability thing, I don't think there's a non-CTE answer to recursively finding rows.

zX41ZdbW 4y ago

ClickHouse has the `transform` function for this purpose: SELECT transform(id, [1, 2, 11], ['ClickHouse', 'Postgres', 'MongoDB'], '') It creates a lookup table. When you are using the CASE operator, in simple cases it will use the `transform` function under the hood. There are more advanced capabilities: - The `Join` table engine. It is a pre-warmed state for joining, a hash table kept in memory. - Dictionaries. Pre-warmed, automatically updated lookup data structures from various sources. For example, you can connect a dictionary of company names from your operational database while keeping only the ids in ClickHouse. And, just in case, the same CTE works as in SQLite: WITH countries AS ( SELECT c1 AS code, c2 AS name FROM VALUES( ('us', 'United States'), ('fr', 'France'), ('in', 'India'))) SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; Example: milovidov-desktop :) WITH countries AS ( SELECT c1 AS code, c2 AS name FROM VALUES( ('us', 'United States'), ('fr', 'France'), ('in', 'India'))) SELECT data.code, name FROM (SELECT 'us' AS code) AS data LEFT JOIN countries ON countries.code = data.code; ┌─code─┬─name──────────┐ │ us │ United States │ └──────┴───────────────┘ Disclaimer: I'm working on ClickHouse.

layer8 4y ago

In cases like in the example given, I would also consider using a generated column (based on the CASE expression, in the example). That way the CTE doesn’t need to be repeated on each affected query — any query on the table can directly reference the derived value. Of course, this approach is only applicable for row-based values of a single table, and requires you to be able to modify the schema to add the generated column.

emodendroket 4y ago

I've been doing this for ages and am a big evangelist of CTEs. Don't just copy and paste the same hairy sub-query over and over!

shybear 4y ago

Isn't it more readable to make the code snippet with the case when statements into a CTE? I feel like I am misunderstanding the proposed benefits here.

gunshai 4y ago

I loathe using sub queries. I use ctes for everything and do my joins at the end. This seems far more readable.

vlasky 4y ago

This also works in MySQL 8.0.19 and above but the syntax of the CTE example needs to be tweaked to use the ROW() constructor: WITH countries (code, name) AS ( SELECT * FROM (VALUES ROW('us', 'United States'), ROW('fr', 'France'), ROW('in', 'India') ) AS codes ) SELECT data.code, name FROM data LEFT JOIN countries.code = data.code;

rerdavies 4y ago

Sucks for localization though.

nurettin 4y ago

I do this with lateral joins instead.

nbevans 4y ago

CTEs are great for breaking down a large query into discrete steps. They're basically SQL's equivalent of a pipe function.

JacKTrocinskI 4y ago

CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clause. If you're using the same CTE in multiple different queries, then consider creating a view instead. I wouldn’t use a CTE for lookup values, create a lookup table instead.

alexisread 4y ago
Not only that, but in Postgres you can do updates in the CTEs, and chain them together eg. WITH CTE1 AS ( SELECT.... ) CTE2 AS ( INSERT....RETURNING insertid ) INSERT INTO TABLE2 insertid as FK, otherdata... The CTEs are executed in define-order as a single complete transaction, no need to use multiple calls to insert relational data

jpalomaki 4y ago

I like CTEs, but for complicated queries I find it lot easier to make them run fast by splitting into individual queries and creating temporary tables with appropriate indexes.