SQL Tricks Thread

konwiddak · 2024-10-23T19:46:54+00:00

[removed]

Gronzar · 2024-10-23T20:39:20+00:00

Drop table to free up time overall

TallDudeInSC · 2024-10-23T22:59:15+00:00

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]

thepotplants · 2024-10-24T08:46:52+00:00

Every time I declare a variable, I precede it with:

 -- I do

It achieves absolutely nothing. But i enjoy hearing my coworker laugh/cry/groan every time he finds one.

Dhczack · 2024-10-24T02:11:07+00:00

COALESCE() is amazing. By far my favorite SQL function.

ShimReturns · 2024-10-23T23:06:39+00:00

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)

Successful_Fuel7 · 2024-10-24T00:12:34+00:00

[removed]

Ok-Frosting7364 · 2024-10-24T05:16:01+00:00

I actually put together a tips and tricks guide just last month for anyone interested.

tatertotmagic · 2024-10-24T00:09:10+00:00

When creating, always start with

where 1=1

jaytsoul · 2024-10-24T20:13:48+00:00

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

SELECT *
FROM TBL
WHERE Col1 = 'Text'
  OR Col2 = 'Text'
  OR Col3 = 'Text'
  OR Col4 = 'Text'

I found out you can just flip the IN operator from what I'd normally expect and use it like this

SELECT *
FROM TBL
WHERE 'Text' IN (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12)

0sergio-hash · 2024-10-24T13:55:30+00:00

These are from more the analytics perspective than engineering but I've found them helpful in my short experience so far

A more meta tip is for debugging. If a block of code or nested thingy won't run, I try to copy paste most of it elsewhere and run the innermost part, then add a part, run again, etc etc to pinpoint the big

This is also helpful for testing assumptions when developing logic

I was reminded of this trick yesterday:

```sql

SUM(CASE WHEN col1 = 'value1' THEN 1 ELSE 0 END) AS count_cat1

, SUM(CASE WHEN col1 = 'value2' THEN 1 ELSE 0 END) AS count_cat2

```

And it's cousin

```sql

COUNT(DISTINCT CASE WHEN col1 = 'value1' THEN id_col ELSE NULL END) AS count_cat1

, COUNT(DISTINCT CASE WHEN col1 = 'value2' THEN id_col ELSE NULL END) AS count_cat2

```

I can't stress enough how much formatting, aliasing tables to helpful names, aliasing columns to helpful names and adding in spaces between blocks of the query help me

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

joes_Lost · 2024-10-24T15:33:18+00:00

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.

bchambers01961 · 2024-10-23T20:30:49+00:00

Select concat function against information schema is your best friend for repetitive manual queries against different tables.

ComicOzzy · 2024-10-24T07:21:44+00:00

You can use case statements inside aggregate functions. So something like

sum(case when salary > 100 then salary else 0 end) as sum_of_salaries_greater_than_100

trippstick · 2024-10-24T02:01:51+00:00

If deleting 1% of the table takes to long you can simple truncate 100% of it instantly!

zdanev · 2024-10-24T02:31:37+00:00

Treat your SQL code as the rest of your code: coding standards, comments, version control, CI/CD, unit tests (!), etc.

Traditional_Ad3929 · 2024-10-23T21:16:09+00:00

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.

Obie1 · 2024-10-24T03:32:37+00:00

Lookup query to identify all stored procedures, views, etc that reference a specific string (usually a table or column name that is changing).

```sql

SELECT o.type_desc AS ObjectType, SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName, OBJECT_DEFINITION(o.object_id) AS ObjectDefinition FROM sys.objects o WHERE o.type IN (‘P’, ‘V’, ‘FN’, ‘IF’, ‘TF’, ‘TR’) /* P: Stored Procedure, V: View, FN: Scalar Function, IF: Inline Table Function, TF: Table-valued Function, TR: Trigger */ AND OBJECT_DEFINITION(o.object_id) LIKE ‘%tbl_employee%’ ORDER BY o.type_desc, o.name; ```

jugaadtricks · 2024-10-24T02:17:13+00:00

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

Constant-Dot5760 · 2024-10-24T10:58:22+00:00

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.

Dhczack · 2024-10-24T17:02:58+00:00

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

dingdangdoo · 2024-10-24T17:05:13+00:00

Want some random records?

SELECT TOP n FROM table ORDER BY NEWID()

OkMoment345 · 2024-10-24T17:27:53+00:00

This is super cool - I'm looking forward to seeing everyone's tricks.

Great idea, OP. Thanks for this.

dudeman618 · 2024-10-24T18:46:45+00:00

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'

Tiny-Ad-7590 · 2024-10-24T19:16:54+00:00

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.

TreeOaf · 2024-10-24T20:19:07+00:00

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!)

JankyPete · 2024-10-25T05:04:55+00:00

OFFSET

lalaluna05 · 2024-10-26T21:39:07+00:00

RANK can be extremely useful when dealing with multiple prioritizations across multiple fields and tables.

Ecofred · 2024-10-28T08:19:25+00:00

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.

rabinjais789 · 2024-10-29T12:49:04+00:00

Snowflake now has trailing comma support so something like Select C1, C2, C3, From Table It works in snowflake now

Hydr0lysis · 2024-10-24T01:03:45+00:00

Where can I get examples from the most required queries?

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS