SQL Tips and Tricks
github.com
github.com
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.
One interesting thing I found about Postgres that's probably true of others too, often you can manually shard INSERT (SELECT ...) operations to speed them up linearly with the number of CPU cores, even when you have like 10 joins. EXPLAIN first, find the innermost or outermost join, and kick off a separate parallel query operating on each range of rows (id >= start AND id < end). For weird reasons, I relied on this a lot for one job 6 years ago. Postgres has added parallelism in versions 10+, but it's still not this advanced afaik.
Wonder if it'd be useful at all for live applications or just data processing. For the former, would need to somehow execute all reads at the same MVCC version even though they're separate connections.
What does this mean? Running
SELECT
column1,
(
SELECT column2, column3, ...
FROM table_b
WHERE table_a.id = table_b.a_id
)
FROM table_a
Keep in mind GROUP BY clause usually dictates index use.
The reason for this wasn't immediately apparent to me. For those who were curious, this blog post walks through it step by step: https://www.brentozar.com/archive/2015/06/indexing-for-group...
> The reason for this wasn't immediately apparent to me.
The key thing to remember is that grouping is essentially a sorting operation, and it happens before your other sorts (that last part isn't necessarily as obvious).
SELECT
column1,
(
SELECT column2
FROM table_b
WHERE table_a.id = table_b.a_id
) as b_column2,
(
SELECT column3
FROM table_b
WHERE table_a.id = table_b.a_id
) as b_column3
FROM table_a
If table_b has an index on id,column2,column3 (or on id INLUDEing column2,column3) I would expect the equivalent JOIN to usually be faster. If you have a clustered index on Id (which is the case more often than not in MS SQL Server and MySQL/InnoDB) then that would count for this unless the table is much wider than those three columns (so the index with its selective data would get many rows per page more than the base data).
Worst (and fairly common) case with sub-selects like that is the query planner deciding to run each subquery one per row from table_a. This is not an issue if you are only returning a few rows, or just one, from table_a, but in more complex examples (perhaps if this fragment is a CTE or view that is joined in a non-sargable manner so filtering predicates can't push down) you might find a lot more rows are processed this way even if few are eventually returned due to other filters.
There are times when the method is definitely faster but be very careful with it (test with realistic data sizes and patterns) because often when it isn't, it really isn't.
Yeah I guess I should have specified that this technique usually works best when done in the outer query, not buried deep inside.
It can be particularly effective if you fetch partial results, ie due to pagination or similar.
That said, these things aren't set in stone. I shared my experience, but my first tip goes first :)
Frequently this is trivial, sometimes it's not.
If there will be multiple hits but it doesn't matter that much, there's the obvious TOP 1 or MIN(col) and such.
It's a tradeoff between accidentally breaking the query and returning unexpected data.
Note that if you used join you could have bigger issues as the join would succeed but now you got multiple rows where you didn't expect.
Usually my biggest problem is getting all the query parameters lined up to reproduce the issue! (Being able to flip on extended logging or a profiler can make this easy.)
Cutting out the result columns when disabling JOINs to narrow it down is straightforward but tracking columns down in WHERE clauses quickly tends not to be.
If you can reproduce the issue then what I tend to do is to include the unique id column from each joined table (we try to avoid natural keys).
If it doesn't have a unique id column I replace the join with a subquery that includes row_number(), so I can se which one that doesn't repeat.
But without being able to replicate, I don't know of any better way than just studying the ON conditions carefully.
Cross apply (select top 1 ... ) x
https://github.com/ankane/pghero
https://news.ycombinator.com/item?id=41299148#41300220 (mentioned among the author's other helpful tools and Ruby gems)
> helpful to identify slow queries in production, remove duplicate indexes, see missing indexes, keep an eye on table size, etc
--
I haven't recently put in the effort to find a copy of SQL Sentry from back when the full-featured edition was briefly free but even the "always free" version was helpful working with MSSQL query plans.
https://www.solarwinds.com/free-tools/plan-explorer
(NOTE: Not sure how free-but-pushy it is these days, but years ago it wasn't bad.)
Oftentimes the well-designed queries behave unexpectedly, because the column statistics are not updated or when the data is fragmented for big tables (e.g. random PK insertion).
I disagree.
There are queries where a table scan is the most efficient access strategy. These are typically analytical/aggregation queries that usually query the whole table. And sometimes getting only 50% of all rows is better done using a table scan as well.
I also don't see how a (read only) "table scan" could leave to an outage. It won't block concurrent access. The only drawback is that it results in a higher I/O load - but if the server can't handle that, it would assume it's massively undersized.
Outage might "just" mean slow enough that customer can't get their work done in time. For the customer it's the same.
The JSON functions most RDBMS offer are awesome for that. One subquery to get a JSON if you have multiple results for the field then you only have to decode it on the app side.
That very much depends on your data.
If you have something else in mind, do feel free to elaborate.
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.
What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables might fill quickly when a favorable review comes in, or some world even results in churn in your system.
Just make the damned index. You Are Going to Need It. And what’s the harm in making it?
After some digging I found the service generating the responses got killed due to being unresponsive.
Turns out our customer got a new client which caused them to suddenly generate 100x as much data as others in this module. And that caused a lot more data in a table that joined this non-indexed table.
So everything was working, it was just the performance went over a cliff in a matter of days due to the missing index.
Added the required index and it's been humming ever since.
I've had similar experiences, and so these days I'm very liberal with indexes.
We have read-heavy workloads, if you mostly insert then sure be conservative.
Increased storage and slower inserts?
The leading comma format brings benefits beyond readability. For example, in version control systems the single-argument-per-line-with-leading-comma format turns any change to those arguments as a one-line diff.
I think developers spend as much time looking at commit historyas they do to the actual source code.
I think you failed to understand what I wrote.
Leading comma ensures one line diffs, but trailing comma forces two-line diffs when you add a trailing argument. With trailing comma, you need to touch the last line to add a comma, and then add the new argument in the line below.
We are not discussing bundling all arguments in a single line. I don't know where you got that idea from.
It does not. It just moves the edge case to a different position: trailing comma has the "issue" when adding an argument to the end of the list while leading comma has it when adding an argument to the beginning.
Also, as pointed out by the other commenter, any decent modern diff tool will make it obvious that the change to the existing line is just the addition of a comma, which makes the difference basically moot.
It only makes the diff clearer if you don’t have single character highlighting in your diff tool. Which most have now. Have had for a decade.
Also it’s not going to be a single line anyway. You add a line to the query and one to the caller. At a minimum. So you’re really arguing for three versus four. Which is false economy.
Because it makes the diff clearer.
Are you even reading the posts you're replying to?
I've seen them enough to not be bothered by them any more.
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.
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;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.
Closer to more seriously: which "beautifier" button is best? Is there a free one that is close to industry standard?
Heard there is a faster one called sqruff, but I have not tried it
https://www.quary.dev/blog/sqruff-launch
Might also be able to cajole sqlglot into being a formatter but it's designed for forgiving dialect conversion, not formatting
? I don’t see any problems
, or anything wrong with it
.
NOBODY CHECKS LONG LINES IN CODE REVIEWS. That was the biggest problem with AngularJS. People mishandling merges and breaking everything because the eyes start to glaze over at column 90. I’ve been on more than half a dozen teams with CRs and it’s always the same. I’m exquisitely aware of this and try not to do it, and I still fuck it up half as often as the next person.
Split your shit up. Especially when trying to set an example for others.
SQL, unfortunately, is very verbose and has a strange mix of super-high and very low abstraction. There is also no SQL formatter out there that does a decent job, and no real consensus about how good SQL is supposed to look.
If I look at the 'indent' guideline, it contains e.g.:
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
NULL) AS C28_fta_share
Please point me to the objective measurement of readability that you're using.
Appeals to "readability", for instance both what the OP and yourself are doing, are always 100% subjective.
On point 3: What I do is use CTEs to create intermediate columns (with good names) and then a final one creating the final column. It's way more readable.
```sql
with intermediate as (
select
DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7 as days_7_difference,
DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29 as days_29_difference,
LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity) as overnight_fta_share_1_lag,
LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity)as overnight_fta_share_2_lag
select
iff(days_7_difference, overnight_fta_share_1_lag, null) as C7_fta_share,
iff(days_29_difference, overnight_fta_share_2_lag, null) as C28_fta_share
I apologize in advance and hope you are able to come to grips with how easy it is to read and understand at some point in the near future.
And I agree, I should have used CTEs for this query, I was just trying to save lines of code which had the unintended consequence of quite an ugly query. However I did want to use it as an example of indentation being useful to make it slightly easier to read. Although perhaps I'm the only one who thinks so.
I greatly appreciate the constructive criticism.
This is a pretty solid reason for use in temporary queries so no doubt this approach will be around for a long time.
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.
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.
Also, the version of the database can matter, too.
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.
select name,cast((select OBJECT_DEFINITION(object_id) for xml path('')) as xml) from sys.procedures
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-... (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.
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.
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%3A...
It also depends on how you write SQL, but not by much.
I wouldn't put a dummy variable into a finalized query.
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.
As for formatting, indent the first field, too
SELECT
employee_id
, employee_name
, job
, salary
FROM employees
;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 ...
FROM t1 JOIN t2 USING (c1, c2)
2. Ability to exclude columns in select *
DuckDB:
SELECT * EXCLUDE (c1)
SELECT * EXCEPT (c1)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.
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.
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.
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
;
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 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
;
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.
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.
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.
Which ones?
Postgres, Oracle, SQL Server, MySQL, MariaDB and SQLite do not allow that.
I can't tell yet whether my experiment starting an all-OR WHERE clause with 0=1 so each OR could start the next line would go over like a lead balloon here too or not.
One thing I've actually found useful especially in SQL is always including a single-line comment in front of the closing of every multi-line comment, so that using a single-line comment to toggle off the opening of the multi-line comment toggles the entire block back into action and the end is already set (the closing comment is itself already commented out). This obviously doesn't work if multi-line comments start nesting, but that can confuse parsers enough already.
Trying to get an entire team on the same page with SQL formatting is one of the mothers of all bikesheds. In any case it's useful to be aware of the idioms whether or not they are personally palatable.
To be fair, BigQuery SQL is improving at quite a pace. If you follow their RSS, they are often announcing small but solid affordances like trailing commas, the new RANGE datatype, BigLake, some limited grouping and equality for arrays and structs, etc.
It is also probable that they expose Google's new query pipe syntax. Currently there are some hints from the error messages in the console that it's behind a feature flag or something.
Leading commas are king. Simple as.
I've also seen it in code with iterative adds, for example:
for crit in criteria:
sql += " AND " + crit
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.
WHERE 1=1
AND ...[usual-where-clause]...
WHERE ...[usual-where-clause]...In [1]: "... WHERE " + " AND ".join(str(i) for i in range(4))
Out[1]: '... WHERE 0 AND 1 AND 2 AND 3'
Very strange.
So you end up with things like this.
"SELECT * FROM Music WHERE 1=1" + "AND category='rock'"
The risk is now that you by mistake allow for SQL-injections but also every genre will generate a different query plan. Depending on what SQL engine you use this may hurt performance.
And one would think that this is a thing of the past. But it is not.
If I'm inspecting a dataset I use WHERE 1=1 so I can add and remove conditions more easily.
I realise the confusion is in my wording of dynamic - I might amend the README.md to clarify. Thanks for the feedback!
1 = 1 is at least handy for simply joining a variadic amount of other clauses with ' AND ' rather than counting if there's any to add at all.
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.
The reason I pointed out this specific issue is just that I thought it was the worsed of many poor tips. ChatGPT can give better tips.
It is kinda funny that op backpedal on this cause to me the whole site is amateurish. I just pointed out what I thought was the worst part. It is likely that it is generated by AI. Either way the post is terrible.
I certainly didn't use AI, all these tips/tricks are from work experience and reading Snowflake documentation and the like, but I guess I can't convince you either way. Regardless I appreciate the feedback!