SQL is 'blighted' by inertia.
Writing more legible SQL
61–70 of 168 posts
Re: Writing more legible SQL
#62Dunno 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)
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 (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.
Re: Writing more legible SQL
#63Earlier quoted context omitted.
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.
Why not this? WHERE A AND B AND C Or better, this? You can comment out any part easily. WHERE 1 AND A AND B AND C (With keywords capitalized, as g-d intended. :-) Or not.)
I like the "WHERE 1" trick, thanks!
Re: Writing more legible SQL
#64Am 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
Re: Writing more legible SQL
#65Earlier quoted context omitted.
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 jo…
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.
Re: Writing more legible SQL
#66Is 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 you happen to use MS SQL Server, there is ApexSQL Refactor, a plugin to SQL Server Management Studio (https://www.apexsql.com/sql_tools_refactor.aspx).
As the name implies it mainly does stuff other than formatting, but the latter is what I use it for, and I am rather happy with it.
When I write SQL myself, I tend for format it rather scrupulously, but when I have to read SQL written by somebody else, it is very useful.
Re: Writing more legible SQL
#67Here 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 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…
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.
Re: Writing more legible SQL
#68Am 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.
Same here ... except the first field would be on the same line as the select, and the comma would be aligned with select's "t"
Re: Writing more legible SQL
#69Earlier quoted context omitted.
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.
>> If your IDE doesn't have that
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)
Re: Writing more legible SQL
#70Dunno 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)
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;…
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.