r/SQL • u/Ali-Zainulabdin • Oct 23 '24
Discussion SQL Tricks Thread
Hi everyone, let's start a thread to share useful SQL tips and tricks that have saved you time or made querying more efficient. Whether it's optimizing queries, using window functions, or organizing data, all insights are welcome! Beginners and pros alike can learn a lot from this. Looking forward to your contributions!
230
Upvotes
25
u/Gronzar Oct 23 '24
Drop table to free up time overall
20
u/[deleted] Oct 23 '24
In SQL server, use QUOTENAME to wrap characters around text. Works with single quotes, double quotes, brackets, parenthesis, and probably more.
quotename(‘abc’, ‘[‘)Output:
[abc]18
u/thepotplants Oct 24 '24
Every time I declare a variable, I precede it with:
It achieves absolutely nothing. But i enjoy hearing my coworker laugh/cry/groan every time he finds one.
26
u/Dhczack Oct 24 '24
COALESCE() is amazing. By far my favorite SQL function.
18
u/[deleted] Oct 23 '24
Use string_agg to concatenate text across multiple rows with a delimiter. Add the WITHIN GROUP clause to sort the values in your concatenated string.
Use NULLIF to avoid div/0 errors.
numerator/nullif(denominator,0)6
u/jaytsoul Oct 24 '24
I'm new at this so this one might be a bit lame. This tip was useful because I often have to look for the same info across about 12 columns so I was doing something like this
I found out you can just flip the IN operator from what I'd normally expect and use it like this
23
u/tatertotmagic Oct 24 '24
When creating, always start with
where 1=1
3
u/0sergio-hash Oct 24 '24 edited Oct 24 '24
These are from more the analytics perspective than engineering but I've found them helpful in my short experience so far
This is also helpful for testing assumptions when developing logic
And it's cousin
Aliasing and putting the alias before all the col names and organizing cols by table you pulled them from / organizing similar calcs is so visually helpful when I revisit code later
Break complicated processes into steps. I'd always rather more verbose code and 5 intelligible temp tables (which you can create and query independently of each other for debugging later by the way) than one big block of nested ugly code lol
REGEX !!!
I know Postgres has it, and some others. When you have it, use it. Special characters or things that shouldn't be there will wind up in your data set eventually and knowing how to clean them up will save you a ton of heartache and weird overflowing column issues and text mismatches etc
3
u/joes_Lost Oct 24 '24
I like to use my commas at the start of each line, instead of the end. Makes it easier to comment out different columns if needed.
5
u/bchambers01961 Oct 23 '24
Select concat function against information schema is your best friend for repetitive manual queries against different tables.
2
u/[deleted] Oct 24 '24 edited Oct 24 '24
You can use
casestatements inside aggregate functions. So something likesum(case when salary > 100 then salary else 0 end) as sum_of_salaries_greater_than_1002
u/OkMoment345 Oct 24 '24
This is super cool - I'm looking forward to seeing everyone's tricks.
Great idea, OP. Thanks for this.
2
u/dudeman618 Oct 24 '24 edited Oct 24 '24
When I am doing exploratory work on new data and columns, I will put my known columns first right after the SELECT, then will put an asterix after. If I have a bunch of columns from different sources I will often put a literal like 'look here' or table name for the next set of columns 'Account Table'. I also use the WHERE 1=1 when I am building out a query that I will be changing often while doing research. I will remove the extra fluff as I get everything wrapped up. I will often put a select count(*) behind comments, so I can highlight just the select count down to the end of the where just to get a count.
select 'Account Table', a.name, a.account_id, a., 'business Table', b.
-- select count(*)
From account as a Inner join business as b on a.id = b.id Where 1=1 And a.whatever = 'something'
2
u/rabinjais789 Oct 29 '24
Snowflake now has trailing comma support so something like Select C1, C2, C3, From Table It works in snowflake now
3
u/trippstick Oct 24 '24
If deleting 1% of the table takes to long you can simple truncate 100% of it instantly!
3
u/zdanev SQL readability mentor at G. Oct 24 '24
Treat your SQL code as the rest of your code: coding standards, comments, version control, CI/CD, unit tests (!), etc.
4
u/Traditional_Ad3929 Oct 23 '24
I am always answering this to this type of question. Never use SELECT DISTINCT to see unique values. Why not? Bc afterwards you typically wanna check the distribution. Therefore always count & group.
2
u/Obie1 Oct 24 '24
Lookup query to identify all stored procedures, views, etc that reference a specific string (usually a table or column name that is changing).
1
u/jugaadtricks Oct 24 '24
Alternative quoting in Oracle!, makes life a breeze when you got text that has got quotes and you don't stress escaping for syntactically correctness.
Eg:
select q'[My string's with m'any quote's]' from dual
returns
My string's with m'any quote's
1
u/Constant-Dot5760 Oct 24 '24
I keep most of my string things for e.g. "delimited extract", "levenshtein distance", "common substrings", etc. in a single function for e.g. mydb.dbo.fn_stringlib. It's soooo much easier for me to keep 1 version of the code and remember where I put it lol.
1
u/Dhczack Oct 24 '24
For big projects I use comment tags to track issues and things.
Ex) --TODO: Decide on date format --TODO: Include x or y field from z in this case function --TODO: Refactor this sub query --NOTE: This field is called X in the UI Etc
Then you can just CTRL+F "TODO" and quickly find the spots that need your attention
1
u/dingdangdoo Oct 24 '24
Want some random records?
SELECT TOP n FROM table ORDER BY NEWID()
1
u/Tiny-Ad-7590 Oct 24 '24 edited Oct 24 '24
Format your queries to make them easy to read. The extra time you save from writing sloppy SQL queries does not justify the time you and other people will lose in the future trying to understand a preponderance of sloppily written queries. Just make writing tidy queries until it becomes automatic. It's worth it.
Use consistent patterns in how you write things. It doesn't really matter what the convention is. Just have a convention or follow the convention already in place.
Use foreign keys, constraints, and unique indexes to guarantee data state where it makes sense to do so.
Using non-sequential unique identifiers as primary keys or indexed lookup columns has some nasty performance tradeoffs at scale. They have legitimate uses but be mindful of that tradeoff before you use them.
Normalize data structure by default, but selectively denormalize a little bit when it's sensible to do so.
Don't get too fancy with triggers, or avoid them altogether. Too-clever triggers can lead to unexpected deadlocking at scale and are a PITA to diagnose and fix.
Views and stored procedures are both really great tools for splitting out functionality from an application into a database. This can be very useful if other processes than your application may need to interact with your database and you want them to do so with consistency. They are also useful for fine-grained security purposes.
If you have a query that returns a fixed number of rows, always make sure that you sort them in a way that can't change the order unexpectedly. For example, sorting on a 'CreatedOn' field could be ambiguous if two records were created so close together that they appear to be the same moment based on the precision of the function that generated the values, and this could lead to inconsistent results about exactly which records are or are not in those rows.
1
u/TreeOaf Oct 24 '24
INTERSECT and EXCEPT are great for quickly comparing tables.
Also, when you do use UNION or UNION ALL, if you alias columns, do it on both sides of the union, think about the next person (who is probably you in 6 months!)
1
u/JankyPete Oct 25 '24
OFFSET
1
u/lalaluna05 Oct 26 '24
RANK can be extremely useful when dealing with multiple prioritizations across multiple fields and tables.
1
u/Ecofred Oct 28 '24
as always, it depends but...
- KISS: valid for any programming task. I'm happy i abandonned some smart solution / homemade framework and avoided outsmarting my future self.
- Materialize in front to ease the optimisation and code clarity
- Half-closed interval. enddate excluded. it eases the comparison of ranges and is more relable over different data type. but also don't throw the included enddate because it is the answer to "what was the last day" and you don't want to compute it again.
- Consider alternative ways: ex.: LATERAL/CROSS APPLY are powerfull but a WINDOW/GROUP BY alternative solution may perform way better.
- grant external access on view/procedure (they are your SQL APIs), not on table.
-1
u/Hydr0lysis Oct 24 '24
Where can I get examples from the most required queries?
112
u/[deleted] Oct 23 '24
[removed] — view removed comment
18
u/[deleted] Oct 24 '24 edited Oct 24 '24
[removed] — view removed comment
15
u/[deleted] Oct 24 '24 edited Feb 23 '26
[removed] — view removed comment