9 ms·
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 jo
by combatentropy 10y ago
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 things vertically.
3. Conjunctions like "and" are put at the end of a line, like a comma, not at the beginning. I think it lines things up better vertically.
4. I try to keep lines short, but I also try to keep it from getting needlessly long vertically. So if the statement is short and simple, I might fold some things back onto one line, like:
select col1, col2, col3
from table1
where col1 = something_or_other
or even:
select * from table1 where col1 = something
- rubyfan 10y agoI 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.
- berntb 10y agoWhy 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.)
- nerdponx 10y agoI like the "WHERE 1" trick, thanks!
- bhrgunatha 10y agoI 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.
- emmelaich 10y agoOh yes please. Capitals just make it harder to read.
- walrus 10y agoInteresting! I independently ended up on nearly the exact same style. The only difference is I don't indent join clauses.
- jm547ster 10y ago"2. I just indent subclauses, with four spaces," Surely you mean tabs? runs
- Sean1708 10y ago"runs" Into my arms.
- julochrobak 10y agoquick 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?
- combatentropy 10y agoNo. I indent all joins to the same level, regardless of the join condition. The effect of joins is to make one large flat table, so nested indentation is an unnecessary and misleading signal.
- BurningFrog 10y agoThe purpose of capitalization is to visually distinguish the keywords. In this style that's done in other ways so there is no need.
- alistairbayley 10y ago1. 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?
- 83457 10y agoI think of joins as operators and from as the block similar to and/or in the where clause. He is being inconsistent with line breaks for select, from and where blocks. Personally I want to be able to visually pick out the blocks of the query and the easiest way to do that is with indention imo.
- 1wd 10y agoIf "joins" are operators like "and", and you put "and" at the end of the line, shouldn't you put "join" at the end too? from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else
- combatentropy 10y ago> Why indent the joins? > Are table2 and table3 less important than table1? It's often arbitrary which tables are joined and which one is from'd. But the joins are part of the from-clause. They all join together into one big from. > 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? The join conditions are just to line up the rows of the different tables with each other, to avoid a cartesian product, to form one big table. This giant table is then filtered through the where-clause, like a funnel. You can put the filters in the joins, and I have in the past, but putting them in the where-clause better reflects the picture in my head. Tangentially, it would have been better if SQL had the select-clause after the from- and where-clauses: 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 select t1.col1, t2.col2, t3.col3 To understand the select-clause, I always first have to jump down to the from-clause anyway. This would also mirror the other statements: insert, update, and delete, which begin with the table names. This better reflects the flow of data. First you decide the source of data (which tables). Then you filter down to which records (which rows, the where-clause). Finally you determine which fields to get (which columns, the select-clause).
- vog 10y agoI 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 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 100
- gerhardi 10y agoI 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 something and ( (t3.col = x and t2.col = y and tn.col != z) or tn.col = z ) ... So in above you can see by the indent on which table some other table is joined to. Tables t2, t3 and tn are related to t1, but t4 is related to t3, not directly t1.
- vog 10y ago> 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)
- lost_my_pwd 10y agoThat 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 ;
- mozumder 10y agoThe 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.
- bluedino 10y ago>> 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)
- specialist 10y ago+1 Vertical whitespace.
- biztos 10y agoI do almost the same thing, but I capitalize all SQL keywords. I'm sure that's annoying to someone somewhere but I find it useful in separating the SQL from the relation names.
- WorldMaker 10y agoWe've had great syntax highlighting tools for decades now, I'd much rather my tooling do it for me with subtle colorations than be forced to resort to COBOL SCREAMING. I've trended towards a preference to "sentence-cased" SQL where the first word in an SQL statement is Capitalized and everything else is lower-cased. Makes things read more like a narrative and is a nicer hint than trying to spot an optional semi-colon to determine if you've reached the end of a command yet (and helps in those times when you need to find a place where it turned out an optional semi-colon wasn't in fact optional). Select * from table where condition order by fields Insert into table (...) values (...) So far as I know I'm about the only person that likes sentence-casing SQL, but it helped me out quite a bit on some projects I worked on and it kept things readable.
- sunjain 10y agoTotally agree..This should be put on a page published somewhere. Whenever I am trying to look at performance issues (or any other issue) with a SQL, this is the first thing I do and pretty much follow the same strategy.
- matwood 10y agoI've been writing SQL for close the 20 years, and my style has settled into something very similar to yours. Lowercase everything, and use indentation to clearly separate the major parts of the query. And, like you, be pragmatic. If it is a 1 liner, keep it a 1 liner.
- logingone 10y agoI read somewhere, at some point, couldn't provide a ref, that the caps were used for clarity back in the days of monochrome screens. Now, with colour syntax highlighting it's no longer as useful.
- __jal 10y agoYep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps. Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.
- blowski 10y agoOne developer's 'river' is another's 'grid'. Being able to scan down quickly makes it easier to compare lines against each other. That's not something you do when reading a novel or a newspaper article, but it's something you do all the time when reading code.
- __jal 10y agoMaybe I'm a weirdo. I find it massively distracting. About the only time column-alignment is helpful is when scanning for typos, but that's handled pretty capably in other ways by most editors more sophisticated than Text Edit.
- gbog 10y agoI think "rivers" are not the same. if the same as in edition of books in French, rivers are those unwanted "paths" your eye sees floating downwards inside the rectangle of words that makes a page. They come out of randomness, when whitespaces happen to draw a negative path. It has to be corrected by slightly modifying the whitespaces between some words. AFAIK this is done manually and is one reason for book edition to be done by humans. In short, it has nothing to do with purposeful perfect vertical alignment in code.
- __jal 10y agoWhen I worked in magazine production (90s, California) we used the term for both whitespace and other patterns. Other patterns are a lot less common, because whitespace is the most common "character", but they happen.
- cptskippy 10y agoIf I came across your SQL, I'd probably hunt you down and give you a hug. There's so much horridly formatted code and it's unusual to see a developer care. I prefer to uppercase command syntax to make stand apart visually from the parameters of the query. I don't agree with your conjunctions at the end of the line, I actually prefer commas at the beginning of the next line though I don't do that so as to conform to convention. I also use the AS keyword to explicitly denote aliases. If I had it my way, all SQL would look like this: SELECT t1.col1 ,t2.col2 ,t3.col3 FROM table1 AS t1 JOIN table2 AS t2 ON t1.col2 = t2.col1 JOIN table3 AS 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
- cwyers 10y agoThat's pretty close to how I do it. I started putting commas at the beginning of a line because when I started doing so I stopped ever having queries error out because I copy and pasted something from one query to another and forgot to remove a trailing comma. Having the commas at the front of the line makes finding those kinds of errors automatic for me.
- sbuttgereit 10y agoVery much like this style, very close to what I do... really the only difference between your style and mine is that I will indent the joins. For me it makes it somewhat easier to be able to scan for the where clause. Also I'll typically not use the AS keyword for table aliasing, no real good reason for that choice other than many years of habit. But mostly commas first and logical operators and such first: If I have complicated queries that I'm working through... having the ability to just comment bit out here and there without worrying too much if I've left a dangling comma (et al) is a good time saver.
- blakeyrat 10y agoInstead of using uppercase, which looks awful, just get an editor with color-coding. Solves the same problem, no more ugly uppercase.
- neves 10y agoYou didn't mentioned the most important thing to make readable SQL and that is demonstrated in your clause: use the "join" keyword instead of doing the join in the "where" clause. I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.
- sundvor 10y agoMy first thought was "why would you do that?". Glad to say I've rarely seen joins in the where clause.
- T-hawk 10y agoThis 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 happening.
- sundvor 10y agoGreat, 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 just bad; WHERE clauses ought to be used for filtering the data, not declaring how the tables join. Glad to have cleared that up. :)
- T-hawk 10y agoYou'd do it that way if you just didn't know any better. "Two tables where A = B" falls into a natural line of thinking for someone who picked up SQL ad hoc rather than formally learning what a join is and the syntax for it.
- irrational 10y agoI prefer putting the comma at the beginning of the line (this makes dynamic sql much easier). I prefer putting and/or at the beginning of the line. I indent multiple items like case statements in the select. I use parens for join statements with multiple join conditions. I separate the select/from/where clauses with a blank line when a section has more than one line. select t1.col1 , t2.col2 , t3.col3 , (case when t1.col1 = 'Y' then '1' else '0' end) as the_flag , t3.col2 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 , col1 limit 100