Am I the only one to see or missing obviously. The following is wrong, where clause has table name (baz)?
SELECT foo,
bar
FROM baz
WHERE foo > 3
AND baz = 'craig.kerstiens@gmail.com'
Edit: Technically you can have a column name exactly same as table name, however I am finding it hard to find difference between the two queries presented.
I loathe wide lines. I think it's much easier to mentally parse vertical lists of things and I write my SQL much like this.
In PHP / Python / Go when inlining SQL I also line up sequential items indentation with spaces (which most editors do by default when hitting enter inside a multi line string).
Nice to see this codified. I really just wish I would have worked more in my career on teams that care as much about readability and comprehension in 6+ months as I do.
Hopefully more people will follow the advice in the article and write cleaner, easier to mentally parse SQL.
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.
Yeah, I've definitely seen this and tried it myself. Visually for some reason, it just pains me too much. It does make removing lines much easier though so I can understand the appeal.
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
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.
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?
No. 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.
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?
I 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.
> 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).
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 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
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 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.
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)
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.
>> prefer tuple comparisons of multi-comparisons,
>> i.e. "(a,b)=(c,d)" instead of "a=c and b=d"
> never realized I could do tuple compare and
> will probably adopt that.
For item 3, the benefit of "and" at the beginning is that you can add or remove lines to the condition without disturbing the previous/next lines. The same works for ","
But the disadvantage is that you don't immediately see whether it is "and", "or" or something else.
That's why in most programming languages the coding style says you should put the operators at start of line instead of end of line, e.g.
GOOD:
if ((...some_condition_that_may_make_sense...)
&& (...some_other_condition...)
&& (...and_yet_some_condition_that_makes_sense...))
x = ((some expresssion)
+ (some other expression of different length)
- (some final expression))
BAD:
if ((...some_condition_that_may_make_sense...) &&
(...some_other_condition...) &&
(...and_yet_some_condition_that_makes_sense...))
x = ((some expresssion) +
(some other expression of different length) -
(some final expression))
Regardless of the particulars, I find the most important thing for readability is for the author to have made some kind of decision and stuck to it. The most illegible style is no style at all.
As long as it does not deviate into getting really creative with the spec, keeping everything consistent is overrated in my opinion.
There are the exceptions of very widely used projects where contributors will only look at the code a couple times when making a change to be pushed upstream or where there are significantly junior developers that need a narrow scope of the language to help them with the learning curve and getting up to speed with the code base.
In my experience(which is very anecdotal I admit), the requirement for consistency is more about a particular personality trait that many developers have - the need for order and control - as opposed to a requirement for quality production code.
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 = something_else
WHERE
t1.col1 > 0 and
t2.col2 <> t1.col4
ORDER BY col2
LIMIT 100
;
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.
I 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.
We'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.
Totally 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.
I'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.
I 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.
Yep. 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.
One 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.
Maybe 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.
I 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.
When 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.
I used to agree with you about putting commas at the beginning of lines, but now I've changed my mind. By putting the commas at the beginning of lines it becomes much easier to do dynamic sql and to be able to add remove select statements without having to check the ends of the lines to make sure the last line does not have a trailing comma and to make sure the preceding lines each does have the requisite comma.
If 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
That'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.
Very 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.
You 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.
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 happening.
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 just bad; WHERE clauses ought to be used for filtering the data, not declaring how the tables join. Glad to have cleared that up. :)
You'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.
I 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.
Poor Man's SQL Formatter for Notepad++ can do it, it leaves a lot to be desired though in terms of customization. It has saved me a lot of times, since I'd rather read Poor Man's code than the code of the other people.
I am using Jetbrains Datagrip to write SQL all day, every day. It can auto-format SQL just like all their other tools can auto-format the language in use. It also has a bunch of other handy stuff like symbol completion from the current database.
Well that is cute. I find it odd that the second line of the where clause is on the same level as the where but a neat format otherwise. Tabs or spaces though?
The document styles make code blocks sans-serif, and then attempt to use monospace indentation—the end result looks worse, not better, than no leading indentation.
I also use UPPER case for SQL Keywords to visually distinguish them from user-defined names (i.e. similar purpose to syntax coloring), but I don't collapse SELECT over multiple lines and line up each statement so the conditions line-up, e.g:
SELECT foo, bar
FROM baz
WHERE foo > 3
AND bar = 'craig.kerstiens@gmail.com'
Yes, readability is good, it makes any scripting easier. SQL is no exception. Hand in hand with this is calling things what they are and good comments for the 'why' rather than the 'what' parts of a statement.
I make very liberal use of functions in SQL, as much as I would in other languages - any complex calculation or WHERE clause I'll usually abstract out into a function with a meaningful name. Postgres has nice syntactic sugar that makes these look pretty much like columns, so if your function takes a row type as an argument, you can just call it as `row.function_name` instead of `function_name(row)`. With the right hints performance is fine, and where it's not, it's possible to build indexes over function call results.
The article touches on CTEs, while mentioning they are on optimisation boundaries on some platforms (e.g. Postgres). This touches on my main annoyance with SQL - most of the abstractions are very costly. Even just phrasing a query in the simplest, most natural way is rarely optimal. I spend hours of my life wondering why query planners are so deliberately obtuse. And there are countless specific little annoyances, e.g. 'percentile_disc' seeming like a natural fit for calculating a median, but being wildly slower than more hacky solutions.
I would love to know what's next, to be honest. A readable query language, which captures the intention and semantics of a query clearly and minimally, is designed to allow abstractions to be composed together, and compiles to a mostly optimal query plan. I'd probably settle for SQL as-is, if there was an RDBMS with a query planner that was willing to go away and think for five minutes so I could just write the SQL I wanted without thinking too hard about the implementation.
Anyway, the formatting's important, but like any language, the way you can create and compose abstractions, and reveal the intention of your code is the biggest thing for readability.
It isn't just you, and indeed, the alignment examples make little sense with a proportional typeface.
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).
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.
IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
If you like the formatter in the current version of Prompt, wait until you see what's coming out in version 7.3. They've gone nuts. The new mechanisms for formatting are far beyond anything we've had so far. I'm pretty jazzed about it (disclosure: I work for Redgate).
So I think it is much easier to read if you can line up like clauses:
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 <= date_sub(from_unixtime(unix_timestamp()), 2) AND
spf.lstg_site_id = 0 AND
prod_map.ENTITY_ATTR_TYPE_DESC IN ('BMPN','GTIN') AND
spf.CTLG_ID = 1075 AND
spf.LSTG_TYPE_CD NOT IN (10,12,15)
GROUP BY spf.blah_blah;
The library is built on the Jinja template engine. You can create macros, functions and other reusable sql fragments, and then use them to create the query. You can also write loops and conditionals, so you can do all the things you routinely do to generate HTML.
The library tracks bind parameters, and doesn't let user input into the query. At the end of the day, you get the generated SQL and an array of bind parameters. You can use these two to execute the query using whatever database & driver combination you like.
Dunno 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 (<= best imo);
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.
ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators.
Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL.
Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere.
My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior.
People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).
Yes. And PL/Perl is perversely fun. I don't think I've ever seen other languages used in production, though[1]; I think folks tend to stick to PLSQL because of defaults.
[1] I'm sure people do, but this discussion has an anecdata deficiency.
In Enterprise Land a lot of people use ETL tooling as an alternative / in addition to writing SQL.
Databases, data interchange formats, and the concepts they represent are mapped out in graphical tools like ER Studio.
The SQL operations you do to load in data from various sources are done in graphical ETL tools like Microsoft SSIS, Talend etc let you draw diagrams which replace SQL. They are more properly a way of programming an engine that runs the SQL but also does things like monitor for incoming files, query webservices etc. Some also provide a technology neutral way to express transforms that can be run in SQL but also on Hadoop or whatever. That said, you sometimes need to go in and write the SQL for optimization.
Enterprise Service Bus sit between multiple databases and multiple data sources and consumers, and the interfaces these provide replace SQL queries. Typically graphical tools or configuration files are used to configure the relationship between the endpoints they provide and databases.
Even outside enterprise land, people rarely write SQL when defining and interfacing with databases. They use an ORM, and a lot of popular ORMs will create a database and provide a way of accessing it so that you never write SQL.
Technologies like OData mean that applications can query data by proxy without needing to know the model it is stored as, and the mapping between the model OData presents and the database is typically implemented using an ORM.
Cloud computing means people with very large databases increasingly want to spread the processing across many slower machines rather than spending the budget on one specialist data processing server with very fast IO. SQL is replaced by other languages in this case (although SQL like languages are making a bit of a comeback).
Finally, in academia and certain very data datacentric industries, SPARQL and successors are still a thing.
I think there is a big divide between enterprise development and what we learn in school / hackernews here. A lot of people outside the industry are not aware of these ecosystems of applications.
I wouldn't recommend any of these over SQL in all cases. They are technologies that solve a problem and you should consider them if you have that problem.
Hum.. SQL is a "mathematically pure" language, like Lisp or, to a smaller extent, Haskell. Those tend to not attract criticism (mostly because they age slowly, but also because the most vocal language critics are biased towards them).
SQL is also a standardized language. People tend to see those as something you should deal with, not something you should improve. Thus less criticism.
It's also a language that is mostly embedded inside others. So any weakness is easy to compensate on the outer code.
Besides this, it is a pretty well designed language.
The main trouble is that even if a better alternative is suggested, how convince the RDBMS developers to use it?
Unfortunately, RDBMS are highly coupled. I remember when someday I ask if is possible to remove the SQL part of sqlite and substitute with my own, and get laughed!
RDBMS are "expected" to be a black box enclosed in dark mystery.
I use it (way more than a lot) and I love it, but we could do better.
1) It really needs algebraic data types (and pattern matching!). I refuse to use a general purpose language that doesn't have them, and I always end up struggling to represent them using SQL.
2) Null, and the subsequent binary-but-maybe-ternary logic that it imposes on everything is a terrible hazard. It needs Option types. I guess this goes along with algebraic data types, but it is important enough to merit its own mention.
3) It needs more expressive constraints: foreign keys on partial unique indexes or views, declarable immutability, window constraints, etc.
4) It needs better graph representation and queryability. Anything remotely resembling a graph (or even trees!) ends up as a huge incomprehensible hack. Graphs are still relational, they're just ignored in favor of constructs that only tenably represent them.
5) It needs better ways to present output. Dumping everything as a table leaves the end user to their various hacky devices if what they really want is not an array of flat data structures. Users should be able to easily output data as a graph, tree, arbitrarily nested structs, associative maps, sets, arrays, and even scalars.
6) It needs extensive compile time checking of queries and statements, complete with errors and warnings and lints. As it stands right now, you only get syntax validation and checks for object existence. And although this is implementation level and not language level, SQL engines need to provide way better IDEs to compliment the better compilation.
7) It needs phantom keys for multi-column foreign keys against multi-column primary-keyed tables. As it stands right now, I have to lose the semantic benefits of a natural key in favor of a surrogate key in order to not duplicate a large number of columns in a 1-many table relationship. I should be able to use the many-columned natural foreign key, but invisible and behind the scenes map to a surrogate primary key.
> CTEs: First, yes they can be an optimisation boundary.
Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.
select
col1
,col2
,col3
from table1 a
left join table 2 b
on a.col1 = b.col2
where 1=1
and col1 = 'condition'
and col2 = 'condition2'
;
* everything lower case (except strings)
* leading commas
* conditions indented by two spaces
* select columns indented by two spaces, except the first colunm which is indented by three.
* where 1=1 for easier commenting/uncommenting of conditions
Came here to find out who else uses leading commas. I learned this several years ago at my current job and love it. I find it much more readable and also makes adding to the list clearer for some reason. Plus if you ever need to insert like 50 commas at the start of a bunch of lines in an ad hoc query it is much easier at the start of the line (yes, I am an animal).
167 comments
[ 4.7 ms ] story [ 237 ms ] threadIn PHP / Python / Go when inlining SQL I also line up sequential items indentation with spaces (which most editors do by default when hitting enter inside a multi line string).
Nice to see this codified. I really just wish I would have worked more in my career on teams that care as much about readability and comprehension in 6+ months as I do.
Hopefully more people will follow the advice in the article and write cleaner, easier to mentally parse SQL.
<code> SELECT foo, bar, blah FROM baz WHERE foo > 3 AND blah = 'dont use baz' </code>
1. http://poorsql.com/
2. SSMS plug-in: http://architectshack.com/PoorMansTSqlFormatter.ashx#Downloa...
Your life reading and saving SQL will change forever.
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:
or even:I think I usually end up with the later because I think it makes more sense.
Sometimes you want to remove/comment out the first line, sometimes the last, sometimes one in the middle.
Surely you mean tabs? runs
Into my arms.
In this style that's done in other ways so there is no need.
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?
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:
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).
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:
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)
They are at the top of the hierarchy of information I want when I'm reading a query.
The information I want to be able to identify the quickest are.
1. Tables/view names
2. How they are joined
3. Columns
4. Filters
5. Grouping/Ordering/Anything Else
Never realized I could do tuple compare and will probably adopt that.
I have been following this pattern for over a decade. I believe I picked it up from some project's style guide, but I never found it again.
That's why in most programming languages the coding style says you should put the operators at start of line instead of end of line, e.g.
GOOD:
BAD: Examples:- Python PEP-0008: "Should a line break before or after a binary operator?" https://www.python.org/dev/peps/pep-0008/#should-a-line-brea...
- Topvoted SO answer for "If you break long code lines, how do you indent the stuff on the next line?" http://stackoverflow.com/a/699347
There are the exceptions of very widely used projects where contributors will only look at the code a couple times when making a change to be pushed upstream or where there are significantly junior developers that need a narrow scope of the language to help them with the learning curve and getting up to speed with the code base.
In my experience(which is very anecdotal I admit), the requirement for consistency is more about a particular personality trait that many developers have - the need for order and control - as opposed to a requirement for quality production code.
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)
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).
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.And, like you, be pragmatic. If it is a 1 liner, keep it a 1 liner.
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.
In short, it has nothing to do with purposeful perfect vertical alignment in code.
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:
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.
I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.
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.
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. :)
select
t1.col1
, t2.col2
, t3.col3
, (case when t1.col1 = 'Y'
, t3.col2from table1 t1
join table2 t2 on t1.col2 = t2.col1
join table3 t3 on (t1.col3 = t3.col1
)where t1.col1 > 0
and t2.col2 <> t1.col4
order by
col2
, col1
limit 100
But I think it's an important caveat that the whole team use the same formatter and the same settings.
Otherwise diff's turn to useless noise* because Cubicle Bob has personalized his settings.
*Yes, I've tried the Winmerge plugin. It's helpful but not ideal.
They just released a beta for SSMS 2016 that has a lot more flexible SQL formatting features. See http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-....
https://news.ycombinator.com/item?id=12671667 (146 comments)
What would you do when you're selecting 20 columns from 5 different tables joined together?
The article touches on CTEs, while mentioning they are on optimisation boundaries on some platforms (e.g. Postgres). This touches on my main annoyance with SQL - most of the abstractions are very costly. Even just phrasing a query in the simplest, most natural way is rarely optimal. I spend hours of my life wondering why query planners are so deliberately obtuse. And there are countless specific little annoyances, e.g. 'percentile_disc' seeming like a natural fit for calculating a median, but being wildly slower than more hacky solutions.
I would love to know what's next, to be honest. A readable query language, which captures the intention and semantics of a query clearly and minimally, is designed to allow abstractions to be composed together, and compiles to a mostly optimal query plan. I'd probably settle for SQL as-is, if there was an RDBMS with a query planner that was willing to go away and think for five minutes so I could just write the SQL I wanted without thinking too hard about the implementation.
Anyway, the formatting's important, but like any language, the way you can create and compose abstractions, and reveal the intention of your code is the biggest thing for readability.
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).
(Quite liked SQL Prompt's auto formatter, but a shame so hideously expensive).
`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.
The first example is supposed to read:
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.
The library is built on the Jinja template engine. You can create macros, functions and other reusable sql fragments, and then use them to create the query. You can also write loops and conditionals, so you can do all the things you routinely do to generate HTML.
The library tracks bind parameters, and doesn't let user input into the query. At the end of the day, you get the generated SQL and an array of bind parameters. You can use these two to execute the query using whatever database & driver combination you like.
(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)
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 (<= best imo);
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.
You can also write stored procedures in Postgres in other languages.
Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL.
Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere.
My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior.
People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).
PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but aren't included. See https://www.postgresql.org/docs/9.6/static/external-pl.html
[1] I'm sure people do, but this discussion has an anecdata deficiency.
Databases, data interchange formats, and the concepts they represent are mapped out in graphical tools like ER Studio.
The SQL operations you do to load in data from various sources are done in graphical ETL tools like Microsoft SSIS, Talend etc let you draw diagrams which replace SQL. They are more properly a way of programming an engine that runs the SQL but also does things like monitor for incoming files, query webservices etc. Some also provide a technology neutral way to express transforms that can be run in SQL but also on Hadoop or whatever. That said, you sometimes need to go in and write the SQL for optimization.
Enterprise Service Bus sit between multiple databases and multiple data sources and consumers, and the interfaces these provide replace SQL queries. Typically graphical tools or configuration files are used to configure the relationship between the endpoints they provide and databases.
Even outside enterprise land, people rarely write SQL when defining and interfacing with databases. They use an ORM, and a lot of popular ORMs will create a database and provide a way of accessing it so that you never write SQL.
Technologies like OData mean that applications can query data by proxy without needing to know the model it is stored as, and the mapping between the model OData presents and the database is typically implemented using an ORM.
Cloud computing means people with very large databases increasingly want to spread the processing across many slower machines rather than spending the budget on one specialist data processing server with very fast IO. SQL is replaced by other languages in this case (although SQL like languages are making a bit of a comeback).
Finally, in academia and certain very data datacentric industries, SPARQL and successors are still a thing.
I think there is a big divide between enterprise development and what we learn in school / hackernews here. A lot of people outside the industry are not aware of these ecosystems of applications.
I wouldn't recommend any of these over SQL in all cases. They are technologies that solve a problem and you should consider them if you have that problem.
SQL is also a standardized language. People tend to see those as something you should deal with, not something you should improve. Thus less criticism.
It's also a language that is mostly embedded inside others. So any weakness is easy to compensate on the outer code.
Besides this, it is a pretty well designed language.
Unfortunately, RDBMS are highly coupled. I remember when someday I ask if is possible to remove the SQL part of sqlite and substitute with my own, and get laughed!
RDBMS are "expected" to be a black box enclosed in dark mystery.
1) It really needs algebraic data types (and pattern matching!). I refuse to use a general purpose language that doesn't have them, and I always end up struggling to represent them using SQL.
2) Null, and the subsequent binary-but-maybe-ternary logic that it imposes on everything is a terrible hazard. It needs Option types. I guess this goes along with algebraic data types, but it is important enough to merit its own mention.
3) It needs more expressive constraints: foreign keys on partial unique indexes or views, declarable immutability, window constraints, etc.
4) It needs better graph representation and queryability. Anything remotely resembling a graph (or even trees!) ends up as a huge incomprehensible hack. Graphs are still relational, they're just ignored in favor of constructs that only tenably represent them.
5) It needs better ways to present output. Dumping everything as a table leaves the end user to their various hacky devices if what they really want is not an array of flat data structures. Users should be able to easily output data as a graph, tree, arbitrarily nested structs, associative maps, sets, arrays, and even scalars.
6) It needs extensive compile time checking of queries and statements, complete with errors and warnings and lints. As it stands right now, you only get syntax validation and checks for object existence. And although this is implementation level and not language level, SQL engines need to provide way better IDEs to compliment the better compilation.
7) It needs phantom keys for multi-column foreign keys against multi-column primary-keyed tables. As it stands right now, I have to lose the semantic benefits of a natural key in favor of a surrogate key in order to not duplicate a large number of columns in a 1-many table relationship. I should be able to use the many-columned natural foreign key, but invisible and behind the scenes map to a surrogate primary key.
Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.
I know the maintainer, they also run a service at https://sqlformat.org/
You can try it on the command line e.g. on Fedora: