4 ms·
A couple of jobs ago, I worked at a company that did a ton of SQL and we used right justified keywords, which looks like: select t1.col1, t2.col
by msluyter 10y ago
A couple of jobs ago, I worked at a company that did a ton of SQL and we used right justified keywords, which looks like:
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
Initially it seemed weird to see the ragged left edge, but over time I got used to it and grew to prefer it. It doesn't really hold up for extremely complex queries with lots of subqueries because everything tends to start drifting too far to the right (unrealistic example below):
select *
from blah b
where b.whatever in (
select blah
from c
where c.whatever = in (
select blah
from d
where d.whatever = 'foo'
)
)
I almost never see this style used these days, and I was curious if anyone else does it this way?
- teej 10y agoThis is very close to the style we use. The only big difference is that we opt for CTEs over subqueries whenever possible.
- perlgeek 10y agoWe also use right-justified keywords (and never "select *", though I guess that was just to keep your example brief).
- 20years 10y agoThis is the style I use other than I uppercase the reserved words.
- howItsDone123 10y agoI use this style. I work with queries that are a little bigger than average, and it's very difficult to keep track of it unless it's well laid out. conditionals, loops, etc, get formatted like they would in a procedural language. the DML gets formatted similarly to what you've posted. a not unrealistic example from this morning is as follows: if (select object_id('aTableName', 'u')) is not null begin select column from aTableName where thisIsTrue = 1 and somethingIs in (select aList from aListTable) and somethingElse = ( select aValue from aValueTable where aValue = 'somethingImportant' ) end else begin print('Oh noes! I couldn't find somethingImportant or something in someList form aTable!') end we work with huge amounts of data with systems and processes that don't lend themselves to other methods (yet... it's coming.) so we have to make SQL as readable as possible. edit: yes... i know... subqueries are bad... it's just an example. those would have been done in joins or as temporary variables or something else.
- kentosi 10y agoYes! All the time, but with some variation. Let me explain by rewriting your query: select t1.col1, t2.col2, t3.col3 from table1 t1 inner join table2 t2 on t1.col2 = t2.col1 inner join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where 1 = 1 and t1.col1 > 0 and t2.col2 <> t1.col4 order by col2 limit 100 My logic is that I: 1 - There should be a clean vertical line between the select/from/inner/where/etc keyboards to the right hand side for clean alignment. 2 - I always use the where 1 = 1 syntax so that I can easily comment out the following conditions while testing. And thanks for not capitalising the keywords :-)
- nthcolumn 10y agoYes I too am of the river people now. +1 for long queries and being able to comment out everything. 30 years SQL. bah!