3 ms·
> Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query
by fifilura 2y ago
> Consider that one wouldn't complain as much about a super fucked up nested query (and resulting SQL syntax woes) if it wasn't necessary to write such a query in the first place.
And in "modern" SQL this is solved with CTEs. Use them to unwrap the schema the way you want it first, before implementing the business logic.
- larodi 2y agothis is poor man's SQL to unwrap for business logic. SQL is such more more about everything else and so litte about the trouble with mapping business logic into storage.
- bbkane 2y agoCould you please add some examples? I'm still living in poor man's SQL land
- solumunus 2y agoPlease elaborate.
- larodi 2y agoCTEs can indeed align mismatched and denormalised storage to some E/R which follows some business logic. That much you can say. But CTEs allow for recursion and save time when you need complexity but you want to follow a precise step-by-step reasoning. This dis not arise as a need to solve business mess, but as a need to skip DDL for views/mviews. You can have arbitrary dimensions sliced in CTEs which does not immediately imply a goal of business schema alignment. Besides the top SQL devs I’ve met don’t lose time to align schema at all, but write the DB table names and columns as they are, because well… because they were usually the people who created the mess this way. Many reports such as month-to-month increase of sales or other statistical stuff is much more readable when implemented with CTes. Besides - older versions of DB software didn’t always have window functions or proper ranking, so problems such as top-n were not (and in some occasions still are not) trivial to write unless view/cte is used. We talking pages of single query here, not textbook examples. So is really about dice and slice in a convenient way, but less about business schema or the ideal E/R
- fifilura 2y agoMaybe I misunderstood, and if I did it makes for an interesting discussion, but your explanation is mainly about what CTEs can be used for not what they can not be used for. In my experience, normalizing tables is mostly for simplifying inserts. When you write business logic it is rather the opposite, you want non-normalized data because this is where the data is all in one place. And this de-normalization is a great use of CTEs. As well as a general cleanup of the input data, which was my original point.
- solumunus 2y agoThat makes sense. I misunderstood your OP.
- fifilura 2y agoI am happy you understood! I still feel this discussion is above my head, so if you are still there i would love an explanation!