For me, it’s CTE’s. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.

What construct do you like using on a daily basis?

  • @deegeese@sopuli.xyz
    link
    fedilink
    71 year ago

    Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.

    • luciole (he/him)
      link
      fedilink
      21 year ago

      I believe recursive CTEs are pretty cool for tree traversal. Anytime you’ve got a table with a foreign key on its own primary key they might come in handy.

  • key
    link
    fedilink
    41 year ago

    Subqueries in Subqueries in Subqueries

    • @megaman1970@beehaw.orgOP
      link
      fedilink
      11 year ago

      Those work, but require a lot of careful structuring to get right, and can be a pain to debug. With a CTE, you can just call on the intermediate steps to trace down problems.

  • ddh
    link
    41 year ago

    CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.

  • Leo Uino
    link
    21 year ago

    Yes, CTEs are awesome. Especially when you don’t force materialization and the optimizer can work its magic.

    I’ve had a lot of fun with window functions as well.

  • @szeis4cookie@lemmy.world
    link
    fedilink
    21 year ago

    I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it’s just nicer and easier to read the intermediate step than as a subquery

  • @itty53@vlemmy.net
    link
    fedilink
    2
    edit-2
    1 year ago

    You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.

    Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.

    Example

    CREATE VIEW AS const.OrderType
    SELECT 
    CAST (1 as 'New'),
    CAST (2 as 'Pending'),
    CAST (3 as 'Shipped')
    GO
    
    -- Assuming a table with OrderId and OrderTypeId
    
    SELECT         o.OrderId 
    FROM           dbo.Orders AS o
    CROSS JOIN const.OrderType AS ot
    WHERE         o.OrderTypeId = ot.[Pending]
    
    -- Only returns orders where TypeId = 2
    
  • DarkAngelofMusic
    link
    fedilink
    01 year ago

    CTEs can be useful, particularly in PostgreSQL, where there are writable CTEs, but a lot of the time, I prefer using temp tables over CTEs, as they often perform better for larger datasets. I think one of my favorite constructs is window functions. I’ve found many uses for them, over the years.

    • @megaman1970@beehaw.orgOP
      link
      fedilink
      11 year ago

      There was one time a good while back where I used window functions to perform edge detection in a dataset. I’ll see if I can dig up that query later.