4 ms·
> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.titl
by eirki 5y ago
> 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.
- dragonwriter 5y agoI 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.
- GordonS 5y agoIt 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 5y agoLazy 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 5y agoIt'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.
- hn_throwaway_99 5y agoWhile I semi disagree, I think the author's primary point was that you should always scope your column names.
- prepend 5y agoI prefer having some meaningful alias because trying to remember what a,b,c,d, etc gets annoying.
- chrishas35 5y agoAuthor 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.
- deleted 5y ago[deleted]
- dspillett 5y agoWhile 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.
- deleted 5y ago[deleted]
- hobs 5y agoMost 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 5y agoactually, 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 5y agoON should be on another line because it gives context to bo.author_id = au.id.
- hobs 5y agoYou never write NOT ON, so no, I'd disagree that its required for all the inequalities you express.
- mulmen 5y agoHuh? 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 5y ago
- zoomablemind 5y agoThe 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.
- trollied 5y agoCame 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.
- heavyset_go 5y agoLike naming variables, choose better names for your table aliases, then.