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
orhaving
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.
-
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
<stares in disbelief at console output>
<tries again with the same result>
<frantically double-checks user credentials>
<triggers full database restore from latest backup>
<chews nails>
<tries again, same result>
Oh right, semicolon.
-
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.
-
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 aliasesHuge 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
-
@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.
-
@Bort said in SQL Formatting:
And it needs macros.
-
@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 thenano
/notepad
argument?
-
@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:
- 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
- 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.
- 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.
-
@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.
-
@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.
=(
-
@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.
-
@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 ;)
-
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 withAS
, in pascalcase, no length limit - Every condition in
WHERE
orHAVING
has its own line, withAND
/OR
at the beginning
-
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?
-
@RaceProUK
No, there's just one right way to do it. I just posted it.
-
@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 isbest@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
, orfull
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
andor
, 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 byCAPITALIZATION
. 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.
-
@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
-
@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), .
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
-
@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"