[deleted]
Writing more legible SQL
51–60 of 168 posts
Re: Writing more legible SQL
#52Earlier quoted context omitted.
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 pro…
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 somethin…
> 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)Re: Writing more legible SQL
#53Is 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.
If Oracle is your flavour of SQL, PL/SQL Developer is very nice and has a good formatter built in.
Re: Writing more legible SQL
#54I 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
Re: Writing more legible SQL
#55Earlier quoted context omitted.
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 pro…
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 somethin…
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
Re: Writing more legible SQL
#56Here 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 thing…
I go back and forth with conjunction at the end or beginning of the line. At the end you get to line up column names, at the beginning it makes it easier to comment out or remove the line and basically contains the intended logic on a single line. I think I usually end up with the later because I think it makes more sense.
I have the same problem and also with where to put the commas separating columns names. You can't win the game though.
Sometimes you want to remove/comment out the first line, sometimes the last, sometimes one in the middle.
Re: Writing more legible SQL
#57This 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
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).
Re: Writing more legible SQL
#58This 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.
Re: Writing more legible SQL
#59Here 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 thing…
That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does:
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
;Re: Writing more legible SQL
#60Here 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 thing…
That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does: 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 = some…
The lower-case probably works better for IDEs that have SQL syntax-coloring. If your IDE doesn't have that (looking at you XCode..), then upper-case keywords are better.