Why make a list of SQL tips?
This is going to be an ever-growing list of SQL tips, shortcuts that I find useful day-to-day. I don’t claim to be the best at SQL but I figure if I keep track of some useful techniques and ideas here then it won’t hurt! The idea originally came from when I revisited my own post about removing duplicate rows in a SQL table, and realised maybe this would be handy. I am pretty bad for keeping my notes on paper, in a private wiki, text files etc. but at least hopefully I’ll be able to find my own website pretty easily. These SQL tips below are in no particular order (at present, anyway). I should add that I’m mostly referring to PostgreSQL syntax, but I’ll try and call that out if it’s only PostgreSQL.
NB: this is a work in progress!
SQL wildcards and matching: like and regexp
- Official docs
- Use
~forregexpmatching (possibly PostgresQL only) - Use
~~orLIKEfor simpler wildcard matching that is more performant
Using ‘with’
- Use it when your subqueries are fast
- Don’t use it when you have subqueries that don’t change but take a while whilst you are refining the overall query — just use a temp table (if you think you’ll be done during the session) or an actual table if you will keep coming back to it (or if the intermediate table will be useful to someone else!)
Using aliases in SQL queries
- Always use them, and use
asin every case for readability - Be explicit everywhere about referencing them, future you will thank past you
Pivoting data in SQL
- Not great in Redshift/PostgreSQL if you have lots of dimensions
- Sometimes easier to export and process with Python/R/etc. in these cases
- Other SQL dialects can be much more user-friendly
More general SQL tips
- User-defined functions are worth learning about…
- Find a client that suits you — you will spend a lot of time in there
- I quite like VSCode with the SQLTools extension but also have enjoyed using DBeaver
- If you’re in a bind or working in a terminal then most SQL databases can be used with a CLI (e.g. for PostgreSQL you can use
psql) - Sometimes these clients can be used cleverly with a bit of scripting to create simple pipelines
- Some people get on fine with a less complicated interface
- Just have a way to keep track of all your previous queries… and save often, or even better use
gitor similar - As with anything, always check to see if someone has done what you’re doing already if you work in a team!
- Maintain appropriate levels of metadata about the schemas and tables you are creating (especially using built-in fields where applicable) for future reference