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?)
[ Parent
| on HN ]
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.