Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Best practices for writing SQL queries

metabase.com

Post reply on HN
Print view View original

61–70 of 158 posts

Re: Best practices for writing SQL queries

#61

Post by learn_more »

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…

I'm the opposite. I like the short aliases, esp. when there are many tables. Short aliases can all be the same length, and therefore align better for better readability, and they don't pollute the visibility as overly-verbose table names do.

In code, I like the length of the variable name to be proportional to the size of the scope. Small scope -- short variable names.

Re: Best practices for writing SQL queries

#62

Post by Hjfrf »

yardstick wrote: post #47

> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. Don’t most databases figure this out as part of the query planner anyway? Postgres has no problems using indexes for joins inside WHERE.

Yes all databases will use indexes for joins. There's quite a few mistakes like that.

My guess is the author heard something about not using implicit inner joins (deprecated decades ago) and misunderstood.

E.g. This old syntax- SELECT * FROM a, b WHERE a.id = b. a_id

Re: Best practices for writing SQL queries

#63

Post by mactrey »

carbocation wrote: post #29

Personal habit is to start my WHERE clause with a TRUE or a FALSE so that adding or removing clauses becomes seamless: SELECT foo FROM bar WHERE TRUE AND baz > boom For OR conditions it's a bit different: SELECT foo FROM bar WHERE FALSE OR baz > boom

I'm so attached to starting all my where clauses with a TRUE (1=1 since SQL Server doesn't have boolean literals) that I do this when I need some OR clauses:

    SELECT foo
    FROM bar
    WHERE 1=1
      AND (11
           OR baz > boom
           OR fizz >= bang
           )
      AND foo is not null
So you can comment out lines starting with OR individually. Some people might hate it but it makes sense conceptually for me since almost every query I write takes a chain of ANDs in the where clause as a starting point.

Re: Best practices for writing SQL queries

#64

Post by magicalhippo »

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…

I got descriptive table names like WhsTransactionGoodsItems and WhsTransactionGoodsItemPackages.

I feel it would be rather noisy to have to specify such table names in front of the 15+ column references in a query, compared to using aliases.

Then again I've never had to diff the result sets, so I guess our usage is quite different.

Re: Best practices for writing SQL queries

#65

Post by bob1029 »

This seems like reasonable discussion, but you would get far more traction if you have an opportunity to write an entire schema from scratch in the proper way.

Not having to fight assumptions along lines of improperly denormalized columns (i.e. which table is source of truth for a specific fact) can auto-magically simplify a lot of really horrible joins and other SQL hack-arounds that otherwise wouldn't be necessary. The essential vs accidental complexity battle begins right here with domain modeling.

You should be seeking something around 3rd normal form when developing a SQL schema for any arbitrary problem domain. Worry about performance after it's actually slow. A business expert who understands basic SQL should be able to look at and understand what every fact & relation table in your schema are for. They might even be able to help confirm the correctness of business logic throughout or even author some of it themselves. SQL can be an extremely powerful contract between the technology wizards and the business people.

More along lines of the original topic - I would strongly advocate for views in cases where repetitive, complex queries are being made throughout the application. These serve as single points of reference for a particular projection of facts and can dramatically simplify downstream queries.

Re: Best practices for writing SQL queries

#66

Post by hn_throwaway_99 »

dragonwriter wrote: post #45
hn_throwaway_99 wrote: post #40

Earlier quoted context omitted.

I don't see why you think it would make diffs painful. If anything, in my experience it makes diffs easier because each chunk can be put on it's own, independent line so that if you change anything it is constrained to the relevant line.

It makes diffs harder because maintaining the indentation rule (sometimes, depending on what is on other lines) requires changing every line of the query if you go from “INNER JOIN” (equally, outer/right/cross join) to “LEFT JOIN” (equally, full join).

The indentation rules never change. Fortunately, "SELECT" at six letters is as long as the longest first word that starts a clause, which is why when doing, for example, a "LEFT JOIN" you line up the "LEFT" and not the "JOIN, e.g.

  SELECT a.foo
    FROM alpha a
    JOIN beta b ...
would become

  SELECT a.foo
    FROM alpha a
    LEFT JOIN beta b ...
Any diff tool correctly highlights the only change is the LEFT.

Re: Best practices for writing SQL queries

#67

Post by sodapopcan »

hn_throwaway_99 wrote: post #66
dragonwriter wrote: post #45

Earlier quoted context omitted.

It makes diffs harder because maintaining the indentation rule (sometimes, depending on what is on other lines) requires changing every line of the query if you go from “INNER JOIN” (equally, outer/right/cross join) to “LEFT JOIN” (equally, full join).

The indentation rules never change. Fortunately, "SELECT" at six letters is as long as the longest first word that starts a clause, which is why when doing, for example, a "LEFT JOIN" you line up the "LEFT" and not the "JOIN, e.g. SELECT a.foo FROM alpha a JOIN beta b ... would become SELECT a.foo FROM alpha a LEFT JOIN beta b ... Any diff tool correctly highlights the only change is the LEFT.

Ya, was thinking after I submitted that SELECT and DELETE are always going to be the longest anyway, so it always works for SQL!

Re: Best practices for writing SQL queries

#68

Post by sodapopcan »

fiddlerwoaroof wrote: post #52
dragonwriter wrote: post #45

Earlier quoted context omitted.

It makes diffs harder because maintaining the indentation rule (sometimes, depending on what is on other lines) requires changing every line of the query if you go from “INNER JOIN” (equally, outer/right/cross join) to “LEFT JOIN” (equally, full join).

Nearly every diff tool has -w for this, though: main main annoyance with GitHub is that I can’t enable this as the default diff mode.

Yep—`-w` is the default when I blame in my editor but ya, github is really the problem.

Re: Best practices for writing SQL queries

#69

Post by grzm »

bob1029 wrote: post #65

This seems like reasonable discussion, but you would get far more traction if you have an opportunity to write an entire schema from scratch in the proper way. Not having to fight assumptions along lines of improperly denormalized columns (i.e. which table is source of truth for a specific fact) can auto-magically simplify a lot of really horrible joins and other SQL hack-arounds that otherwise wouldn't be necessary.…

One of the strengths of Metabase is that it can plug into a variety of data sources, not just RDBMS. For example, AWS Athena over data in S3 buckets. Good design can still make things easier, of course, but not always an option. Relational purity is not going to be an option in such circumstances, so are in my opinion correctly not addressed in the piece.

Re: Best practices for writing SQL queries

#70

Post by jitans »

cornel_io wrote: post #59

NB: this post is mostly performance advice, and it only applies to traditional databases. Specifically, it is not good advice for big data columnar DBs, for instance a limit clause doesn't help you at all on BigQuery and grabbing fewer columns really does.

not even all the "traditional" databases, each Engine has his own peculiarities.
Post reply on HN