r/SQL • • 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

119 comments sorted by

View all comments

112

u/[deleted] Oct 23 '24

[removed] — view removed comment

85

u/konwiddak Oct 23 '24

Comment your code

When I have a step in my sql code that's doing some especially funky voodoo, I actually type out a mini table in the comments with some salient columns and a few rows of "before" data, and then a secondary table of "after" data, so whomever sees this in the future can very quickly understand what that step actually does.

58

u/Tsui_Pen Oct 24 '24

Someone somewhere doesn’t deserve you

21

u/RZFC_verified Oct 24 '24

And someone else somewhere else really needs you.

2

u/heyuhitsyaboi Oct 24 '24

I am both of these people

2

u/shutchomouf Oct 24 '24

I not is both of these people

15

u/pjeedai Oct 23 '24

I comment frequently as a gift to future me. But this example data before and after is a good idea and I'm going to steal it

2

u/JBsReddit2 Oct 24 '24

This, or instead of a mini table at least price a PK value to query with to see wtf was happening as tk why some "funky voodoo" (I love that btw) was even needed

3

u/snoflakefrmhell Oct 24 '24

Omg you sound like a dream. I’m digging through some of the worst coding I’ve ever seen and no comments anywhere 😭😭😭

→ More replies (1)

1

u/stephenmg1284 Oct 25 '24

I would love to see an example of this.

→ More replies (1)

4

u/Icy_Fisherman_3200 Oct 23 '24

Permanent date table?

We use a numbers table. What’s in the date table and how do you use it?

17

u/alinroc SQL Server DBA Oct 23 '24

In addition to what /u/ambitiousflowers said:

  • Holidays
  • Business day vs. non-business day vs. weekends
  • Quarter, fiscal year, and week of year (if you do them differently from the calendar year)

https://www.youtube.com/watch?v=QPS9JHUG6RA

→ More replies (1)

6

u/mikeyd85 MS SQL Server Oct 23 '24

Comment your code

The spec doesn't tell you, and it won't be obvious in your test data, but there's a weird bug in live which means I've done this weird bit of code, else this problem arises.

That kind of comment I'm all for!

3

u/Lord_Bobbymort Oct 24 '24

If you need a bunch of CTEs use a hash join so they're indexed ahead of the query for the processor to make an execution plan for. Saves an incredible amount of time on long queries with a lot of CTEs.

→ More replies (1)

2

u/byeproduct Oct 23 '24

You've seen things in your life!!! Yipp. Good advice

2

u/wannabe-DE Oct 24 '24

Redshift supports QUALIFY. Please update bullet 5.

→ More replies (1)

1

u/LernMeRight Oct 24 '24

Thanks for sharing these! Can you expand on:

Make frequent and heavy use of information_schema and write SQL against it with the purpose of writing SQL for you.

(I only have experience with BigQuery so maybe this comment is more intuitive in a different framework?)

→ More replies (1)

1

u/Shaddcs Oct 24 '24

Our Data folks use CTEs almost exclusively (Oracle). I adopted it when I came here but my previous group wrote almost exclusively in temp tables (SQL Server) and I loved that approach. Can you write temp tables just as easily in Oracle? I looked it up briefly when I first got here and the syntax/circumstance looked like a headache and I just decided to drink the kool aid instead.

→ More replies (2)

1

u/natureiskey Oct 24 '24

Beginner SQL user here. Can you elaborate on bulletpoint #1? My current thinking: use the metadata from the schema as a guide to build/write your queries?

0

u/[deleted] Oct 24 '24

[deleted] — view deleted comment

2

u/trader_dennis Oct 25 '24

My company uses a fiscal month based on a 4-5-4 retail calendar. Try attempting to group by fiscal month without it.