4 ms·

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 th

by vog 10y ago

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 10y ago
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 10y ago
  > 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 10y ago
    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.
- bhrgunatha 10y ago
  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
rubyfan 10y ago
Very similar to my style, though I rarely use <> and instead mostly use != Never realized I could do tuple compare and will probably adopt that.
- combatentropy 10y ago
  >> 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. Indeed. Mind blown.
ckastner 10y ago
Where did you pick up this style? 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.
snarfy 10y ago
For item 3, the benefit of "and" at the beginning is that you can add or remove lines to the condition without disturbing the previous/next lines. The same works for ","
- vog 10y ago
  But the disadvantage is that you don't immediately see whether it is "and", "or" or something else. 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)) BAD: 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)) Examples: - Python PEP-0008: "Should a line break before or after a binary operator?" https://www.python.org/dev/peps/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 http://stackoverflow.com/a/699347
sevensor 10y ago
Regardless of the particulars, I find the most important thing for readability is for the author to have made some kind of decision and stuck to it. The most illegible style is no style at all.
- hiou 10y ago
  As long as it does not deviate into getting really creative with the spec, keeping everything consistent is overrated in my opinion. 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.

by vog 10y ago

gerhardi 10y ago

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 10y ago
> 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 10y ago
  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.
bhrgunatha 10y ago
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

rubyfan 10y ago

Very similar to my style, though I rarely use <> and instead mostly use != Never realized I could do tuple compare and will probably adopt that.

combatentropy 10y ago
>> 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. Indeed. Mind blown.

ckastner 10y ago

Where did you pick up this style? 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.

snarfy 10y ago

For item 3, the benefit of "and" at the beginning is that you can add or remove lines to the condition without disturbing the previous/next lines. The same works for ","

vog 10y ago
But the disadvantage is that you don't immediately see whether it is "and", "or" or something else. 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)) BAD: 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)) Examples: - Python PEP-0008: "Should a line break before or after a binary operator?" https://www.python.org/dev/peps/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 http://stackoverflow.com/a/699347

sevensor 10y ago

Regardless of the particulars, I find the most important thing for readability is for the author to have made some kind of decision and stuck to it. The most illegible style is no style at all.

hiou 10y ago
As long as it does not deviate into getting really creative with the spec, keeping everything consistent is overrated in my opinion. 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.