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

21–30 of 158 posts

Re: Best practices for writing SQL queries

#21

Post by Hjfrf »

hn_throwaway_99 wrote: post #8

This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta…

Does that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?

Re: Best practices for writing SQL queries

#22

Post by chrishas35 »

prepend wrote: post #13
eirki wrote: post #4

> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.

I prefer having some meaningful alias because trying to remember what a,b,c,d, etc gets annoying.

Author probably could have chosen a better example such that it doesn't look like the author chose letters sequently. In this case the letters are meaningful as they are the same as the first of the table name, a common convention, unfortunately that happens to be the first two letters of the alphabet...which yes, would be very annoying.

Re: Best practices for writing SQL queries

#23

Post by hobs »

eirki wrote: post #4

> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.

Most of your queries shouldn't be so verbose as to confuse which aliases you are talking about, but I agree - here's the best format :) (because why put ON on another line anyway?)

  SELECT
    bo.title,
    au.last_name,
    au.first_name
  FROM books AS bo
  LEFT JOIN authors AS au ON 
    bo.author_id = au.id

Re: Best practices for writing SQL queries

#24

Post by dspillett »

hn_throwaway_99 wrote: post #8

This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta…

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 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
I'm not consistent with the layout of my joining predicates - I go for whatever seems clearer given the current circumstances and that varies due to several factors (number of parts, length of column names and/or functions, ...). How sub-queries and instances of CASE are broken into lines and indented is something I also vary on.

Re: Best practices for writing SQL queries

#25

Post by 7952 »

nerdbaggy wrote: post #2

Does anybody else like putting from first? I find it makes the auto complete sooo much better and easier to read.

I think it would be easier to order things in terms of when they are executed. And perhaps it would be easier to teach SQL if the different parts where more obviously separate. As the different parts are actually distinct and don't really cross over. But to a newbie would seem procedural when it's not.

Re: Best practices for writing SQL queries

#26

Post by hn_throwaway_99 »

Hjfrf wrote: post #21
hn_throwaway_99 wrote: post #8

This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta…

Does that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?

Another commenter showed how this works:

  SELECT a.foo
       , b.bar
       , g.zed
    FROM ...
While the comma placement may seem weird, it makes this exactly identical to the "AND" or "OR" placement in WHERE clauses, and the primary benefit is that it's easy to comment out any column except the first.

Re: Best practices for writing SQL queries

#27

Post by zoomablemind »

eirki wrote: post #4

> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.

The usefulness of this advice depends on the schema or design of the database. If the data is normalized, then it's quite reasonable to design for unambiguous field names in the queries after all relevant joins.

Sure it does not help to understand the origins of a given field without aliases, unless someone is very familiar with the schema.

Re: Best practices for writing SQL queries

#28

Post by trollied »

eirki wrote: post #4

> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.

Came here to post exactly this.

If the tables names are long, by all means abbreviate them a little, but never just use 1 letter aliases.

I wonder if the author has ever worked with a system that has more than a handful of tables.

Re: Best practices for writing SQL queries

#30

Post by warent »

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

this seems like taking on a pretty huge risk for a minor convenience. the difference between those two queries can mean the difference between protecting someone's PII
Post reply on HN