[eirki]

> 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.

[dspillett]

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.

[hn_throwaway_99]

While I semi disagree, I think the author's primary point was that you should always scope your column names.

[GordonS]

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.

[dspillett]

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).

[markmark]

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.

[dragonwriter]

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.

[zoomablemind]

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.

[prepend]

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

[chrishas35]

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.

[heavyset_go]

Like naming variables, choose better names for your table aliases, then.

[trollied]

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.

[hobs]

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

[dragonwriter]

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

[mulmen]

ON should be on another line because it gives context to bo.author_id = au.id.

[hobs]

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

[mulmen]

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.