Best practices for writing SQL queries • Hacker News Board

Re: Best practices for writing SQL queries

#151

Post by higeorge13 » Sun, Apr 11, 2021, 6:24 AM UTC

jjice wrote: post #31

Does anyone have any good resources for practicing SQL queries? I recently had an interview where I did well on a project and the programming portions, but fumbled on the more SQL queries that were above basic joins. I didn't realize how much I need to learn and practice. I don't know if my lack of knowledge was enough to cost me the position or not yet, but I'd like to prepare for the future either way. I've seen a…

The one you found is good for basic queries, but misses a lot of basic sql usage scenarios, such as window functions. It is PG-oriented, but i suggest https://www.postgresqltutorial.com. It also contains a sample database to practise (https://www.postgresqltutorial.com/postgresql-sample-databas...).

Re: Best practices for writing SQL queries

#152

Post by [deleted] » Sun, Apr 11, 2021, 4:12 PM UTC

mulmen wrote: post #119

hobs wrote: post #108

Earlier quoted context omitted.

You never write NOT ON, so no, I'd disagree that its required for all the inequalities you express.

Huh? ON is required by the syntax. Why start every line with a keyword that describes the line except the ON portion of a JOIN? It's inconsistent and has no clear benefit.

[deleted]

Re: Best practices for writing SQL queries

#153

Post by mike22 » Sun, Apr 11, 2021, 5:07 PM UTC

johnvaluk wrote: post #5

Overall an enjoyable read, but as someone who includes SQL queries in code, I disagree with two points: I despise table aliases and usually remove them from queries. To me, they add a level of abstraction that obscures the purpose of the query. They're usually meaningless strings generated automatically by the tools used by data analysts who rarely inspect the underlying SQL for readability. I fully agree that you sh…

Aliases are pretty much mandatory if you need to join the same table twice; otherwise you have no way to disambiguate column references. I understand the case of auto-gen’ed queries. However, in page long queries that I’ve written by hand, short aliases (typically the acronym of the table name, sometimes with a one-letter suffix if the table is joined multiple times) make the whole query flow like a nice story.

Re: Best practices for writing SQL queries

#154

Post by arh68 » Sun, Apr 11, 2021, 9:09 PM UTC

mulmen wrote: post #142

vericiab wrote: post #133

Earlier quoted context omitted.

Personally I like leading commas for the ergonomics rather than the aesthetics. When I'm developing or debugging a query the first column is typically the one I'm least likely to change. I tend to build up the query from there, so the last columns are the ones I'm most likely to change or to comment out. Plus I find it easiest to interpret the result set when columns that I'm using as a temporary reference are at the…

I hear this argument all the time but it makes no sense. Leading commas only help you comment out the last line. Any comma arrangement allows you to comment any intermediate line. It’s sacrificing readability and aesthetics for a tiny benefit on one row.

likelihood of commenting out last N lines >> .. first N lines

What happens is I want to comment out a join entirely, and all its columns. I rarely find myself commenting out the first column. I'd prefer trailing commas on every element, where allowed, to be sure! but leading isn't so bad once I got used to it.

Plus to scan for a missing leading comma is a linear (literally!) search whereas [missing] trailing commas don't line up.

Re: Best practices for writing SQL queries

#155

Post by dspillett » Mon, Apr 12, 2021, 2:48 PM UTC

jolmg wrote: post #38

dspillett wrote: post #24

Earlier quoted context omitted.

I've taken to using a similar format too, though some seem to dislike it significantly. Other things I like for clarity and editing ease are prefix commas and lining up like parts, using something like your example: SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col or SELECT a.foo , b.bar , g.zed FROM alph…

> though some seem to dislike it significantly I can see why. The indentation of the whole statement is not determined by the first line, but by the 6th on the first and the 8th on the second on a `JOIN` clause. It's really arbitrary, and when you have that statement between other code, it's going to be weird how the start of the statement is much more indented than its preceding code. I really dislike it, too. I pre…

> and when you have that statement between other code

In many circumstances (chunks of SQL in SSIS steps and so forth) there is no code to be around. Within longer tracks of SQL I'll not let "LEFT OUTER JOIN" push the whole statement to the right, but will drop the table name to the next line and maintain the alignment from there on.

> removed the padding you used to align the `=` signs. I dislike big changes where the only thing that changed for a line is the whitespace padding. It obscures the real change

For modifying existing code, I will sometimes let the alignment slide to avoid unnecessary extra lines of change. Though a good visual diff tool will have the option to ignore white-space-only changes and I'm not concerned about patch sizes being a few lines bigger.

To fix the alignment there may be a subsequent check-in that tidies up non-functional elements of the code. I'm happy to have an extra item in history in order to maintain readability over a stream of unaligned text.

Something I've wanted for a long time in code editors is for tabs after the first non-whitespace character on a line to line up, that way such alignment could be managed without extra effort or extra lines in a diff. There would need to be some simple heuristics to control breaking the alignment (and these may need to vary between languages) and perhaps some way to control/override them (a directive in comments?) in edge cases.

Re: Best practices for writing SQL queries

#156

Post by jolmg » Mon, Apr 12, 2021, 4:59 PM UTC

Wevah wrote: post #132

jolmg wrote: post #80

Earlier quoted context omitted.

> While the comma placement may seem weird It's not completely unconventional. Haskell is typically styled with that kind of comma usage, too. For example, [ 1 , 2 ] { foo = 1 , bar = 2 } Coincidentally, SQL and Haskell are the only languages I know that use `--` for comments.

FWIW, Lua and AppleScript (and HyperTalk!) use(d) `--` as well. (Edit: I think I misread “know” as “know of”; whoops.)

> I think I misread “know” as “know of”; whoops.

Don't worry about it; you didn't misread. I wasn't aware of AppleScript and HyperTalk, and though I read a bit on Lua some years ago, I either forgot or never realized that it used `--` for comments.

Re: Best practices for writing SQL queries

#157

Post by tremon » Mon, Apr 12, 2021, 9:25 PM UTC

twoodfin wrote: post #137

tremon wrote: post #44

Avoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, p…

Prefer EXISTS to IN is also a bit odd, as the latter is trivially transformed to the former. The DBMS I work with does it universally.

There is a semantic difference, in the context of handling NULLs: EXISTS only checks the size of the result set, so even a subquery that returns (NULL, NULL, NULL) will satisfy the EXISTS condition. IN, on the other hand, performs data equality checks on the results, so NULL rows will never yield a positive match.

Usually, this doesn't make a difference as an EXISTS check is mostly used on the primary key (or business key) of a table, which is (hopefully) non-nullable. But it can sometimes give surprising results when using EXISTS on a nullable column, or for worse results, NOT IN.

Re: Best practices for writing SQL queries

#158

Post by carbocation » Wed, Apr 14, 2021, 1:53 AM UTC

shazzdeeds wrote: post #75

carbocation wrote: post #54

Earlier quoted context omitted.

I'm not sure that I follow. The two queries are to demonstrate difference in form; they are not intended to be equivalent. If you're already writing: WHERE foo=bar AND biz=baz It's not clear to me how: WHERE TRUE AND foo=bar AND biz=baz is worse.

He’s saying if someone gets in the habit of using that style they have to be very careful. If they forget to change True to False when using an OR that it could have major consequences. Performance being the least of concerns.

I agree that such an error would be of the catastrophic type. It's interesting that several people seem to perceive this formatting approach as something that would increase the risk of that error. Is the red flag for people the WHERE TRUE on one line? Like, would this be less alarming to people?

    WHERE
      TRUE
      AND x=y