13 ms·
Best practices for writing SQL queries
- nerdbaggy 5y agoDoes anybody else like putting from first? I find it makes the auto complete sooo much better and easier to read.
- chubot 5y agoI like it, but sqlite doesn't seem to accept it? At least not in the version on my Ubuntu machine. Is putting from first standard SQL?
- deleted 5y ago[deleted]
- SigmundA 5y agoXquery and Linq both use FLWOR like syntax which puts the "FROM" first and helps auto complete, wish SQL had ordered things this way: https://en.wikipedia.org/wiki/FLWOR https://en.wikipedia.org/wiki/FLWOR SELECT first_name FROM person WHERE first_name LIKE 'john' becomes: FROM person WHERE first_name LIKE 'john' SELECT first_name SQL reads more English like while from first is more Yoda speak but the auto-complete is worth more to me.
- 7952 5y agoI think it would be easier to order things in terms of when they are executed. And perhaps it would be easier to teach SQL if the different parts where more obviously separate. As the different parts are actually distinct and don't really cross over. But to a newbie would seem procedural when it's not.
- SigmundA 5y ago>LIKE compares characters, and can be paired with wildcard operators like %, whereas the = >operator compares strings and numbers for exact matches. The = can take advantage of indexed columns. Unless this specific to certain databases, LIKE can take advantage of indexes too, without wildcards LIKE should be nearly identical in performance to = both seeking the index. >Using wildcards for searching can be expensive. Prefer adding wildcards to the end of strings. Prefixing a string with a wildcard can lead to a full table scan. Which is contradictory to the first quote, it seems you recognize that a wildcard at the end can take advantage of an index. Full table scan is the same thing as not taking advantage of an index, hence LIKE can take advantage of normal indexes so long as there are characters before the first wildcard or has no wildcards.
- Hjfrf 5y agoLIKE 'abc%' will use indexes but LIKE '%abc' will not. At least for the latest versions of every database. If you go back to a version from 10+ years ago there's no guarantees.
- SigmundA 5y agoPedantically if your database supports index scans it can use the index on the column to scan for '%abc' rather than the whole table which can be much faster while not as a fast as a seek. It can only do a seek if there are character before the wildcard: 'ab%c', 'abc%' and 'abc' getting progressively faster due to less index entries transversed.
- tomnipotent 5y ago> it can use the index on the column to scan for '%abc' Using an index would just mean more overhead to fetch data later, so optimizers will prioritize a table scan in these cases since it would have less cost.
- deathanatos 5y agoI think it would depend, wouldn't it? If the query can be answered directly from an index (there exists some index containing all of the columns required by the query) then an index scan would suffice and be faster by virtue of not having to scan all the data (the index would be smaller by not including all columns). I believe most modern DB query optimizers are capable of this. If there isn't such an index, then it's a toss up: yes, going to the main table to fetch a row has a cost, but if there are only a few rows answered by the query, then it might be worth it. If there are many rows, that indirection will probably outweigh the benefit of the index scan & we'd be better off with a table scan. This would require an optimizer to estimate the number of rows the query would find. I don't know if modern DB query optimizers would do this or not. (And my naïve guess would be "they don't", specifically, that the statistics kept are not sufficiently detailed to answer any generalized LIKE expression.)
- eirki 5y ago> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.
- dragonwriter 5y agoI agree with the source that the latter (explicit table specification in the SELECT list, whether using aliases or not) is to be preferred to the former; at the same time (while I am sometimes guilty of using them) I agree that single-character aliases are generally a poor choice for the same reasons that’s generally true of single character identifier names; column aliases are variable (well, constant) names and the usual rules of meaningful identifier names apply.
- GordonS 5y agoIt seems to be a matter of personal preference, but I've never liked single-character aliases myself, and never understood why so many seem to.
- dspillett 5y agoLazy typing: t is shorter than tableWithTheDataIWantIn I prefer descriptive table and other object names, and abbreviate them in aliases within queries (though usually not to single letters).
- markmark 5y agoIt's not just about lazy typing it's about removing unnecessary clutter from large queries that makes things harder to read. In the author/books example, repeating the words author and books a dozen times doesn't convey any information that a and b don't, but clutters up the query making it harder to see the useful parts.
- hn_throwaway_99 5y ago
- johnvaluk 5y agoOverall an enjoyable read, but as someone who includes SQL queries in code, I disagree with two points: I despise table aliases and usually remove them from queries. To me, they add a level of abstraction that obscures the purpose of the query. They're usually meaningless strings generated automatically by the tools used by data analysts who rarely inspect the underlying SQL for readability. I fully agree that you should reference columns explicitly with the table name, which I think is the real point they're trying to make in the article. While it's true that sorting is expensive, the downstream benefits can be huge. The ability to easily diff sorted result sets helps with troubleshooting and can also save significant storage space whenever the results are archived.
- alex_anglin 5y agoTo each their own, but in the case of ETL/ELT, you would just be asking for pain not using aliases.
- gizmodo59 5y agoEven there someone needs to read them eventually than just the person who wrote it. Single letter aliases are just evil. In some ways it’s the same as doing: String x = “Hello”
- dragonwriter 5y ago> Even there someone needs to read them eventually than just the person who wrote it. That’s not an argument against table aliases, its an argument against unclear table aliases. Single letter table aliases are better than just using unqualified column names, both of which are worse than table aliases guided by the same naming rules you’d use for semantically-meaningful identifiers in regular program code.
- mulmen 5y agoIn the case of ETL you should only be referencing those tables a few times because you are integrating them into friendly analytic models. In that case you probably have a lot of columns to wrangle and complex transformation logic. In those cases I prefer to use no alias at all to avoid the scrolling around to get context, even when table names are very long.
- magicalhippo 5y agoI'd add "be aware of window functions"[1]. Certain gnarly aggregates and joins can often be much better expressed using window functions. And at least for the database we use at work, if the sole reason for a join is to reduce the data, prefer EXISTS. [1]: https://www.sqltutorial.org/sql-window-functions/ https://www.sqltutorial.org/sql-window-functions/
- Hjfrf 5y agoLots of mistakes (or at least rare opinions going against the crowd) here. Here's a better general performance tuning handbook - https://use-the-index-luke.com/ https://use-the-index-luke.com/
- willvarfar 5y agoMore that its a dumbed-down general guide aimed at meta base users? Use-the-index-luke is an altogether deeper, more technical article aimed at data engineers and going into the details and differences between databases.
- Tomis02 5y ago> aimed at data engineers I disagree. Professional developers should know their database of choice inside and out, and use-the-index-luke helps with that. You can skip the details about databases that aren't relevant to you.
- hn_throwaway_99 5y agoThis is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col < 2 ORDER BY a.foo
- Hjfrf 5y agoDoes that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?
- hn_throwaway_99 5y agoAnother commenter showed how this works: SELECT a.foo , b.bar , g.zed FROM ... While the comma placement may seem weird, it makes this exactly identical to the "AND" or "OR" placement in WHERE clauses, and the primary benefit is that it's easy to comment out any column except the first.
- jolmg 5y ago> While the comma placement may seem weird It's not completely unconventional. Haskell is typically styled with that kind of comma usage, too. For example, [ 1 , 2 ] { foo = 1 , bar = 2 } Coincidentally, SQL and Haskell are the only languages I know that use `--` for comments.
- carbocation 5y agoPersonal habit is to start my WHERE clause with a TRUE or a FALSE so that adding or removing clauses becomes seamless: SELECT foo FROM bar WHERE TRUE AND baz > boom For OR conditions it's a bit different: SELECT foo FROM bar WHERE FALSE OR baz > boom
- warent 5y agothis seems like taking on a pretty huge risk for a minor convenience. the difference between those two queries can mean the difference between protecting someone's PII
- carbocation 5y agoI'm not sure that I follow. The two queries are to demonstrate difference in form; they are not intended to be equivalent. If you're already writing: WHERE foo=bar AND biz=baz It's not clear to me how: WHERE TRUE AND foo=bar AND biz=baz is worse.
- shazzdeeds 5y agoHe’s saying if someone gets in the habit of using that style they have to be very careful. If they forget to change True to False when using an OR that it could have major consequences. Performance being the least of concerns.
- carbocation 5y agoI agree that such an error would be of the catastrophic type. It's interesting that several people seem to perceive this formatting approach as something that would increase the risk of that error. Is the red flag for people the WHERE TRUE on one line? Like, would this be less alarming to people? WHERE TRUE AND x=y
- magicalhippo 5y agoYeah, I almost always do "where 1=1" with the actual expressions AND'ed below. For OR, I like to keep the "1=1" and do AND (1=2 OR ... )
- jjice 5y agoDoes anyone have any good resources for practicing SQL queries? I recently had an interview where I did well on a project and the programming portions, but fumbled on the more SQL queries that were above basic joins. I didn't realize how much I need to learn and practice. I don't know if my lack of knowledge was enough to cost me the position or not yet, but I'd like to prepare for the future either way. I've seen a few websites, but I don't know which ones to use. Or maybe there is a dataset with practice question I could download? Edit: I found https://pgexercises.com https://pgexercises.com and it's been fantastic so far. Much more responsive than other sites, clear questions, and free.
- higeorge13 5y agoThe one you found is good for basic queries, but misses a lot of basic sql usage scenarios, such as window functions. It is PG-oriented, but i suggest https://www.postgresqltutorial.com https://www.postgresqltutorial.com. It also contains a sample database to practise (https://www.postgresqltutorial.com/postgresql-sample-database/ https://www.postgresqltutorial.com/postgresql-sample-databas...).
- macando 5y agoThis article nudge me to google "SQL optimization tool". I found one that says: "Predict performance bottlenecks and optimize SQL queries, using AI". Basically, it gives you suggestions on how to improve your queries. I wonder what the results would be if I ran the queries from this article through that tool.
- the_arun 5y agoAnyone using Metabase? Is it worth having self hosted and managing it?
- supernova87a 5y agoIs there a good place to read from an advanced casual "lay user's" perspective what SQL query optimizers do in the background after you submit the query? I would love to know, so that I can know what optimizations and WHERE / JOIN conditions I should really be careful about making more efficient, versus others that I don't have to worry because the optimizer will take care of it. For example, if I'm joining 2 long tables together, should I be very careful to create 2 subtables with restrictive WHERE conditions first, so that it doesn't try to join the whole thing, or is the optimizer taking care of that if lump that query all into one entire join and only WHERE it afterwards? How do you tell what columns are indexed and inexpensive to query frequently, and which are not? Is it better to avoid joining on floating point value BETWEEN conditions? And other questions like this.
- wolf550e 5y agoYou basically only need to know one thing to answer all your questions: use EXPLAIN PLAN. Postgresql has "explain analyze", which is even better than simple "explain", but all SQL databases have "explain", because they are kinda useless without it. The database will tell you what it's going to do (or what it did) and you will decide whether that's ok or whether it's doing something stupid (e.g. full table scan when only 1% of rows is needed), and then you can try things to get the plan that you want (ensuring statistics are up to date, adding indexes, changing the query, etc). Databases have ways to query the schema which includes the index definitions, so you can know which columns and indexed (and the order of the columns in those indexes). Unless you materialize a temporary table or materialized view or use a CTE with a planner that doesn't look inside CTEs, the planner will just "inline" your subqueries (what are "subtables"?) and it will not affect the way the join is performed. Join on floating point value is quite rare. Why do you need to do that?
- supernova87a 5y ago>Join on floating point value is quite rare. Why do you need to do that? Ah, thanks for noticing this. They are, for example, (1) tables of timestamped events, and (2) tables of time ranges in which those events need to be associated with (but which unfortunately were not created with that in mind at the time)... So for example FROM tableA LEFT JOIN tableB ON (timestampA BETWEEN timestampB1 AND timestampB2) (and where the timestamps can be either floating point or integer nanoseconds)
- tremon 5y agoAvoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, provided an index on the field column exists. Prefer = to LIKE And therefore this advice is wrong. As long as your LIKE expression doesn't start with a wildcard, LIKE can use an index just fine. Filter with WHERE before HAVING This usually isn't an issue, because the search terms you would use under HAVING can't be used in the WHERE clause. But yes, the other way around is possible, so the rule of thumb is: if the condition can be evaluated in the WHERE clause, it should be. WITH Be aware that not all database engines perform predicate propagation across CTE boundaries. That is, a query like this: WITH allRows AS ( SELECT id, result = difficult_calculation(col) FROM table) SELECT result FROM allRows WHERE id = 15; might cause the database engine to perform difficult_calculation() on all rows, not just row 15. All big databases support this nowadays, but it's not a given. [1] https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
- richardeb 5y agoAdvice would have to be tailored to specific database technologies and probably specific versions. For example, in Apache Impala and Spark, "Prefer = to LIKE" is good advice, especially in join conditions, where an equijoin would allow the query planner to use a Hash Join, whereas a non equijoin limits the query planner to a Nested Loop join.
- musingsole 5y agoThis is ultimately my problem with databases. We use the term as a catchall, but every implementation is different and is unified only in that they store tables and can respond to SQL. People treat deciding your app will have a database as a design decision when in reality it is only about 10% of a design decision.
- 5y ago
- yardstick 5y ago> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. Don’t most databases figure this out as part of the query planner anyway? Postgres has no problems using indexes for joins inside WHERE.
- Hjfrf 5y agoYes all databases will use indexes for joins. There's quite a few mistakes like that. My guess is the author heard something about not using implicit inner joins (deprecated decades ago) and misunderstood. E.g. This old syntax- SELECT * FROM a, b WHERE a.id = b. a_id
- ineedasername 5y agoprefer an explicit JOIN Yes absolutely, and not just for performance benefits. It's much easier to track what, how, and why you're joining to something when it's not jumbled together in a list of a dozen conditions in the WHERE clause. I can't tell you how much bad data I've had to fix because when I break apart the implicit conditions into explicit joins it is absolutely not doing what the original author intended and it would have been obvious with an explicit join. And then in the explicit join, always be explicit about the join type. don't just use JOIN when you want an INNER JOIN. Otherwise I have to wonder if the author accidentally left off something.
- c2h5oh 5y agoCTE advice is somewhat questionable, as it is database specific. CTEs were for a very long time an optimization fence in PostgreSQL, were not inlined and behaved more like temporary materialized views. Only with release of PostgreSQL 12 some CTE inlining is happening - with limitations: not recursive, no side-effects and are only referenced once in a later part of a query. Mode info: https://hakibenita.com/be-careful-with-cte-in-postgre-sql https://hakibenita.com/be-careful-with-cte-in-postgre-sql
- haolez 5y agoMetabase is an amazing product, but I'm using Superset[0] in my company because it supports Azure AD SSO, which became a necessity for us. But as soon as this feature appears in Metabase, we are switching. [0] https://superset.apache.org/ https://superset.apache.org/
- cornel_io 5y agoNB: this post is mostly performance advice, and it only applies to traditional databases. Specifically, it is not good advice for big data columnar DBs, for instance a limit clause doesn't help you at all on BigQuery and grabbing fewer columns really does.
- jitans 5y agonot even all the "traditional" databases, each Engine has his own peculiarities.
- bob1029 5y agoThis seems like reasonable discussion, but you would get far more traction if you have an opportunity to write an entire schema from scratch in the proper way. Not having to fight assumptions along lines of improperly denormalized columns (i.e. which table is source of truth for a specific fact) can auto-magically simplify a lot of really horrible joins and other SQL hack-arounds that otherwise wouldn't be necessary. The essential vs accidental complexity battle begins right here with domain modeling. You should be seeking something around 3rd normal form when developing a SQL schema for any arbitrary problem domain. Worry about performance after it's actually slow. A business expert who understands basic SQL should be able to look at and understand what every fact & relation table in your schema are for. They might even be able to help confirm the correctness of business logic throughout or even author some of it themselves. SQL can be an extremely powerful contract between the technology wizards and the business people. More along lines of the original topic - I would strongly advocate for views in cases where repetitive, complex queries are being made throughout the application. These serve as single points of reference for a particular projection of facts and can dramatically simplify downstream queries.
- grzm 5y agoOne of the strengths of Metabase is that it can plug into a variety of data sources, not just RDBMS. For example, AWS Athena over data in S3 buckets. Good design can still make things easier, of course, but not always an option. Relational purity is not going to be an option in such circumstances, so are in my opinion correctly not addressed in the piece.
- mulmen 5y agoMetabase sounds like a great tool for building clean analytic schemas then. You still need to design those schemas though.
- iblaine 5y ago"Best practices for writing SQL queries in metabase" should be the title here. 10 or so years ago when SQL Server, Oracle & MySQL dominated the industry, you could talk about SQL optimization with the expectation that all advice was good advice. There are too many flavors of databases to do that today.
- deleted 5y ago[deleted]
- colonwqbang 5y ago> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. This implies that WHERE style join can't use indices. I can understand why some would prefer either syntax for readability/style reasons. But the idea that one uses indices and the other not, seems highly dubious. Looking at the postgres manual [1], the WHERE syntax is clearly presented as the main way of inner joining tables. The JOIN syntax is described as an "alternative syntax": > This [INNER JOIN] syntax is not as commonly used as the one above, but we show it here to help you understand the following topics. Maybe some database somewhere cannot optimise queries properly unless JOIN is used? Or is this just FUD? [1] https://www.postgresql.org/docs/13/tutorial-join.html https://www.postgresql.org/docs/13/tutorial-join.html
- SigmundA 5y agoYes the optimizer should end up with the same plan either way, although the ON syntax is SQL standard.
- joelcollinsdc 5y agoMaybe not for indexes but what about using a sql syntax that is more common and extensible?
- FranzFerdiNaN 5y agoI don’t think I have ever seen that way of doing an inner join in the wild, despite working as a DBA or data engineer for the past 15 years, 10 of those Postgres-only roles.
- matwood 5y agoI started my dba roles back with mssql 6.5, and the join using where was all that was supported. I found the join syntax much more clear around intent and moved as soon as it was available.
- mtone 5y ago> This syntax is not as commonly used as the one above This is going to need some sources. Is it true today? And why did they put parentheses in the ON condition? Worth nothing that there were variants of the WHERE syntax to support left joins using vendor-specific operators such as A += B, A = B (+) -- those are clearly deprecated today. [1] [2] I have a really hard time finding any source on the internet that recommends using the WHERE style joins. So by extension, I wouldn't expect to be used much anymore except for legacy projects. MS SQL Server docs docs mention ON syntax being "preferred" [3], and MySQL says "Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set." [4] The PostgreSQL docs seem misleading and outdated to me. [1] https://docs.microsoft.com/en-us/archive/blogs/wardpond/deprecation-of-old-style-join-syntax-only-a-partial-thing https://docs.microsoft.com/en-us/archive/blogs/wardpond/depr... [2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri... [3] https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [4] https://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.7/en/join.html
- AdrianB1 5y agoSorry to rain on your parade, but there is nothing in that article that is not included in the basic SQL manuals like Itzik Ben-Gan's. Also a few things are dead wrong: the "make the haystack small" is optimization (it should be at the end, as the first rule says), the "prefer UNION ALL to UNION" is missing the context (a good dev knows what is needed, not what to prefer) and the usage of CTEs is nice, but sometimes slower that other options and in SQL slower can easily be orders of magnitude, so nice is not enough. Same for 'avoid sorting where possible, especially in subqueries' or "use composite indexes" (really? it's a basic thing, not a best practice). In the past few months I interviewed and hired several DBAs, this list is ok-ish for a junior but a fail for a senior. I am not working for FAANG, so the bar is pretty low, this article would not even pass for a junior there.
- supercanuck 5y agothat is because this is an advertisement for Metabase and not an actual article.
- Justsignedup 5y agosome of these are inaccurate. "a = 'foo'" is exactly the same performance as "a like 'foo'" and very close to the performance as "a like 'foo%'" and is fully indexed. When you put a wildcard in the front, the entire index is avoided, so you gotta switch to full text search.