161–168 of 168 posts
Am 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.
It has some pre-defined formats depending on the database you use such as Oracle, DB2, etc. and it also lets you customize these formats if you like.
Here 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…
quick question about the indent of subclauses. How would you write a query where table3 needs to be joined with both table1 and table2? Would it change anything?
Earlier quoted context omitted.
My first thought was "why would you do that?". Glad to say I've rarely seen joins in the where clause.
This is what he means, and I see it all the time: SELECT FROM table1 JOIN table2 WHERE table1.field = table2.field 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…
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. :)
Is 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.
Redgate's SQL Prompt has a great auto formatter with definable rule sets, if you use SSMS - and have the budget for it.
Earlier quoted context omitted.
This is what he means, and I see it all the time: SELECT FROM table1 JOIN table2 WHERE table1.field = table2.field 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…
Great, thanks for that - I've been using (typically INNER) JOIN .. ON .. for all my life, well 20 odd years of development, and not once did I ever put the ON clause into WHERE. :) 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 jus…