Re:Best practices for writing SQL queries
by eirki on Saturday April 10, 2021 @12:47PM
(#26762830)
> Avoid
> Prefer
Couldn't disagree more. One letter abbreviations hurt readability IMO.
[ Parent
| on HN ]
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.
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)=1ON should be on another line because it gives context to bo.author_id = au.id.
You never write NOT ON, so no, I'd disagree that its required for all the inequalities you express.