Writing more legible SQL

Re:Writing more legible SQL by gerhardi on Thursday November 03, 2016 @05:00AM (#12862448)

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.

[ Parent | on HN ]

Re:Writing more legible SQL by vog on Thursday November 03, 2016 @05:51AM (#12862626)

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

[ Parent | on HN ]

Re:Writing more legible SQL by gerhardi on Thursday November 03, 2016 @07:05AM (#12862899)

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.

[ Parent | on HN ]

Re:Writing more legible SQL by bhrgunatha on Thursday November 03, 2016 @06:00AM (#12862660)

I much prefer the style of the comment you replied to because it's very easy (for me) to scan the table/view names when they are vertically aligned.

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

[ Parent | on HN ]