SQL Tips and Tricks

Leading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.

Re: SQL Tips and Tricks

#7

Post by sgarland » Wed, Sep 25, 2024, 5:24 AM UTC

Not shown: stop using SELECT *. You almost certainly do not need the entire width of the table, and by doing so, you add more data to filter and transmit, and also prevent semijoins, which are awesome.

Re: SQL Tips and Tricks

#8

Post by egeozcan » Wed, Sep 25, 2024, 5:24 AM UTC

I remember doing the "WHERE 1=1" trick in my last job and it causing a... let's say "unproductive", discussion in the pull-request.

Re: SQL Tips and Tricks

#9

Post by youdeet » Wed, Sep 25, 2024, 5:36 AM UTC

One more point in the "Anti Join". Use EXISTS instead of IN and LEFT JOIN if you only want to check existence of a row in another large table / subquery based on the conditions. EXISTS returns true as soon as it has found a hit. In case of LEFT JOIN and IN engine collects all results before evaluating.

Re: SQL Tips and Tricks

#10

Post by alex5207 » Wed, Sep 25, 2024, 5:37 AM UTC

Never knew about QUALIFY. That's great