News for hackers. Stuff that matters.
Re:Best practices for writing SQL queries by eirki on Saturday April 10, 2021 @12:47PM (#26762830)
> 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.
Re:Best practices for writing SQL queries by dspillett on Saturday April 10, 2021 @01:01PM (#26762990)
While single letter aliases can be bad outside of small examples like that, even worse being:

    SELECT t1.thing
         , t2.stuff
         , t3.stuffagain
         , t4.more 
      FROM SomeTable            t1 
      JOIN TableThatLinksToSelf t2 ON <join predicate>
      JOIN TableThatLinksToSelf t3 ON <join predicate>
      JOIN AnotherTable         t4 ON <join predicate>
that is not the point that is being made here. The point is that explicitly naming tables is beneficial to understanding and reducing issues later. Short alias is preferable to not specifying column sources at all.
Re:Best practices for writing SQL queries by hn_throwaway_99 on Saturday April 10, 2021 @01:00PM (#26762968)
While I semi disagree, I think the author's primary point was that you should always scope your column names.
Re:Best practices for writing SQL queries by GordonS on Saturday April 10, 2021 @12:59PM (#26762966)
It seems to be a matter of personal preference, but I've never liked single-character aliases myself, and never understood why so many seem to.
Re:Best practices for writing SQL queries by dspillett on Saturday April 10, 2021 @01:04PM (#26763023)
Lazy typing: t is shorter than tableWithTheDataIWantIn

I prefer descriptive table and other object names, and abbreviate them in aliases within queries (though usually not to single letters).

Re:Best practices for writing SQL queries by markmark on Saturday April 10, 2021 @05:45PM (#26765433)
It's not just about lazy typing it's about removing unnecessary clutter from large queries that makes things harder to read. In the author/books example, repeating the words author and books a dozen times doesn't convey any information that a and b don't, but clutters up the query making it harder to see the useful parts.
Re:Best practices for writing SQL queries by dragonwriter on Saturday April 10, 2021 @12:59PM (#26762956)
I agree with the source that the latter (explicit table specification in the SELECT list, whether using aliases or not) is to be preferred to the former; at the same time (while I am sometimes guilty of using them) I agree that single-character aliases are generally a poor choice for the same reasons that’s generally true of single character identifier names; column aliases are variable (well, constant) names and the usual rules of meaningful identifier names apply.
Re:Best practices for writing SQL queries by zoomablemind on Saturday April 10, 2021 @01:20PM (#26763163)
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 by prepend on Saturday April 10, 2021 @01:00PM (#26762976)
I prefer having some meaningful alias because trying to remember what a,b,c,d, etc gets annoying.
Re:Best practices for writing SQL queries by chrishas35 on Saturday April 10, 2021 @01:15PM (#26763118)
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 by heavyset_go on Saturday April 10, 2021 @05:18PM (#26765213)
Like naming variables, choose better names for your table aliases, then.
Re:Best practices for writing SQL queries by trollied on Saturday April 10, 2021 @01:23PM (#26763192)
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 by hobs on Saturday April 10, 2021 @01:16PM (#26763126)
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 by dragonwriter on Saturday April 10, 2021 @01:33PM (#26763280)
actually, I'd say the best is “column aliases are like full identifiers that name what the entity is in the context of the query”.

In simple cases that may be just the singular of the table name, e.g.:

  SELECT
    book.title,
    author.last_name,
    author.first_name
  FROM books AS book
  LEFT JOIN authors AS author ON 
    book.author_id = author.id
But in other cases, it will be different, e.g.:

  SELECT    
    manager.last_name || ', ' || manager.first_name AS manager_name,
    coalesce(employee.title, 'All Titles') AS staff_title,
    count(employee.id) AS count
  FROM employees AS manager
  LEFT JOIN employees AS employee ON
    manager.id = employee.manager_id
  GROUP BY manager.id, ROLLUP(employee.title)
  HAVING employee.title IS NOT NULL
      OR GROUPING(employee.title)=1
Re:Best practices for writing SQL queries by mulmen on Saturday April 10, 2021 @04:21PM (#26764780)
ON should be on another line because it gives context to bo.author_id = au.id.
Re:Best practices for writing SQL queries by hobs on Saturday April 10, 2021 @05:09PM (#26765132)
You never write NOT ON, so no, I'd disagree that its required for all the inequalities you express.
Re:Best practices for writing SQL queries by mulmen on Saturday April 10, 2021 @05:31PM (#26765319)
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.