Why most developers stop learning SQL at subqueries - a 5-minute guide for PARTITION BY and CTEs

Comments

• abhibeckert@lemmy.world ⁨8⁩ ⁨months⁩ ago

  Stage 5 can give you a huge boost in writing complex queries!

  You say that like it’s a good thing. I like my queries simple. Also - the stuff you have under “stage 6” should be “stage 2” in my opinion.

  source

  • fmstrat@lemmy.nowsci.com ⁨8⁩ ⁨months⁩ ago

    Huh? How do you write an efficient query and not take into account joins? A single table query? Are you a Prisma fan?

    source

    • FooBarrington@lemmy.world ⁨8⁩ ⁨months⁩ ago

      Are you a Prisma fan?

      Ooooh, that’s a good burn

      source
  • Lmaydev@programming.dev ⁨8⁩ ⁨months⁩ ago

    Joining is one of the simplest and most useful things in SQL.

    How can you possibly query without it?

    source
• mvirts@lemmy.world ⁨8⁩ ⁨months⁩ ago

  I feel like the word “partitions” used in this article should be “window functions”

  source

  • GBU_28@lemm.ee ⁨8⁩ ⁨months⁩ ago

    Good point. Window functions are mandatory knowledge for intermediate users

    source
• AngryishHumanoid@reddthat.com ⁨8⁩ ⁨months⁩ ago

  I imagine it depends on use case. Anything more advanced that subqueries/CTEs is largely the domain of ETL developers (not exclusively, obviously) but most others using SQL don’t need anything that advanced.

  source
• GBU_28@lemm.ee ⁨8⁩ ⁨months⁩ ago

  CTEs enhance readability, documentation, and testing imo. I’m very pro CTE whenever a subquery isn’t the obvious choice.

  source
• howrar@lemmy.ca ⁨8⁩ ⁨months⁩ ago

  It’s always bothered me when I write subqueries that have to be run on each row. Good to know that there’s a better way.

  source
• PlutoniumAcid@lemmy.world ⁨8⁩ ⁨months⁩ ago

  I have to create a fucking account just to fucking read your article?! Kthxbye!

  source

  • mac@programming.dev ⁨8⁩ ⁨months⁩ ago

    Image

    source

    • Lmaydev@programming.dev ⁨8⁩ ⁨months⁩ ago

      Lol

      But just that popup existing is irritating.

      source
• schema@lemmy.world ⁨8⁩ ⁨months⁩ ago

  Very useful. I’ll definitely going to use the ranking stuff.

  source
• Lmaydev@programming.dev ⁨8⁩ ⁨months⁩ ago

  CTEs are amazing. I discovered them semi recently and it’s such a great tool to have in your belt.

  Partitioning is definitely something I need to work on.

  source