31–40 of 168 posts
I write my SQL as follows: SELECT `second_column`, `fourth_column` FROM `table_name` WHERE `first_column` = 'Value' AND `third_column` = 3 ORDER BY `fifth_column` ASC LIMIT 1
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…
1. Why indent the joins? Are table2 and table3 less important than table1? Is table1 special? Is that why it enjoys privileged status in the from-clause? 2. Why place some predicates in the where-clause and others in the join-clauses? What's the thinking here? Why not put all predicates up in the join-clauses, nearer to the tables that they affect?
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…
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 100Is it just me, or are the code examples not monospaced ? This doesn't help when talking about alignment.
I checked the CSS, and for some reason the code blocks use the exact same fonts as the rest of the text (albeit with a new font-family clause just for them).
I write my SQL as follows: SELECT `second_column`, `fourth_column` FROM `table_name` WHERE `first_column` = 'Value' AND `third_column` = 3 ORDER BY `fifth_column` ASC LIMIT 1
I prefer my own style where comma is placed before every column. It makes columns, subqueries and case expressions line up nicely, especially when you have 15 columns or more.
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…
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…
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
)
...
`SELECT foo, bar FROM baz`
Is not at all more legible than:
`SELECT foo, bar FROM baz`
On first glance I even missed the 'bar' column completely and just saw it when compressing this line.
As things get longer it gets more important to make it legible, but saying that my first example is better than the second is just nonsense.
Don't worry so much about what you should or should not do, just use common sense.
SELECT spf.blah_blah,
count(*) AS cnt
FROM jv_CTLG_ENTITY_ATTR_PROD_MAP prod_map
JOIN jv_SSA_PRODUCT_FACT spf
ON prod_map.prod_ref_id = spf.PROD_REF_ID
WHERE prod_map.INVSBL_IND = '0' AND
spf.LSTG_END_DT >= date_sub(from_unixtime(unix_timestamp()), 2) AND
spf.LSTG_START_DT