
Writing more legible SQL - craigkerstiens
http://www.craigkerstiens.com/2016/01/08/writing-better-sql/
======
combatentropy
Here is how I write SQL:

    
    
        select
            t1.col1,
            t2.col2,
            t3.col3
        from table1 t1
            join table2 t2 on t1.col2 = t2.col1
            join table3 t3 on
                t1.col3 = t3.col1 and
                t3.col2 = something_else
        where
            t1.col1 > 0 and
            t2.col2 <> t1.col4
        order by col2
        limit 100
    

So:

1\. SQL capitalization is not sacred. I lowercase everything.

2\. I just indent subclauses, with four spaces, like I indent other languages.
I don't go out of my way to line up things vertically.

3\. Conjunctions like "and" are put at the end of a line, like a comma, not at
the beginning. I think it lines things up better vertically.

4\. I try to keep lines short, but I also try to keep it from getting
needlessly long vertically. So if the statement is short and simple, I might
fold some things back onto one line, like:

    
    
        select col1, col2, col3
        from table1
        where col1 = something_or_other
    
    

or even:

    
    
       select * from table1 where col1 = something

~~~
vog
I fully agree with the sentiment, and especially with treating SQL code as
code, because it really is code. My style differs in 4 regards:

1\. "join"s are at the same level as "from", and the contents of from/join are
indented

2\. prefer tuple comparisons of multi-comparisons, i.e. "(a,b)=(c,d)" instead
of "a=c and b=d"

3\. operators auch as "and" are at the beginning of a line, as in style guide
of almost all other programming languages, too

4\. the contents of "order by" and "group by" are also indented, the same way
as the "select" columns

Example:

    
    
        select
            t1.col1,
            t2.col2,
            t3.col3
        from
            table1 t1
        join
            table2 t2 on t1.col2 = t2.col1
        join
            table3 t3 on (t3.col3,  r3.col2) = (t1.col3, something_else)
        where
            t1.col1 > 0
            and t2.col2 <> t1.col4
        order by
            col2 asc,
            col1 desc
        limit 100

~~~
gerhardi
I always keep the joins indented from the table that they are joined to:

    
    
      select    t1.col1,
                t2.col2,
                t3.col3,
                t4.col4
      from      table1 t1
                    join table2 t2 on t2.colx = t1.colx
                    join table3 t3 on t3.coly = t1.coly
                        join table4 t4 on t4.colz = t3.colz
                    join tablen tn on tn.coln = t1.coln
      where     tn.colnx in (...)
    
                -- Table 3 and Table 2 have some values while Table n value is not something OR Table n has value that is exactly something
                and (
                        (t3.col = x and t2.col = y and tn.col != z)
                        or 
                        tn.col = z
                    )
      ...
    

So in above you can see by the indent on which table some other table is
joined to. Tables t2, t3 and tn are related to t1, but t4 is related to t3,
not directly t1.

~~~
vog
_> indented from the table that they are joined to_

There is not always "the" table.

How does your coding style work if you join into multiple tables? (because in
reality you join the new table with the joined result of the previous tables,
and hence can reference any combination of any previous table columns, unless
you group your joins with parentheses)

    
    
      select
          ...
      from
          table1 t1
      join
          table2 t2 on t2.colx = t1.colx
      join
          table3 t3 on (t3.coly, t3.colz) = (t1.coly, t2.colz)

~~~
gerhardi
What you are saying is correct. In this case I would have the join of t3 on
the same indent level as the join of t2 as t3 table has also the common key
with t1. Somehow I just find the parent comment style not so intuitive for me.

------
msluyter
A couple of jobs ago, I worked at a company that did a ton of SQL and we used
right justified keywords, which looks like:

    
    
        select t1.col1,
               t2.col2,
               t3.col3
          from table1 t1
               join table2 t2 on t1.col2 = t2.col1
               join table3 t3 on t1.col3 = t3.col1 
                and t3.col2 = something_else
         where t1.col1 > 0 
           and t2.col2 <> t1.col4
         order by col2
         limit 100
    

Initially it seemed weird to see the ragged left edge, but over time I got
used to it and grew to prefer it. It doesn't really hold up for extremely
complex queries with lots of subqueries because everything tends to start
drifting too far to the right (unrealistic example below):

    
    
         select *
           from blah b
          where b.whatever in (
              select blah 
                from c
               where c.whatever = in (
                   select blah 
                     from d
                    where d.whatever = 'foo'
               )
          )
    

I almost never see this style used these days, and I was curious if anyone
else does it this way?

~~~
howItsDone123
I use this style. I work with queries that are a little bigger than average,
and it's very difficult to keep track of it unless it's well laid out.

conditionals, loops, etc, get formatted like they would in a procedural
language. the DML gets formatted similarly to what you've posted.

a not unrealistic example from this morning is as follows:

    
    
      if (select object_id('aTableName', 'u')) is not null begin
        select column
          from aTableName
         where thisIsTrue = 1
               and somethingIs in (select aList from aListTable)
               and somethingElse = (
                 select aValue
                   from aValueTable
                  where aValue = 'somethingImportant'
               )
      end else begin
        print('Oh noes! I couldn't find somethingImportant or something in someList form aTable!')
      end
    

we work with huge amounts of data with systems and processes that don't lend
themselves to other methods (yet... it's coming.) so we have to make SQL as
readable as possible.

edit: yes... i know... subqueries are bad... it's just an example. those would
have been done in joins or as temporary variables or something else.

------
mythz
I also use UPPER case for SQL Keywords to visually distinguish them from user-
defined names (i.e. similar purpose to syntax coloring), but I don't collapse
SELECT over multiple lines and line up each statement so the conditions line-
up, e.g:

    
    
        SELECT foo, bar
          FROM baz
         WHERE foo > 3
           AND bar = 'craig.kerstiens@gmail.com'

~~~
alistairbayley
Re uppercase: can't afford a syntax-highlighting editor?

~~~
Sean1708
It's true that there are _some_ editors which highlight SQL inside strings,
but most don't.

------
the_ez
sqlparse comes to mind -
[https://github.com/andialbrecht/sqlparse](https://github.com/andialbrecht/sqlparse)

I know the maintainer, they also run a service at
[https://sqlformat.org/](https://sqlformat.org/)

You can try it on the command line e.g. on Fedora:

    
    
      $ sudo dnf install python3-sqlparse
      $ echo "select c1, c2, c3 from table1 join table2 t2 on t1.col2 = t2.col1 where 1=2 order by col2 limit 100" | sqlformat -k upper -i lower -sa -
      SELECT c1,
             c2,
             c3
        FROM table1
        JOIN table2 t2
          ON t1.col2 = t2.col1
       WHERE 1 = 2
       ORDER BY col2
       LIMIT 100

------
nerdponx
Am I the only one who puts commas on the next line? I think of them as an
operator analogous to AND or ON:

    
    
        select
            t1.col1   as col1
            , t2.col2 as col2
            , t2.col3 as col3
    

It makes commenting out columns painless.

~~~
bigato
> It makes commenting out columns painless.

No it does not. If you have to comment out the first column you'll have to
remove the comma from the second column. It's the same problem that happens if
you put the commas at the end of the line, except that the offending line will
be the last column. So putting commas at the beginning just makes it look
weird because it differs from how we use commas in natural language.

~~~
matwood
Exactly, You're also hiding the most important part of the line which is the
variable name.

------
wiz21c
Dunno if it's the quality of the posts here, but I find interesting to see
that when we talk about SQL nobody complains about it : nobody says there are
better alternative, nobody talks about the great schism between the last 2
major revisions of the SQL standard,...

(IMHO, SQL is one of the oldest languages and it is still super powerful _and_
used like hell in production environment, but that's just a point of view)

~~~
tragomaskhalos
I have plenty of gripes about SQL but am not aware of _any_ real alternatives
... which I do find bizarre - in the realm of programming languages in general
we live in a time of Cambrian explosion in terms of the number and type of
languages being used and developed, and yet SQL sits aloof and unassailable on
its pedestal.

There are a number of clear paths not taken:

1/ A new, better language that compiles down to SQL;

2/ A set of enhancements to SQL that compile down to regular SQL (<= best
imo);

3/ A new language that compiles down to the same AST that is generated from
SQL: this of course would need to be done by the database implementors.

That we don't see this I attribute to:

1/ Perception that SQL is "good enough". Which is true as far as it goes, but
again odd compared with the endless innovation in the world "normal"
programming languages;

2/ Innate conservativism of the database tech community;

3/ Wide use of tools that already sit on top of SQL - query builder APIs, ORMs
etc.

~~~
garysieling
I've wondered about this too. I think ORMs serve effectively the same purpose
as Javascript transpilers (which gives you 1 & 2)

You can also write stored procedures in Postgres in other languages.

~~~
__jal
ORMs attempt to bridge object and relational models. (With results that vary
from "works fairly well" to "screw it, give me a DB handle and I'll just write
SQL".) I see what you're saying, but they operate at a different place than
generators/translators.

Stored procedures are just that - they get a name and persistence. PLSQL has
procedural extensions, but it is still SQL.

Fabian Pascal (who has made a career out of SQL criticism) proposed a
replacement system called Raquel that would have a "real" set-theory-based
query language. I don't think it went anywhere.

My best guess as to why there aren't many alternatives: SQL is both mature and
Good Enough(tm), but importantly, also unsexy and complicated. Database engine
development is more similar to kernel development than, say, web application
development, and there are simply far fewer engineers qualified to do it.
Building some framework in Javascript currently gets a ton of love, and in
comparison is far easier than developing a SQL replacement that is roughly as
performant as SQL and either less awkward linguistically or functionally
superior.

People do take up those sorts of projects, but they're pretty lonely unless
some combination of talent, luck, and timing turns into rock star status
(Linus wasn't the only one writing unix clones at the time).

~~~
dancek
> Stored procedures are just that - they get a name and persistence. PLSQL has
> procedural extensions, but it is still SQL.

PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and
PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but
aren't included. See [https://www.postgresql.org/docs/9.6/static/external-
pl.html](https://www.postgresql.org/docs/9.6/static/external-pl.html)

~~~
__jal
Yes. And PL/Perl is perversely fun. I don't think I've ever seen other
languages used in production, though[1]; I think folks tend to stick to PLSQL
because of defaults.

[1] I'm sure people do, but this discussion has an anecdata deficiency.

------
thom
I make very liberal use of functions in SQL, as much as I would in other
languages - any complex calculation or WHERE clause I'll usually abstract out
into a function with a meaningful name. Postgres has nice syntactic sugar that
makes these look pretty much like columns, so if your function takes a row
type as an argument, you can just call it as `row.function_name` instead of
`function_name(row)`. With the right hints performance is fine, and where it's
not, it's possible to build indexes over function call results.

The article touches on CTEs, while mentioning they are on optimisation
boundaries on some platforms (e.g. Postgres). This touches on my main
annoyance with SQL - most of the abstractions are very costly. Even just
phrasing a query in the simplest, most natural way is rarely optimal. I spend
hours of my life wondering why query planners are so deliberately obtuse. And
there are countless specific little annoyances, e.g. 'percentile_disc' seeming
like a natural fit for calculating a median, but being wildly slower than more
hacky solutions.

I would love to know what's next, to be honest. A readable query language,
which captures the intention and semantics of a query clearly and minimally,
is designed to allow abstractions to be composed together, and compiles to a
mostly optimal query plan. I'd probably settle for SQL as-is, if there was an
RDBMS with a query planner that was willing to go away and think for five
minutes so I could just write the SQL I wanted without thinking too hard about
the implementation.

Anyway, the formatting's important, but like any language, the way you can
create and compose abstractions, and reveal the intention of your code is the
biggest thing for readability.

------
iblaine
I prefer any formatting over no formatting. If had to choose then I would do
this:

* Lowercase for tables & columns and upper case for syntax.

* Having commas first makes it easier to move lines around.

* I avoid small aliases to minimize conflicts when code is resused.

* Every column needs a table reference. Do not make me guess.

* Alias every column in the SELECT clause.
    
    
        SELECT
            table1.col1 AS table_col1
          , table2.col2 AS table_col2
          , table3.col3 AS table_col3
        FROM
            table1 table1
        INNER JOIN
            table2 table2
        ON
            table1.col2 = table2.col1
        INNER JOIN
            table3 table3
        ON
            table1.col3 = table3.col1
        AND table3.col2 = something_else
        WHERE
            table1.col1 > 0
        AND table2.col2 <> table1.col4
        ORDER BY
            table2.col2
        LIMIT 100;

------
mobiuscog
Is it just me, or are the code examples not monospaced ?

This doesn't help when talking about alignment.

~~~
exprA
It isn't just you, and indeed, the alignment examples make little sense with a
proportional typeface.

I checked the CSS, and for some reason the code blocks use the exact same
fonts as the rest of the text (albeit with a new font-family clause just for
them).

------
ak39
If you're in the world of Microsoft SQL Server:

1\. [http://poorsql.com/](http://poorsql.com/)

2\. SSMS plug-in:
[http://architectshack.com/PoorMansTSqlFormatter.ashx#Downloa...](http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_5)

Your life reading and saving SQL will change forever.

------
chrismorgan
Of interest: [http://www.sqlstyle.guide/](http://www.sqlstyle.guide/)

~~~
okket
It was on HN three weeks ago, here es is the link to the discussion:

[https://news.ycombinator.com/item?id=12671667](https://news.ycombinator.com/item?id=12671667)
(146 comments)

------
junto
My pet hate, directly out of SQL Server Management Studio, is how their code
generation places commas. It generates code like this:

    
    
      select
          columnA
          ,columnB
          ,columnC
      from
          tableName
    

Whereas I want my commas AFTER the column names on the same line:

    
    
      select
          columnA,
          columnB,
          columnC
      from
          tableName
    

Or just looks so much neater.

~~~
harterrt
Leading with commas makes it easier to refactor if you're prototyping a query.
You're less likely to cause an error when removing a column from:

    
    
      SELECT 
          first
        , second
        , third
      ...
    

than from

    
    
      SELECT 
        first,
        second,
        third
      ...
    

Which becomes:

    
    
      SELECT 
        first,
        second,
      ...

~~~
ckastner
At best, that may shave off a few seconds of the time needed to refactor.

The trade-off is code that is harder to mentally parse, because we are used to
trailing commas, not leading commas.

If you spend more time reading code than writing it (which I assume applies to
the majority of development), then the trailing comma is a much, much better
choice of style.

~~~
Ntrails
I spend a _lot_ of time writing ad hoc queries for analysis. That means I
don't know what columns I want, I might be switching things up from aggregates
to subsets and back. That's why you'll find things like:

    
    
        WHERE   1=1
        AND     a.id = 12
        --AND     a.Col1 > 23
        AND     a.Col1 = 23
    

Similarly the leading comma makes it faster to cut out things I'm not using
anymore. Now obviously for production code you could argue my reasons are no
longer valid - but probably they'll leak through because that's what all my
shk hotkeys generate and I'm used to writing

------
ksri
I'd love to get HN feedback on a library I wrote to make SQL more manageable -
[https://github.com/hashedin/jinjasql](https://github.com/hashedin/jinjasql).

The library is built on the Jinja template engine. You can create macros,
functions and other reusable sql fragments, and then use them to create the
query. You can also write loops and conditionals, so you can do all the things
you routinely do to generate HTML.

The library tracks bind parameters, and doesn't let user input into the query.
At the end of the day, you get the generated SQL and an array of bind
parameters. You can use these two to execute the query using whatever database
& driver combination you like.

------
justin_vanw
So I think it is much easier to read if you can line up like clauses:

    
    
      SELECT spf.blah_blah,
             count(*) AS cnt
        FROM jv_CTLG_ENTITY_ATTR_PROD_MAP prod_map
        JOIN jv_SSA_PRODUCT_FACT spf
             ON prod_map.prod_ref_id = spf.PROD_REF_ID
       WHERE prod_map.INVSBL_IND = '0' AND
             spf.LSTG_END_DT >= date_sub(from_unixtime(unix_timestamp()), 2) AND
             spf.LSTG_START_DT <= date_sub(from_unixtime(unix_timestamp()), 2) AND
             spf.lstg_site_id = 0 AND 
             prod_map.ENTITY_ATTR_TYPE_DESC IN ('BMPN','GTIN') AND
             spf.CTLG_ID = 1075 AND
             spf.LSTG_TYPE_CD NOT IN (10,12,15)
      GROUP BY spf.blah_blah;

------
akavi
Only tangentially related, but this reminds me of something I've wanted for a
while: Are there any languages for relational queries, but with improved
syntax (composability, epxression capture, static typing?) and which "compile"
down to SQL?

Ideally, I imagine something that looks something like RethinkDB's ReQL, but
DSL-ed up a bit beyond what's possible when it's "just" a JS library (eg, a
nicer syntax for referencing table and row instead of "r.table('table
name').row('row name')" and support for operator literals so you can write ">"
instead of ".gt" or similar.).

~~~
jessemerriman
There's Alf, which has a functional, pipeline-style, though I don't know if
it's still active: [http://www.try-alf.org/blog/](http://www.try-
alf.org/blog/)

~~~
akavi
Sweet, Alf seems to be _exactly_ what I wanted!

Shame it no longer seems to be under active development, but it seems to be
pretty mature?

------
erdemozg
I use the following indentation pattern and find it very useful:

    
    
        select 
            t1.col1,
            t2.col1
    
        from 
            table1 t1 
            left join table2 t2 on t2.primarykey = t1.foreignkey
    
        where 
            t1.somevalue is not null
            and isnull(t2.someothervalue, 0) > 0
    
        order by 
            t1.someothervalue desc
    

By using this pattern I can easily locate columns, tables, criterias, grouping
fields, orders etc. And it works well with more complicated queries as well
given all the subqueries, cases etc. written in a similar fashion.

------
craigds
Is there a good sql autoformatter? For cleaning up ORM-generated queries so I
can read them. I've used python's sqlparse but it produces output that's often
still unreadable.

~~~
sundvor
Redgate's SQL Prompt has a great auto formatter with definable rule sets, if
you use SSMS - and have the budget for it.

~~~
ScaryDBA
If you like the formatter in the current version of Prompt, wait until you see
what's coming out in version 7.3. They've gone nuts. The new mechanisms for
formatting are far beyond anything we've had so far. I'm pretty jazzed about
it (disclosure: I work for Redgate).

------
bigato
> CTEs

>First, yes they can be an optimisation boundary. But they can also make your
query much more read-able and prevent you from doing the wrong thing because
you couldn’t reason about a query.

In my experience, CTEs may be simpler for the writer to reason about, but they
make the queries complicated for the reader to understand. If you use a CTE
because you find the problem too complicated, chances are that you are not yet
understanding the problem well enough to describe it in a single plain SQL
query.

~~~
blowski
Sometimes I understand the problem perfectly well, but a single beautiful
query is not performant enough. Anything that can help to write and maintain
big ugly queries without a cost in performance is welcome.

~~~
bigato
Care to elaborate on what situation would a CTE improve performance? Because
the OP itself mentioned CTEs degrading performance.

~~~
blowski
It wouldn't improve performance, but it might improve readability without
denigrating performance. I don't have much experience with CTEs, but I do have
lots of experience trying to debug unreadable SQL.

~~~
bigato
> Sometimes I understand the problem perfectly well, but a single beautiful
> query is not performant enough.

> It wouldn't improve performance, but it might improve readability without
> denigrating performance.

These two statements of yours are negating each other, unless people started
using "performant" as synonymous for readable now.

~~~
blowski
OK, a clarification.

My first attempt at a query is readable but performance is terrible. I fix the
performance problem, but the query is now much less readable. I use a CTE as a
compromise between performance and readability.

------
falcolas
One thing missing that I can't recommend more highly - a comment in the SQL
clause indicating where in the code it is being called from. These comments
are invaluable when debugging or doing performance tuning of queries,
especially when you have a large codebase.

i.e.

    
    
        SELECT *
        FROM students
        WHERE 1
        /* somemodule.somemethod */
    

As for query formatting, as with everything, consistency in style is more
important than a dogmatic style guide. My time as a DB admin has given me some
specific style guidelines, typically stolen from the formatting provided by
the DB itself. More specifically, I match my style to what you see when you
run a command like `SHOW CREATE TABLE students;`. I tend to reserve my
indentation for things like subqueries.

~~~
irrational
I agree it would be great to show where the code is being called from, but
often we have queries that are called from dozens of different places and it
would be unwieldy to add all of those. Plus, if one time a developer starts
using a query somewhere in the code and forgets to add that place to the
query, all of the sudden all places where the query is called from becomes
suspect. I think it is better to give your query block a unique name or
namespace that allows you to quickly search all of your code for where that
name is used.

------
stadeschuldt
Good post. I use one of Jetbrains IDEs and I am big fan of their auto-
formatting. You select the dialect of your SQL (or connect it to the data
source) and the IDE almost formats it ideally. Sometimes I enforce certain
line breaks but for the most part it does a really good job.

~~~
douche
DataGrip is pretty excellent from them. The only thing I'm not really happy
with is it's support for diagramming table structure, but I haven't found
anything that really does a good job of that.

------
tumba
I highly recommend Joe Celko's book, SQL Programming Style. [1] He does deal
with formatting, but offers a comprehensive usage standard that goes beyond
readability to discussing thorny issues like the taxonomy of words like
"type," "category," and "code," as well as referencing relevant international
standards for identifier naming.

[1] [https://www.amazon.com/Celkos-Programming-Kaufmann-
Managemen...](https://www.amazon.com/Celkos-Programming-Kaufmann-Management-
Systems/dp/0120887975/)

------
leetrout
I loathe wide lines. I think it's much easier to mentally parse vertical lists
of things and I write my SQL much like this.

In PHP / Python / Go when inlining SQL I also line up sequential items
indentation with spaces (which most editors do by default when hitting enter
inside a multi line string).

Nice to see this codified. I really just wish I would have worked more in my
career on teams that care as much about readability and comprehension in 6+
months as I do.

Hopefully more people will follow the advice in the article and write cleaner,
easier to mentally parse SQL.

------
jgilfillan
This is how I write SQL:

    
    
      select
         col1
        ,col2
        ,col3
      from table1 a
      
      left join table 2 b
        on a.col1 = b.col2
      
      where 1=1
        and col1 = 'condition'
        and col2 = 'condition2'
      ;
    
    
     * everything lower case (except strings)
     * leading commas
     * conditions indented by two spaces
     * select columns indented by two spaces, except the first colunm which is indented by three.
     * where 1=1 for easier commenting/uncommenting of conditions

~~~
zikzak
Came here to find out who else uses leading commas. I learned this several
years ago at my current job and love it. I find it much more readable and also
makes adding to the list clearer for some reason. Plus if you ever need to
insert like 50 commas at the start of a bunch of lines in an ad hoc query it
is much easier at the start of the line (yes, I am an animal).

------
jenkstom
I am a visual thinker. The method of listing columns as rows (i.e., the one
line per column format) is too annoying to focus on the underlying code.
Splitting a list is something like pagination or even regular text, so I
suppose my mind just compensates for that without my thinking about it.

But I just can't do the columns as rows thing. I feel a little bit out in the
cold because of it, but I'll just keep reformatting everybody else's code so I
can read it. :-)

------
chrismorgan
The document styles make code blocks sans-serif, and then attempt to use
monospace indentation—the end result looks _worse_ , not better, than no
leading indentation.

------
agentgt
I while back I wrote a crappy SQL mapper that has an interesting SQL template
language that uses SQL comments to make Jdbc place holders:
[https://github.com/agentgt/jirm/blob/master/jirm-
core/README...](https://github.com/agentgt/jirm/blob/master/jirm-
core/README.md)

One of the interesting side effects of using the parser was easier to read
SQL.

------
krylon
One issue that bit me pretty badly wrt comments is either Perl's DBI or the
ODBC layer, but when talking to SQL Server from a Perl script via ODBC, I had
to remove all comments from a rather convoluted SQL query.

I ended up keeping the comments in my source code and removing them via a
regex at runtime, but it took me quite a while to figure out why my Perl
script failed with a query that ran perfectly when copied and pasted into
SSMS.

------
mosselman
This is what is wrong with 'best practices'

`SELECT foo, bar FROM baz`

Is not at all more legible than:

`SELECT foo, bar FROM baz`

On first glance I even missed the 'bar' column completely and just saw it when
compressing this line.

As things get longer it gets more important to make it legible, but saying
that my first example is better than the second is just nonsense.

Don't worry so much about what you should or should not do, just use common
sense.

~~~
mosselman
Haha woops, multiline did not work.

The first example is supposed to read:

    
    
        SELECT foo, 
            bar 
        FROM baz

------
guruparan18
Am I the only one to see or missing obviously. The following is wrong, where
clause has table name (baz)?

    
    
        SELECT foo,
           bar
        FROM baz
        WHERE foo > 3
          AND baz =  'craig.kerstiens@gmail.com'
    

Edit: Technically you can have a column name exactly same as table name,
however I am finding it hard to find difference between the two queries
presented.

~~~
craigkerstiens
Yeah, definitely a typo in the example, will fix.

------
dspillett
_> CTEs: First, yes they can be an optimisation boundary._

Note that this varies by database engine. They are an optimisation fence in
postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or
DB2 but I think they can optimise predicate application over CTE boundaries
too.

------
codeulike
This is all trivial. Simple queries are pretty easy to read as long as the
formatting is vaguely sane. What we need to talk about is how to deal with
queries with multiple nested subqueries (in the select clause and the where
clause) and how to make that legible.

------
Revell
I write my SQL as follows:

    
    
        SELECT `second_column`,
               `fourth_column`
          FROM `table_name`
         WHERE `first_column` = 'Value'
           AND `third_column` = 3
      ORDER BY `fifth_column` ASC
         LIMIT 1

~~~
83457
Well that is cute. I find it odd that the second line of the where clause is
on the same level as the where but a neat format otherwise. Tabs or spaces
though?

------
wenham
Yes, readability is good, it makes any scripting easier. SQL is no exception.
Hand in hand with this is calling things what they are and good comments for
the 'why' rather than the 'what' parts of a statement.

------
nurettin
I prefer my own style where comma is placed before every column. It makes
columns, subqueries and case expressions line up nicely, especially when you
have 15 columns or more.

~~~
craigkerstiens
Yeah, I've definitely seen this and tried it myself. Visually for some reason,
it just pains me too much. It does make removing lines much easier though so I
can understand the appeal.

------
rdameron
Just use Redgate SQL Prompt. It's the easiest fix. I do not work for Redgate.

------
shearnie
I prefer keywords in their own line eg:

<code> SELECT foo, bar, blah FROM baz WHERE foo > 3 AND blah = 'dont use baz'
</code>

------
jjp
Would have been useful to see some thinking on joins and the naming
conventions when you have the same column in both tables

~~~
NDizzle
Use aliases, and if you have more than two joins you should prefix every
column, unless the column names are obvious.

------
meganvito
I think that may be more compact, if the IDE can reflect the algebra beneath
the SQL clauses.

------
lorenzfx
How do you handle indentation in python (especially if the line is already
indented)?

~~~
falcolas
Triple quoted strings. Since SQL doesn't care about whitespace, you can line
up your SQL with the rest of your code quite neatly.

------
davidw
Anyone got some good Emacs advice for automated formatting?

------
ed_blackburn
SQL is 'blighted' by inertia.

------
maurycy
Is there indent for SQL?

~~~
ManlyBread
Poor Man's SQL Formatter for Notepad++ can do it, it leaves a lot to be
desired though in terms of customization. It has saved me a lot of times,
since I'd rather read Poor Man's code than the code of the other people.

~~~
gregn610
Another vote for Poor Man's.

But I think it's an important caveat that the whole team use the same
formatter and the same settings.

Otherwise diff's turn to useless noise* because Cubicle Bob has personalized
his settings.

*Yes, I've tried the Winmerge plugin. It's helpful but not ideal.

