• xmunk@sh.itjust.works
    link
    fedilink
    arrow-up
    18
    ·
    edit-2
    1 year ago

    Sorry, to clarify, not everything is in all caps. I’ll append my prefered syntax below

    WITH foo AS (
        SELECT id, baz.binid
        FROM
                bar
            JOIN baz
                ON bar.id = baz.barid
    )
    SELECT bin.name, bin.id AS binid
    FROM
            foo
        JOIN bin
            foo.binid = bin.id
    

    The above is some dirt simple SQL, when you get into report construction things get very complicated and it pays off to make sure the simple stuff is expressive.

      • xmunk@sh.itjust.works
        link
        fedilink
        arrow-up
        9
        ·
        1 year ago

        I’ve seen both approaches and I think they’re both quite reasonable. An indented join is my preference since it makes sub queries more logically indented… but our coding standards allow either approach. We’ve even got a few people that like

        FROM foo
        JOIN bar ON foo.id = bar.fooid
        JOIN baz ON bar.id = baz.barid
        
    • Cold_Brew_Enema@lemmy.world
      link
      fedilink
      arrow-up
      1
      ·
      1 year ago

      Um you forgot the semicolon before with assuming there isn’t one in the previous statement. Syntax error. Code review failed

      • xmunk@sh.itjust.works
        link
        fedilink
        arrow-up
        5
        ·
        1 year ago

        There’s no way we’re running in multi statement mode… I like my prepared queries, thank you very much.