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

That's why in most programming languages the coding style says you should put the operators at start of line instead of end of line, e.g.

GOOD:

    if ((...some_condition_that_may_make_sense...)
        && (...some_other_condition...)
        && (...and_yet_some_condition_that_makes_sense...))

    x = ((some expresssion)
         + (some other expression of different length)
         - (some final expression))

    if ((...some_condition_that_may_make_sense...) &&
        (...some_other_condition...) &&
        (...and_yet_some_condition_that_makes_sense...))

    x = ((some expresssion) +
         (some other expression of different length) -
         (some final expression))

- Python PEP-0008: "Should a line break before or after a binary operator?" https://www.python.org/dev/peps/pep-0008/#should-a-line-brea...

- Topvoted SO answer for "If you break long code lines, how do you indent the stuff on the next line?" http://stackoverflow.com/a/699347

Never realized I could do tuple compare and will probably adopt that.

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

    > never realized I could do tuple compare and
    > will probably adopt that.

  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
                )
  ...

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)

They are at the top of the hierarchy of information I want when I'm reading a query.

The information I want to be able to identify the quickest are.

1. Tables/view names

2. How they are joined

3. Columns

4. Filters

5. Grouping/Ordering/Anything Else

There are the exceptions of very widely used projects where contributors will only look at the code a couple times when making a change to be pushed upstream or where there are significantly junior developers that need a narrow scope of the language to help them with the learning curve and getting up to speed with the code base.

In my experience(which is very anecdotal I admit), the requirement for consistency is more about a particular personality trait that many developers have - the need for order and control - as opposed to a requirement for quality production code.

I have been following this pattern for over a decade. I believe I picked it up from some project's style guide, but I never found it again.

I prefer to uppercase command syntax to make stand apart visually from the parameters of the query. I don't agree with your conjunctions at the end of the line, I actually prefer commas at the beginning of the next line though I don't do that so as to conform to convention. I also use the AS keyword to explicitly denote aliases.

If I had it my way, all SQL would look like this:

    SELECT t1.col1
          ,t2.col2
          ,t3.col3
    FROM table1 AS t1
    JOIN table2 AS t2 
        ON t1.col2 = t2.col1
    JOIN table3 AS 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

But mostly commas first and logical operators and such first: If I have complicated queries that I'm working through... having the ability to just comment bit out here and there without worrying too much if I've left a dangling comma (et al) is a good time saver.

    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
    ;

I'm looking at you, System i Navigator. Also you don't support anti-aliased fonts or scale properly (because you're a 90's Java application)

I've trended towards a preference to "sentence-cased" SQL where the first word in an SQL statement is Capitalized and everything else is lower-cased. Makes things read more like a narrative and is a nicer hint than trying to spot an optional semi-colon to determine if you've reached the end of a command yet (and helps in those times when you need to find a place where it turned out an optional semi-colon wasn't in fact optional).

    Select *
    from table
    where condition
    order by fields

    Insert into table
      (...)
    values
      (...)

Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.

In short, it has nothing to do with purposeful perfect vertical alignment in code.

I think I usually end up with the later because I think it makes more sense.

  WHERE A
    AND B
    AND C

  WHERE 1
    AND A
    AND B
    AND C

Sometimes you want to remove/comment out the first line, sometimes the last, sometimes one in the middle.

Surely you mean tabs? runs

Into my arms.

select

t1.col1

, t2.col2

, t3.col3

, (case when t1.col1 = 'Y'

   then '1'

   else '0'

   end) as the_flag

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

, col1

limit 100

2. Why place some predicates in the where-clause and others in the join-clauses? What's the thinking here? Why not put all predicates up in the join-clauses, nearer to the tables that they affect?

    from
        table1 t1 join
        table2 t2 on t1.col2 = t2.col1 join
        table3 t3 on
            t1.col3 = t3.col1 and
            t3.col2 = something_else

  > Why indent the joins?
  > Are table2 and table3 less important than table1?

  > Why place some predicates in the where-clause and
  > others in the join-clauses? What's the thinking here?
  > Why not put all predicates up in the join-clauses,
  > nearer to the tables that they affect?

This giant table is then filtered through the where-clause, like a funnel. You can put the filters in the joins, and I have in the past, but putting them in the where-clause better reflects the picture in my head.

Tangentially, it would have been better if SQL had the select-clause after the from- and where-clauses:

    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
    select
        t1.col1,
        t2.col2,
        t3.col3

This better reflects the flow of data. First you decide the source of data (which tables). Then you filter down to which records (which rows, the where-clause). Finally you determine which fields to get (which columns, the select-clause).

And, like you, be pragmatic. If it is a 1 liner, keep it a 1 liner.

In this style that's done in other ways so there is no need.

I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.

An unsettling number of SQL developers never actually learned join syntax and write it that way.

It's functionally equivalent to putting the equality in the JOIN ... ON clause, and any modern database will optimize it to execute the same way, but it's a worse syntactical representation of what's actually happening.

I can vaguely recall seeing it a few times now, actually, and being rather confused as to why you'd do it that way - as perhaps it was a more optimal way of putting it, and that I'd done it wrong all the time?

As you state though, it's just bad; WHERE clauses ought to be used for filtering the data, not declaring how the tables join. Glad to have cleared that up. :)