SQL Formatting


  • โ™ฟ

    I feel like SQL formatting angst gets short shrift as opposed to tabs / spaces and braces. Here's my personal style guide (beginning with an example that illustrates a lot of it):

    select
      f.name fooname,
      b.quz,
      sum( b.frags ) total_frags
    from footable f
    join bartable b on b.id = f.bar_id
    left join baztable bz
      on bz.bar_id = b
      and bz.fizz is null
    where f.flarg > 2
      and bz.id is null
    group by
      f.name,
      b.quz
    

    So rules here:

    • Lowercase
    • Selected (or group by) fields on their own lines, indented (I've used two spaces here, but I'm flexible on that)
    • Do not indent first line from, join, where, etc
    • Conditions (in joins or where or having clauses) go on their own line.
    • If a join has multiple conditions then start the on on a new line, indented
    • Don't bother with superfluous as for aliases
    • If you write a subquery or CTE do not name it some variation on temp

    I'm sure this isn't an exhaustive list.


  • SockDev

    Personally, I prefer

    SELECT    F.name AS fooname,
              B.quz,
              sum(B.frags) AS total_frags
    FROM      footable F
              INNER JOIN bartable B ON B.id = F.bar_id
              LEFT JOIN baztable BZ ON BZ.bar_id = B.id AND BZ.fizz IS NULL
    WHERE     F.flarg > 2
    AND       BZ.id IS NULL
    GROUP BY  F.name, B.quz;
    


  • @boomzilla
    clatter <stares in disbelief at console output>
    clatter <tries again with the same result>
    clatter <frantically double-checks user credentials>
    clatter <triggers full database restore from latest backup>
    clatter <chews nails>
    clatter <tries again, same result>
    clatter Oh right, semicolon.


  • area_pol

    Here's my personal style guide

    Oh right, semicolon.

    I would prefer if it was processed and stored as an abstract syntax tree, leaving the way it is displayed to the editor implementation and user settings.
    There would be no style guides or holy wars needed. And it would also decisively kill SQL injections!



  • @RaceProUK Another personal preference:

    SELECT    F.name AS fooname
              , B.quz
              , sum(B.frags) AS total_frags
    FROM      footable F
              INNER JOIN bartable B
    	  	  ON B.id = F.bar_id
              LEFT JOIN baztable BZ
    	       	 ON BZ.bar_id = B.id
    		AND BZ.fizz IS NULL
    WHERE     F.flarg > 2
    AND       BZ.id IS NULL
    GROUP BY  F.name
          	  , B.quz;
    

    By placing the connectors (commas, AND, etc.) first on the line, the query is still valid SQL if you comment out some of these lines.


  • kills Dumbledore

    I mostly agree, but I go for caps on keywords, I like the as to make it more explicit (especially with caps, it stands out nicely) and I always put join conditions on their own line



  • I use something similar to @boomzilla's example, just with the "as" keywords.

    Some of my co-workers though seem to think that SQL breaks if you use line breaks in your queries. It results in some... interesting... examples.



  • @boomzilla said in SQL Formatting:

    Lowercase

    I do all caps keywords, since it makes command stand out from columns/tables. (See callilaAccalia Beta's example)

    Selected (or group by) fields on their own lines, indented (I've used two spaces here, but I'm flexible on that)

    Yes. I'm torn on indenting ON or leaving it inline, but I'm good either way.

    Do not indent first line from, join, where, etc

    I actually like a double-indent on the first WHERE clause. It makes all the clauses line up and the AND/OR sit in a "margin".

    Conditions (in joins or where or having clauses) go on their own line.

    Yes. Fuck people who jam together multiple conditions. It's infinitely harder to read.

    "But that's how the autogenerated query looks"

    {eye twitch} Did you saw autogenerated? {gets baseball bat}

    If a join has multiple conditions then start the on on a new line, indented
    Don't bother with superfluous as for aliases

    Huge disagree. If there's an alias, I make my developers explicitly write the "as" clause. It makes it easier to see the alias, it makes the developers "think" in alias (TABLE Name as alias), but most importantly... well, here's a CodeSOD that's bitten me (or developers around me) more than once:

    SELECT a, b -- This could easily be in your code instead of here, where you'll get an instant error
    FROM
    (
        SELECT a b
        FROM InnerTable
        WHERE SomeDumbClause = 1
    ) as x
    
    

    That is syntax correct, but will throw an error. Fuck forbid it was only "SELECT a b", and your devs waste a bunch of time tracing down a generic error to an uncaught exception to a deep deep class that says DataRow("b")...

    If you write a subquery or CTE do not name it some variation on temp

    Yes. Short, meaningful names. I'm okay with single character aliases, though I prefer some sort of WishItWasHungarian just for ease of reading.

    SELECT dfr.*
    FROM DogFuckerRegistry as dfr
    WHERE FucksDogs = 1    
    

    fake edit
    Screenshot on my preferred indenting, since NodeBBMarkdownLOLBBQ. Redlines mine to demonstrate how everything lines up, so it's easy to just scan down the query and see the important parts. As a dev, you should fucking know just by looking at the structure that SELECT is first, FROM is second, JOIN is third, WHERE is fourth-- those keywords could be fuzzed out and you should still be able to read the query.


  • โ™ฟ

    @Lorne-Kates said in SQL Formatting:

    " Don't bother with superfluous as for aliases"

    Huge disagree. If there's an alias, I make my developers explicitly write the "as" clause. It makes it easier to see the alias, it makes the developers "think" in alias (TABLE Name as alias), but most importantly... well, here's a CodeSOD that's bitten me (or developers around me) more than once:

    SELECT a, b -- This could easily be in your code instead of here, where you'll get an instant error
    FROM
    (
        SELECT a b
        FROM InnerTable
        WHERE SomeDumbClause = 1
    ) as x
    
    

    That is syntax correct, but will throw an error. Fuck forbid it was only "SELECT a b", and your devs waste a bunch of time tracing down a generic error to an uncaught exception to a deep deep class that says DataRow("b")...

    I guess my rule of putting columns on their own lines goes a long way towards catching this visually.

    If you write a subquery or CTE do not name it some variation on temp

    Yes. Short, meaningful names. I'm okay with single character aliases, though I prefer some sort of WishItWasHungarian just for ease of reading.

    SELECT dfr.*
    FROM DogFuckerRegistry as dfr
    WHERE FucksDogs = 1    
    

    I would call that a mnemonic abbreviation, which what I typically use. BUT YOU FORGOT TO SPECIFY THE TABLE FOR FucksDogs!!!!111


  • SockDev

    @Lorne-Kates said in SQL Formatting:

    Fuck people who jam together multiple conditions.

    Only if you buy me dinner first ๐Ÿ˜‰



  • I write it however I want and let MySQL Workbench format it.



  • @x86tux said in SQL Formatting:

    By placing the connectors (commas, AND, etc.) first on the line, the query is still valid SQL if you comment out some of these lines.

    I've been doing that in my C++ code for a while for exactly the same reason. Also adding a new one results in a 1 line diff instead of a 2 line diff.



  • @Adynathos And it needs macros. Every language needs macros.


  • SockDev

    @Bort said in SQL Formatting:

    And it needs macros.

    0_1479925108999_What Is This.gif

    0_1479925129218_Nope.gif


  • โ™ฟ

    @boomzilla said in SQL Formatting:

    I'm sure this isn't an exhaustive list.

    I'm sure. Why isn't this in The Garage next to the nano/notepad argument?

    ๐Ÿ 


  • Discourse touched me in a no-no place

    @Bort said in SQL Formatting:

    Every language needs macros.

    Just preprocess it with m4 before compilation. All the macros you could ever desire! ๐Ÿšป



  • @accalia You more of a Fexpr person?



  • @RaceProUK said in SQL Formatting:

    Personally, I prefer

    SELECT    F.name AS fooname,
              B.quz,
              sum(B.frags) AS total_frags
    FROM      footable F
              INNER JOIN bartable B ON B.id = F.bar_id
              LEFT JOIN baztable BZ ON BZ.bar_id = B.id AND BZ.fizz IS NULL
    WHERE     F.flarg > 2
    AND       BZ.id IS NULL
    GROUP BY  F.name, B.quz;
    

    Mine looks similar, but with three difference:

    1. SELECT get it's own line, so it'd be easier to let me do column number verification when using INSERT...SELECT... statement for recreating tables
    2. the INNER JOIN and LEFT JOIN don't get indented in hope that the conditions at the end of clause could be kept on the same line while still can be fully visible on the screen without scrolling.
    3. All conditionals in WHERE clause will be parenthesised regardless of whether it's really necessary, to avoid "operator precedence" related bugs.


  • @boomzilla said in SQL Formatting:

    I guess my rule of putting columns on their own lines goes a long way towards catching this visually.

    That could do it. You're still relying on proper comma placement.

    @boomzilla said in SQL Formatting:

    BUT YOU FORGOT TO SPECIFY THE TABLE FOR FucksDogs!111

    I have failed.



  • @RaceProUK said in SQL Formatting:

    @Lorne-Kates said in SQL Formatting:

    Fuck people who jam together multiple conditions.

    Only if you buy me dinner first ๐Ÿ˜‰

    Only if you test clean for Oracle. I've got my health to think of.


  • SockDev

    @Lorne-Kates does that include MySQL?



  • @Arantor said in SQL Formatting:

    @Lorne-Kates does that include MySQL?

    You can beat the MySQL out of someone with enough time and candlewax.

    Oracle is-- well-- a stain.


  • SockDev

    @Lorne-Kates Oracle owns MySQL these days...



  • @Arantor said in SQL Formatting:

    @Lorne-Kates Oracle owns MySQL these days...

    Oh god, it's like finding out that child star who turned into a troubled teen then into a wreck of a 20s but was turning her life around is actually still a 15 year old Hungarian sex worker who is kept chained up and drugged 24/7 for the pleasure of members of The International Consortium of Stinky Men Who Have AIDS And Leaky Anuses.

    ๐Ÿ˜ž


  • SockDev

    @Lorne-Kates so, presumably we have to be tested for Oracle and MySQL first, good to know.



  • @Arantor said in SQL Formatting:

    @Lorne-Kates so, presumably we have to be tested for Oracle and MySQL first, good to know.

    I guess it'll depend on which strain of MySQL. If it's one of the older strains, we have a cure. If it's the new mutated strain-- umm-- well, we also have a cure. It's right there in that room marked "Human Incinerator". Have a seat there, and I'll be right by to administer the treatment.



  • @Arantor said in SQL Formatting:

    we have to be tested for Oracle and MySQL

    If you find you have MySQL on you, the cure is known now. It's called MariaDB.



  • @TimeBandit said in SQL Formatting:

    @Arantor said in SQL Formatting:

    we have to be tested for Oracle and MySQL

    If you find you have MySQL on you, the cure is known now. It's called MariaDB.

    I went with someone and wanted to make sure she was clean & untouched. I thought I could trust her because she said she used NoDB.

    I need so much penicillin...



  • @Lorne-Kates said in SQL Formatting:

    I went with someone and wanted to make sure she was clean & untouched. I thought I could trust her because she said she used NoDB.

    Was she web-scale ?



  • @TimeBandit said in SQL Formatting:

    @Lorne-Kates said in SQL Formatting:

    I went with someone and wanted to make sure she was clean & untouched. I thought I could trust her because she said she used NoDB.

    Was she web-scale ?

    She bragged she was, and then tried to take on too big of a load from too many users at once.


  • SockDev

    @Lorne-Kates said in SQL Formatting:

    Only if you test clean for Oracle. I've got my health to think of.

    Unfortunately, I came into contact with it at uni.

    Oh well. I'm sure you'll find someone else ๐Ÿ˜‰


  • area_pol

    SELECT
        foo.name AS FooName,
        bar.quz AS Quz,
        SUM(bar.frags) AS TotalFrags
    FROM
        footable foo
        JOIN bartable bar ON bar.id = foo.bar_id
        LEFT JOIN baztable baz ON baz.bar_id = bar.id AND baz.fizz IS NULL
    WHERE
        foo.flarg > 2
        AND baz.id is null
    GROUP BY
        foo.name,
        bar.quz
    
    • Keywords and functions are uppercase
    • Data types are lowercase
    • Main keywords (SELECT, FROM, WHERE, etc) have their own lines which are the only lines not indented
    • Every JOIN occupies one line
    • Joining columns are always specified
    • Every table is aliased, in lowercase, alias should remind of the table, no length limit
    • Every SELECT item is aliased with AS, in pascalcase, no length limit
    • Every condition in WHERE or HAVING has its own line, with AND/OR at the beginning

  • SockDev

    Is it just me, or is SQL formatting one of those things where there's a thousand right ways to do it, and no-one can agree which is best?


  • area_pol

    @RaceProUK
    No, there's just one right way to do it. I just posted it.


  • Impossible Mission Players - A

    @RaceProUK said in SQL Formatting:

    Is it just me, or is SQL formatting one of those things where there's a thousand rightworst ways to do it, and no-one can agree which is best@mikeTheLiar ?

    FTFY



  • @RaceProUK said in SQL Formatting:

    @Lorne-Kates said in SQL Formatting:

    Only if you test clean for Oracle. I've got my health to think of.

    Unfortunately, I came into contact with it at uni.

    Oh well. I'm sure you'll find someone else ๐Ÿ˜‰

    Everyone experiments during university.



  • @RaceProUK said in SQL Formatting:

    Is it just me, or is SQL formatting one of those things where there's a thousand right ways to do it, and no-one can agree which is best?

    Is it just me, or is SQL formatting one of those things where no-one can agree which way is best, even though there's a thousand right ways to do it?



  • @TimeBandit said in SQL Formatting:

    @Lorne-Kates said in SQL Formatting:

    I went with someone and wanted to make sure she was clean & untouched. I thought I could trust her because she said she used NoDB.

    Was she web-scale ?

    You know what's webscale? My cock.

    it's soft-state, not necessarily available, and doesn't guarantee finalizing the transaction



  • My personal style is kind of flexible, prioritizing internal consistency.

    • Keyword case can be either upper or lower, but must be the same throughout the query.
    • Fields stay on the same line, but only until the line becomes long, then it breaks to a new indented line.
    • Conditions stay on the same line until long, then breaks before the conjunction to a new indented line.
    • Joins should include inner, left, right, or full specifiers. Comma joins are used very rarely.
    • If multiple conditions use similar constructions, then the similarities should be ordered the same way and ought to align or nearly align vertically with each other.
    • If conditions contain both and and or, then parentheses will be used, even if unnecessary.
    • Subqueries should be indented so each clause starts in the same text column as the "sub"-select (which may begin either on the same line as the opening parenthesis or indented on the next line).
    • Each closing parenthesis should either be on the same line or the same column as its matching opening parenthesis.
    • Conditions that break over into a new line should have the conjunctions indented on the next line with the conditional fields starting in the same text column.
    • Indents can be either tabs or spaces, but must be consistent, except to match the indenting rules above.
    • as for aliases is optional (but personally unused), and aliases should be made clear, either by "quotes" (or [brackets], as applicable) or by CAPITALIZATION. Table aliases to distinguish separate tables that are joined multiple times either use a short mnemonic or have a number appended to the end.
    • Above all, if a query has something a certain way, that should continue throughout the rest of the query.

    (A long line is approximately half to two-thirds of the way across the screen, but may be longer or shorter.)


    Edit: I use both Oracle SQL Developer and Microsoft SQL Server Management Studio at work. We have databases on both. The older ones are Oracle, and the newer ones are SQL Server.


  • @Lorne-Kates said in SQL Formatting:

    @RaceProUK said in SQL Formatting:

    Is it just me, or is SQL formatting one of those things where there's a thousand right ways to do it, and no-one can agree which is best?

    Is it just me, or is SQL formatting one of those things where no-one can agree which way is best, even though there's a thousand right ways to do it?

    SQL formatting is one of those things where everyone does it in whatever way works best for them, so unsurprisingly there are a thousand different ways of doing it and everyone thinks theirs is best. And it is, for them.

    It's only a problem when one person tries to push their way of doing it onto someone else.

    Personally, I prefer a much more compact form than most of the people who've posted theirs.

    • Lower case for everything.
    • In the select clause, each line holds as many fields as it can. I don't indent additional lines; keyword highlighting lets me pick out the end of the select list easily enough.
    • Table aliases are usually initialisms of one or two characters, may be more if required, may be more expressive if required, especially to distinguish between copies of the same table (e.g. I might have p for period and pnext for the following period).
    • Table and column aliases usually don't use "as". Subquery aliases are more descriptive and often use "as".
    • In the from clause, each join gets its own line. Where possible, the join conditions should be on the same line as the join statement, even for multiple conditions; if they won't fit, break at a condition. Multiple conditions are always enclosed in parentheses, but single conditions are not.
    • I always include "inner" or "outer" in the join specification.
    • I only indent in the following cases:
      • expression or join clause doesn't fit on one line
      • subqueries
    • In the where clause, related conditions can go on the same line but unrelated conditions generally go on a different line, unless they're really short.
    • Group by and order by clauses follow the same style as the select clause. Having clauses follow the same style as the where clause.

    But then, I use and generally like TOAD, so my opinions are clearly not worthy of any respect.


  • area_deu

    @x86tux said in SQL Formatting:

    By placing the connectors (commas, AND, etc.) first on the line, the query is still valid SQL if you comment out some of these lines.

    So? If you place the comma first, you can't comment out the first line. If you don't, you can't comment out the last line. I don't see the advantage.
    Plus comma-first looks like shit.



  • @RaceProUK said in SQL Formatting:

    Is it just me, or is SQL formatting one of those things where there's a thousand right ways to do it, and no-one can agree which is best?

    It's just you. Everybody knows the only right way is @boomzilla 's way ๐Ÿ‘ด๐Ÿป


  • Notification Spam Recipient

    @djls45 said in SQL Formatting:

    (A long line is approximately half to two-thirds of the way across the screen, but may be longer or shorter.)

    Please never, ever write a spec



  • This post is deleted!


  • @cark said in SQL Formatting:

    @djls45 said in SQL Formatting:

    (A long line is approximately half to two-thirds of the way across the screen, but may be longer or shorter.)

    Please never, ever write a spec

    I was trying to describe something subjective, but using language tends to require that objective terms be used.



  • @boomzilla I use something similar, except for the position of commas:

    select
        f.name fooname
      , b.quz
      , sum( b.frags ) total_frags
    from footable f
    join bartable b on b.id = f.bar_id
    left join baztable bz
      on bz.bar_id = b
      and bz.fizz is null
    where f.flarg > 2
      and bz.id is null
    group by
        f.name
      , b.quz
    


  • All of you comma-first people are bad, bad people. And those with the whole query left-aligned to the right of the keywords (@RaceProUK I'm looking at you), undefined undefined.

    Lowercase keywords are an affront to the natural order of things, likewise using camelCase or PascalCase anywhere in the query will see you damned to a special kind of hell.

    So far @boomzilla is closest in form to the One True Way, but there is still room for improvement.

    Of course, I work with Oracle, so everything is case-insensitive, empty string IS NULL, and VARCHAR doesn't VAR. So maybe I've been brain-damaged.


  • โ™ฟ

    @another_sam said in SQL Formatting:

    Of course, I work with Oracle


  • Discourse touched me in a no-no place

    @another_sam So what's wrong with just putting it all on one line? It's not like the ORM needs to put in extra whitespace or anythingโ€ฆ

    ๐Ÿน


  • โ™ฟ

    @dkf said in SQL Formatting:

    @another_sam So what's wrong with just putting it all on one line? It's not like the ORM needs to put in extra whitespace or anythingโ€ฆ

    ๐Ÿน

    C string literals give you the best of both worlds!

    "single-"
    "line "
    "string"
    

Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.