Weird, I really dislike a lot of the suggestions. In particular, an example:
SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num;
We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things right.
How about:
SELECT first_name FROM staff JOIN students ON student.staff_id = staff.id;
If you're reading some 50+ line SQL query with a bug, things like this are easy to read and be confident in. Honestly, I think AS should basically never be used for tables.
Likewise, the guide gives the example of naming join tables 'services' instead of 'car_mechanics'. But if I see a table 'car', a table 'mechanic', and a table 'car_mechanic', I instantly know how to join against this in any direction. The hard part of understanding a schema is how it fits together, not what it represents, and when you have hundreds of tables and need to remember that join table's name, it's really, really nice if it's trivial to derive.
Lastly, the guide's recommended indentation for FROM vs JOIN statements seems off to me. Consider:
FROM riders AS r INNER JOIN bikes AS b ON r.bike_vin_num = b.vin_num AND b.engines > 2
Well, the thing is, the riders table and bikes table have the exact same priority in the query, but this spacing really emphasizes the riders table and it's not clear why.
I only slightly disagree with a couple of your points.
1) They aliased it to show how it should look. This is a contrived example, and you normally wouldn't alias these.
2) Aliasing tables is essential when you have join tables that end up being over 30 characters long, or you need to self join for something. Short of using CTEs (which aren't available everywhere), I don't know of a way to self join without using aliases.
> Aliasing tables is essential when you have join tables that end up being over 30 characters long, or you need to self join for something. Short of using CTEs (which aren't available everywhere), I don't know of a way to self join without using aliases.
This is 80-90% of my queries because we do a lot of cross schema joining in my work. Also there's a lot of tables replicated from the mainframe so the table names are T_<4 Letters that mean something to someone>_<Some more seemingly random letters>_<Something vaguely like a word if you're lucky> so their names aren't useful at all. Add in that a lot of queries are reporting queries so we're pulling in 20-30+ columns from these long named tables from different schemas and not aliasing them would add a a small novel worth of text to the query.
> Honestly, I think AS should basically never be used for tables.
I believe AS is required for self-joins. A common expository query is to find all employees who earn more than their managers, which can be expressed via "select name from staff as employee join staff as manager on (employee.manager_id = manager.id) where employee.salary > manager.salary".
Also, SQL allows a sub-select to be listed in the FROM clause. But in such cases, the sub-select must be given a name (tables have a name, but sub-selects don't).
As an aside, the keyword "AS" is allowed by not required.
Sure, I forgot the self-join case. In my experience these are a minority of joins, but definitely. In postgres at least, a lot of self-joins can be solved with a DISTINCT ON instead.
Sub-selection with a FROM clause, yeah, those definitely need names. But those don't have perfectly good names that everybody already knows, whereas tables do. If you're maintaining somebody's code and they declare
foo_bar = 10
I think it'd be pretty strange to ever consider writing
fb = foo_bar
just because you find foo_bar too long to write or something.
why is "tb_" bad for tables? for some reason this has become standard practice for me. at least on SQL Server it can help distinguish tables from views in complex queries or stored procedures. No idea where i learned this habit.
Aliasing tables is very useful. Aside from giving shorter names and helping you to be explicit when selecting fields, they also end up being useful for working with IntelliSense (or the equivalent in the tool you're using). Plus, you can make them memorable enough (e.g. in the first example you shared, could use 'S' for staff and 'ST' for students) for the query you're working on and shortening the query code makes it more readable. For all the reasons above, aliases are one of the first things I'll add when writing a new SQL query.
As for the indentation after FROM, I prefer to put an indent here too. Whilst you're right that INNER JOIN would have the same priority, LEFT JOIN wouldn't, and I wouldn't want to use different indenting styles for different types of joins.
That said, I don't agree with everything in this style guide, especially...
* Do... "Try to use only standard SQL functions instead of vendor specific functions for reasons of portability."
* Avoid... "Descriptive prefixes or Hungarian notation such as sp_ or tbl."
With the standard functions, it strikes me that's optimising the wrong thing. How many times are you likely to switch database engines? Such actions are very rare, especially for teams with good grounding in RDBMS'. What's more important to me is readability of the code, and there's plenty of useful non-standard SQL for improving readability. If we want both we should be pushing for updated standards to be applied across the board.
As for the second point, it's useful to know whether you're looking at a table/view/function/stored procedure/trigger from the name alone. Whilst the tbl prefix seems superfluous, other database objects should have a descriptive prefix in the name IMO.
I'm aware of that. However, it only has to be memorable for the period of time you're working with a query. If you open up an old view or stored procedure to modify it, you would read the table aliases first. If someone wasn't able to retain the meaning of 'S' and 'ST' for the length of time needed to modify a query then perhaps working with code isn't for them.
Lastly, if someone did have poor memory they could lengthen the aliases to STA and STU.
Better :) But I prefer to just give my tables short names to begin with. How about staff and pupils? That's so short that there is little temptation to abbreviate.
> How many times are you likely to switch database engines?
Not often, but you may wish to support multiple engines at once, and it makes your code a lot easier to manage if you don't have to have a separate set of queries for each engine.
I really agree that the recommendation on aliasing is just plain wrong. I prefer using singular names relating to the role-in-the-query, e.g.:
SELECT manager.last_name AS manager_name, employee.last_name AS employee_name FROM staff as employee JOIN staff as manager ON employee.manager_id = manager.staff_id
This is probably most important (as in the above example) with self-joins, but I think its a good practice more generally, and makes queries more self-documenting as to intent.
SQL isn't BASIC, and we aren't limited to x (1,2,4, etc) character variable names. Why not just use the existing meaningful name? They aren't that hard to type, and you might even be able to use autocompletion, depending on environment.
I've spent far too much time re-writing queries to remove obfuscating aliases to accept a blanket statement that they make it easier to get your head around the code.
In my line of work I often end up working with queries where you'll have a handful of JOINs and need to SELECT a dozen or more columns. In those cases writing out staff.foo or students.bar gets old really fast.
I agree that s1 and s2 are bad though, as I said in another comment.
>In my line of work I often end up working with queries where you'll have a handful of JOINs and need to SELECT a dozen or more columns. In those cases writing out staff.foo or students.bar gets old really fast.
It's better to have it "get old" though, than to introduce subtle bugs because of similar, short, aliases.
Longer Names have there own issues and remember not every one can recall long strings. dyslexics and other neurotypical people often have difficulty with this
How about:
If you're reading some 50+ line SQL query with a bug, things like this are easy to read and be confident in. Honestly, I think AS should basically never be used for tables.Likewise, the guide gives the example of naming join tables 'services' instead of 'car_mechanics'. But if I see a table 'car', a table 'mechanic', and a table 'car_mechanic', I instantly know how to join against this in any direction. The hard part of understanding a schema is how it fits together, not what it represents, and when you have hundreds of tables and need to remember that join table's name, it's really, really nice if it's trivial to derive.
Lastly, the guide's recommended indentation for FROM vs JOIN statements seems off to me. Consider:
Well, the thing is, the riders table and bikes table have the exact same priority in the query, but this spacing really emphasizes the riders table and it's not clear why.