12 ms·
SQL Tips and Tricks
- eezing 2y ago[flagged]
- deleted 2y ago[deleted]
- dang 2y agoOk, but please don't post unsubstantive comments here.
- dooer 2y agoI am bad at SQL so this is great
- isoprophlex 2y agoWow, that EXCEPT trick is neat! ~10 years of using SQL almost daily, and I never knew...
- renhanxue 2y agoUnfortunately EXCEPT is almost never what you actually want. It's a set operator, like UNION, and just like UNION it also has the side effect of removing duplicates from the result set unless you explicitly say EXCEPT ALL. Because it's a set operator and not a join, it's usually very hard for the query planner to optimize it beyond the most trivial cases. It usually ends up being one of the last steps in the query plan. In a good query plan you almost always want to eliminate rows you don't care about as early as possible so you don't have to drag them along in every join operation only to have the data discarded at the end, but if you're using EXCEPT instead of the explicit anti-semi-join operator (that is NOT EXISTS(<subquery>)), you're making that very difficult for the query planner. EXCEPT and INTERSECT are sometimes a handy shortcut when you're writing some quick and dirty query by hand, but I have literally never used either of them in a production query. You almost always want to use EXISTS() and NOT EXISTS(). They explicitly communicate intent, which is appreciated both by people reading the query and by the query planner, and they lack the footguns some of the other alternatives have.
- the_gorilla 2y agoLeading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.
- willvarfar 2y agoA lot of databases support trailing commas in select clauses. Which is just as well. I want to scratch my eyes out every time I see someone formatting with comma starting the lines. It's the kind of foolish consistency that is a big part of performative engineering.
- silveraxe93 2y ago> I want to scratch my eyes out every time I see someone formatting with comma starting the lines Right!? I _physically_ recoil every time I see that. I think that's the clearest example of normalisation of deviance [1] I know. Seems like anyone that enters the industry straight from data instead of moving from a more (software) engineering background gets used to this. And the arguments in favour are always so weak! - It's easier to comment out lines - Easier to not miss a comma Those are picked up in seconds by the compiler. And are a tiny help in writing code vs violating a core writing convention from basically every other language. [1]- https://danluu.com/wat/ https://danluu.com/wat/
- disgruntledphd2 2y agoI'm a data person and despite seeing this for years, still despise that approach to commas. Seriously, it's not that hard to comment out the damn comma.
- yen223 2y agoPostgres and postgres-likes (e.g. Redshift) notably don't support trailing commas in select clauses.
- sgarland 2y agoNot shown: stop using SELECT *. You almost certainly do not need the entire width of the table, and by doing so, you add more data to filter and transmit, and also prevent semijoins, which are awesome.
- yen223 2y agoThere are broadly two kinds of people who write SQL: analysts, and developers For developers, yeah. SELECT * has pitfalls, and you should almost always specify your columns or use a query builder that does that for you. For analysts though, life is short and sometimes you really don't want to type all the columns out. SELECT * is fine.
- higeorge13 2y agoAnalysts usually query data warehouses, which are columnar, so * is a query/warehouse killer. Everybody should just select the columns they need.
- mr_toad 2y agoThis is another area where I wish SQL was more composable. I’d love to be able to specify a bunch of columns using a single reference or a function, without having to resort to dynamic sql. Exclude and rename are a start, but not enough.
- sgarland 2y agoSo make a VIEW, then SELECT * from that. Non-materialized views are effectively free as far as the DB is concerned; make hundreds of them if you want.
- egeozcan 2y agoI remember doing the "WHERE 1=1" trick in my last job and it causing a... let's say "unproductive", discussion in the pull-request.
- deleted 2y ago[deleted]
- hot_gril 2y agoWhat about `WHERE true`?
- abrookewood 2y agoI don't understand the point at all. If you need to add some condition later on, why not just add it then? What benefit is there to just marking out the spot where you might add the condition at some point in the future?
- DH61AG 2y agoIt is a bit silly but I think it just helps with code readability some people.
- azthecx 2y agoI personally don't use it too, but I think it's origins are not just readability, but from developing queries in a REPL like environment. As you develop and are constantly creating / debugging queries where you often add new and or or clauses as a whole line, that becomes much faster to add and remove those same lines as they're a single shortcut away in nearly all text editors.
- paperplatter 2y agoYeah, so often do I have an EXPLAIN ANALYZE query.txt file I'm repeatedly editing in one window and piping into psql in another to try and make something faster. So I put WHERE true at the top.
- 2y ago
- youdeet 2y agoOne more point in the "Anti Join". Use EXISTS instead of IN and LEFT JOIN if you only want to check existence of a row in another large table / subquery based on the conditions. EXISTS returns true as soon as it has found a hit. In case of LEFT JOIN and IN engine collects all results before evaluating.
- Semaphor 2y agoYeah, I was a bit confused there. In all my testing, (NOT) EXISTS was generating either a better plan or the same one as (LEFT) JOIN/(NOT) IN. In addition, it’s also clearer what the intent is.
- alex5207 2y agoNever knew about QUALIFY. That's great
- leosanchez 2y agoLooks like neither Postgres not SQL Server support QUALIFY
- magicalhippo 2y agoI'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values from the same table, even if your database server supports lateral joins. Just make sure the subqueries return at most one row. Any query that's not a one-off should not perform any table scans. A table scan today can mean an outage tomorrow. Add indexes. Keep in mind GROUP BY clause usually dictates index use. If you need to filter on expressions, say where a substring is equal something, you can add a computed column and index on that. Alternatively some db's support indexing expressions directly. Often using UNION ALL can be much faster than using OR, even for non-trivial queries and/or multiple OR clauses. edit: You can JOIN subqueries. This can be useful to force the filtering order if the DB isn't being clever about the order.
- Semaphor 2y ago> Any query that's not a one-off should not perform any table scans. A table scan today can mean an outage tomorrow. That very much depends on your data.
- magicalhippo 2y agoI should have noted that I was talking about application workloads. I don't have much experience with analytics workloads. If you have something else in mind, do feel free to elaborate.
- Semaphor 2y agoRelevant for applications as well, when a table only has a few thousand entries, a scan is not the end of the world and not even an outage in waiting. I agree with you that one should seek when possible as part of normal query optimization, but depending on your data, it could also just easily be something you can live with forever.
- silveraxe93 2y agoThe "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.
- yen223 2y agoI'm not the biggest fan of how the first two conventions look, but they are real conventions used by real SQL people. And I can understand why they exist. I've seen them enough to not be bothered by them any more.
- silveraxe93 2y agoYeah, unfortunately you're right that they are real conventions. Quite common too. I also _understand_ why they exist. It's simple: It makes code marginally easier to write. But writing confusing, unintuitive and honestly plain ugly code. Just so you can save a second after clicking run and the compiler tells you the mistake is a bad reason.
- arp242 2y agoA lot of "readability" depends on what you're used to and what you expect. I don't think these conventions are inherently "ugly" or "confusing", but they are different to what I've been doing for a long time, and thus unexpected, and thus "ugly". But that's extremely subjective. I've done plenty of SQL, and I've regularly run in to the "fuck about with fucking trailing commas until it's valid syntax"-problem. It's a very reasonable convention to have. What should really happen is that the SQL standard should allow trailing commas: select a, b, from t;
- jghn 2y ago> A lot of "readability" depends on what you're used to and what you expect. Yes. Typically shared sense of "readability" in a community for language X translates to "idiomatic patterns when writing X". There's no real thing as readability in a universal sense. It's a placeholder statement for "it's easier for ME to understand", double emphasis on "ME". Within a community, "readability" standards are merely channeling the idiomatic patterns within that community as for most members they'll be easier for the person to understand as it's what they're used to seeing.
- Semaphor 2y agoRegarding "Comment your code!": At least for MSSQL, it’s often recommended not to use -- for comments but instead /**/, because many features like the query store save queries without line breaks, so if you get the query from there, you need to manually fix everything instead of simply using your IDEs formatter.
- regexman1 2y agoI didn't realise that, great to know. Thanks!
- petters 2y agoThat sounds like a bug in the query store
- password4321 2y agoAre you able to cast as XML? I use that for OBJECT_DEFINITION, eg. select name,cast((select OBJECT_DEFINITION(object_id) for xml path('')) as xml) from sys.procedures This can be easier to straighten out since it preserves the newlines though other XML characters get mangled like > to >. One other option is VARBINARY plus something to un-hex it.
- AtNightWeCode 2y agoNever use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.
- regexman1 2y agoI'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.
- halayli 2y agoA random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around. OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause completely. OP probably saw '1=1' and threw that into the comment.
- regexman1 2y agoFair point!
- AtNightWeCode 2y agoRead my other comments. I worked with SQL on and off since the last century. It has nothing to do with your poor assumptions.
- the_gorilla 2y agoDuration of working with SQL doesn't matter. The better SQL programmers don't do it specifically, and have experience in real languages that they bring over to database queries.
- AtNightWeCode 2y ago
- philippta 2y agoI really like the formatting presented in this article: https://www.sqlstyle.guide/#spaces https://www.sqlstyle.guide/#spaces
- elchief 2y agouse sqlfluff linter and do what it says
- beart 2y agoAgreed! It may not be perfect, but it's better than arguing. And the maintainers are very responsive.
- l5870uoo9y 2y agoAnd I take it CTEs are implicitly being discouraged.
- regexman1 2y agoNot at all actually, I just hadn't really planned to add this as a tip. Additionally I thought an in-line view was fine for the examples included. But maybe I will!
- higeorge13 2y agoIt used to be like this (i remember in past postgres versions CTEs had worse performance than subqueries), but not anymore.
- deleted 2y ago[deleted]
- wodenokoto 2y agoEverybody is up in arms about the comma suggestion but everyone thinks the 1=1 is a good idea in the where clause? If I saw that in a code review I don’t know what I’d think of the author.
- AtNightWeCode 2y agoYou can motivate it with the same reasons as trailing commas. Making code reviews easier since changes to WHERE statements does not effect other lines. But if the reason is, as in this case to be able to add dynamic conditions. You will for sure be fired where I work.
- dspillett 2y agoOn readability, I often find aligning things in two columns is more readable. To modify the two examples in TFA: SELECT e.employee_id , e.employee_name , e.job , e.salary FROM employees e WHERE 1=1 -- Dummy value. AND e.job IN ('Clerk', 'Manager') AND e.dept_no != 5 ; and with a JOIN: SELECT e.employee_id , e.employee_name , e.job , e.salary , d.name , d.location FROM employees e JOIN departments d ON d.dept_no = e.dept_no WHERE 1=1 -- Dummy value. AND e.job IN ('Clerk', 'Manager') AND e.dept_no != 5 ; In the join example, for a simple ON clause like that I'll usually just have JOIN ... ON in the one line, but if there are multiple conditions they are usually clearer on separate lines IMO. In more complicated queries I might further indent the joins too, like: SELECT * FROM employees e JOIN departments d ON d.dept_no = e.dept_no WHERE 1=1 -- Dummy value. AND e.job IN ('Clerk', 'Manager') AND e.dept_no != 5 ; YMMV. Some people strongly agree with me here, others vehemently hate the way I align such code… WRT “Always specify which column belongs to which table”: this is particularly important for correlated sub-queries, because if you put the wrong column name in and it happens to match a name in an object in the outer query you have a potentially hard to find error. Also, if the table in the inner query is updated to include a column of the same name as the one you are filtering on in the outer, the meaning of your sub-query suddenly changes quite drastically without it having changed itself. A few other things off the top of my head: 1. Remember that as well as UNION [ALL], EXCEPT and INTERSECT exist. I've seen (and even written myself) some horrendous SQL that badly implements these behaviours. TFA covers EXCEPT, but I find people who know about that don't always know about INTERSECT. It is rarely useful IME, but when it is useful it is really useful. 2. UPDATEs that change nothing still do everything else: create entries in your transaction log (could be an issue if using log-shipping for backups or read-only replicas etc.), fire triggers, create history rows if using system-versioned tables, and so forth. UPDATE a_table SET a_column = 'a value' WHERE a_column <> 'a value' can be a lot faster than without the WHERE. 3. Though of course be very careful with NULLable columns and/or setting a value NULL with point 2. “WHERE a_column IS DISTINCT FROM 'a value'” is much more maintainable if your DB supports that syntax (added in MS SQL Server 2022 and Azure SQL DB a little earlier, supported by Postgres years before, I don't know about other DBs without checking) than the more verbose alternatives. 4. Trying to force the sort order of NULLs with something like “ORDER BY ISNULL(a_column, 0)”, or doing similar with GROUP BY, can be very inefficient in some cases. If you expect few rows to be returned and there are relatively few NULLs in the sort target column it can be more performant to SELECT the non-NULL case and the NULL case then UNION ALL the two and then sort. Though if you do expect many rows this can backfire badly and you and up with excess spooling to disk, so test, test, and test again, when hacking around like this.
- AtNightWeCode 2y agoA common mistake I see is that people think foreign keys will automatically create indexes. Missing indexes is a general problem in SQL. Missing indexes on columns that are in foreign keys are even worse.
- TheCycoONE 2y agoIn some RDBMS a foreign key will automatically create an index: https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html#foreign-key-restrictions https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign... I think this falls under the read the documentation fully point. Edit: It occurs to me you likely meant on the column itself rather than on the referenced column. I don't have an example that does that.
- AtNightWeCode 2y agoBoth columns needs the correct type of index. The best thing to do is to use a tool that scans for missing indexes.
- mergisi 2y ago[dead]
- rawgabbit 2y agoMy tips for working with complex Stored Procedures. 1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need. 2. In the middle of the proc, manipulate the temporary tables as needed. 3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is detected. (By following all three steps, this will improve concurrency and lets you restart the proc without manually cleaning up any data messes). 4. Use extreme caution when working with remote tables. Remote tables do not reside in your RDBMS and most likely will not utilize any statistics/indexes your RDBMS has. In many cases, it is more performant to dump/copy the entire remote table into a temporary table and then work with that. The most you can expect from a remote table is to execute a Where clause. If you attempt Joins or something complicated, it will likely timeout. 5. The Query Plan is easily confused. In some cases, the Query Plan will resort to perform row by row processing which will bring performance to a halt. In many cases, it is better to break up a complex stored procedure into smaller steps using temporary tables. 6. Always check the Query Plan to see what the RDBMS is actually doing.
- remus 2y ago1-3 are nice if you can guarantee your data is reasonably sized, but if it gets too big for your hardware taking copies of large datasets and then doing updates on large datasets can add a lot of overhead.
- wvenable 2y agoI've significantly improved the performance of queries by undoing someone who did #5 when it wasn't strictly needed. Sometimes breaking a query into many smaller queries is significantly less efficient than giving the query optimizer the entire query and letting it find the best route to the data. If you've done #5 without doing #6 then you'll likely not see that you're doing something not optimal. My advice is avoid premature optimization and do things the most straight forward way first and then only optimize if needed. Most importantly, don't code in SQL procedurally -- you're describing the data you want not giving the engine instructions on how to get it.
- the_gorilla 2y ago
- shmerl 2y agoI don't get the point of the dummy value. How does it help doing anything? I can add conditions with ease without it.
- otteromkram 2y agoBecause any subsequent clauses usually start with AND, so if you're just checking data or validating it outside of production, it makes sense since you can comment to see lines you aren't checking out. It also depends on how you write SQL, but not by much. I wouldn't put a dummy variable into a finalized query.
- brikym 2y agoI like SQL but I think it's time for the big players like MySQL, MSSQL, Postgres etc to start using FROM-first and piping syntax. I've had the pleasure of using Kusto query language and it's a huge leap forward in DX.
- password4321 2y agoI feel like a significantly more context-aware autocomplete could go a long way here, most SQL editors are approaching 72% garbage. It should be possible to make an educated guess at which tables are in play. Elsewhere I mentioned always fully qualifying entity references which would narrow the list of possibilities down to a more workable number in most cases.
- otteromkram 2y agoUgh. Don't put opening braces on new lines. As for formatting, indent the first field, too SELECT employee_id , employee_name , job , salary FROM employees ;
- jason-phillips 2y agoAlso, window functions.
- password4321 2y agoIs anyone willing to share general guidance on where to draw the line when it comes to using DB configuration to speed things up ( almost "buy") vs. basically doing things manually ("build")? In my limited experience it often falls to app developers because competent DB admins are all getting paid much more to work elsewhere (as mentioned above, it is important to know the DB). My canonical example is large volumes of data that accrue over time with the most recent accessed most often, where the DB admins can partition things or do partial indexes to keep access fast, but the app developers can move records into a separate archive table sometimes behind the scenes while still supporting things like (eventual) search of the whole data set. (A note here that it feels like a tool could do a lot of the initial heavy lifting to automate splitting one table into many when it makes sense -- perhaps when limited by a cloud DB's missing features) Another management option sometimes accommodated by the DB vs. doing manually is to store all large blobs/files in their own separate database (filesystem?!) for a different storage configuration etc. I imagine it can go as far as basically implementing an index manually: one massive table with just an auto-incrementing primary key but tons of columns then setting up a table with that ID and a few searchable columns (including up to going full text search/vectors I guess). Edit: one useful tip manually implementing the Materialized View pattern with MSSQL 2016+: use partition switching as well explained and implemented by https://github.com/cajuncoding/SqlBulkHelpers?tab=readme-ov-file#sqlbulkhelpersmaterializeddata https://github.com/cajuncoding/SqlBulkHelpers?tab=readme-ov-... (incidentally the most commercially useful out-SEO'd tiny-star-count library I've ever found, focused on bulk inserts into MSSQL using .NET). I think this is a good example of drawing the buy/build line in the right place with the automation of the partition switching.
- WuxiFingerHold 2y agoI'm not a fan of "just in case" development. Not when it comes to interfaces and also not regarding this `where 1=1` placeholder. Do things when you need them. Not if you think you might need them someday in the futures. Also, production code is not the place to keep dev helpers around. Do what you want in dev time, but for prod code readability and clear intent is much more important.
- password4321 2y agoDo you fully qualify all table + column name references? I've found it often increases readability by at least an order of magnitude but quickly becomes very verbose and incredibly painfully tedious to write.
- knighthack 2y agoWith autocomplete (like with Jetbrain's Datagrip) I found that verbose table names aren't that much of a problem; they in fact really help readability.
- password4321 2y agoMaybe fully qualifying all entity references is table stakes these days, I'll have to give Datagrip a spin. I already complained about autocomplete in response to another comment asking to put FROM first; maybe existing tooling is enough to make my life easier.
- galkk 2y agoIdk, I feel it's missing really useful convenience stuff that exists here and there... Examples, from the top of my head: 1. JOIN USING, for databases that support it. In some databases you can replace FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2 ... with FROM t1 JOIN t2 USING (c1, c2) much shorther and cleaner 2. Ability to exclude columns in select * DuckDB: SELECT * EXCLUDE (c1) Spanner SELECT * EXCEPT (c1)
- rldjbpin 2y agoi noticed a lot of these advise used by senior devs in my team or in legacy code. but as someone just starting out, a lot of these (like "1=1") was very odd and made the queries less accessible. nice to finally found the term for the "anti-query"; learning about it really changed how i write queries. equally good to see that most of these apply regardless of the RDBMS of choice.
- MK2k 2y agoMaybe off-topic, but: is "just closing without comment or discussion" an acceptable way of a maintainer to deal with pull requests? Asking as someone who only occasionally contributed (or tried to) with a repository. Examples: https://github.com/ben-n93/SQL-tips-and-tricks/pulls?q=is%3Apr+is%3Aclosed https://github.com/ben-n93/SQL-tips-and-tricks/pulls?q=is%3A...