Opinions: SQL Statements, do you use table aliases?

Question

One of the guys I work with has the following to say about using SQL aliases, and he just posted the following on reddit. What do you guys think, to alias or not to alias?

....

So I've always been the odd man out of my with my dev team about how SQL should be written. I learned in school to use aliases, but I hate them personally. I find it can make the statement unreadable if long. I want to know what tables are joining to what other tables. I don't want to have to search through a 5-10 table join to figure out what "p" means, is that the products table, people, etc...?

Personally I don't find it hard to use a little double click, ctrl+c, ctrl+v. Use the full table name (of course sometimes you have to join to the same table twice which you'll be forced to use aliases in some more bizarre queries.) Ex. My style

SELECT *
FROM
    People 
JOIN
    Orders ON People.PersonID = Orders.PersonID
JOIN
    OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN
    Products ON Products.ProductID = OrderDetails.ProductID
JOIN
    Country ON People.CountryID = Country.CountryID

VS

SELECT *
FROM
    People p
JOIN
    Orders o ON p.PersonID = o.PersonID
JOIN
    OrderDetails od ON o.OrderID = od.OrderID
JOIN
    Products pr ON pr.ProductID = od.ProductID
JOIN
    Country c ON p.CountryID = c.CountryID

Personal preferences yourself? I mean just consider if this query was a larger join and you had a large select statement.

I assume most people use alias as a time saver, do you think it's also more readable? Can you go to your old sql and instantly understand how your joins work?

UPDATE: Specifically I'm trying to argue that shorting the names down to single letters can make the sql statement unreadable. Changing a table from ShoeStackerCrazyProducts down to ShowProducts I think can make sense. Shorting it to sscp on the other hand would make it unreadable. Thoughts?

Answer 1

This is subjective, but when your table names get long then aliases are much more readable. Remember that column names will also need qualifiers, so you can quickly end up with long lists of CamelCasedTableNames.WithDelimiters.

Real-world example, taming some legacy tables:

SELECT count(*) as c, a.Attributeid, property as AttributeName, 
    a.Subattributeid, SubProperty as SubAttributeName 
    FROM prodAttribute a 
    INNER JOIN phAttributeDisplayOrder o 
        ON a.attributeID = o.AttributeID 
    INNER JOIN lstphysicalproperties y 
        ON a.attributeid = y.physicalpropertyid 
    INNER JOIN (select * from tblsubPhysicalProperties where deleted is null) z 
        ON a.attributeid = z.propertyid 
            AND a.subAttributeID = z.subpropertynumbering 
    GROUP BY o.ranking, a.attributeid, property, a.subattributeid, SubProperty 
    ORDER BY o.ranking, a.attributeid, a.subattributeid

That's bad enough without the fully-qualified names.

If I were writing it today, I might avoid the one-character aliases, but in a query this short it's not as important.

Answer 2

I use SqlPrompt (RedGate) which will do all that copy/paste for you and I still use aliases all the time. What I don't do (and can't stand) is using the alphabet to alias. In other words, the first table I write is 'a', the second 'b' and so forth. For small queries I use the first letter of the table. For longer queries I'll use the whole table name.

Of course, I use schemas so my fully qualified object names tend to be a LOT longer than the example given.

Answer 3

Alias is better. In the case you have Product and People, just use different abbreviations. Pd for product, pl for people, for example.

Besides, in the case where you have inner queries, aliases are critical.

Answer 4

Alias are really handy if you need the 3 part name if you are doing logic across multiple databases. Also, their utility is directly proportional to the length of the DB/schema/table names and the thoughtfulness of the alias.

In my environment we do a lot of work with multiple DBs in the same query, and aliases are invaluable, especially when you have long DB names like:

CustomerNameDataClaimsDetail
CustomerNameDataClaimsHeader

and those DBs have tables like:

ClaimsDetailNew
ClaimsDetailLoad

In that instance it makes a lot of sense to alias CustomerNameDataClaimsDetail.dbo.ClaimsDetailNew to CDN or some such.

Answer 5

If your tables are named the same, but on different databases, you're forced to alias. This also occurs across identically-named tables but differently named schemas.

SELECT * FROM
DB1.dbo.Table1
JOIN DB2.dbo.Table1 ON DB1.dbo.Table1.field = DB2.dbo.Table1.field

will not work, where

SELECT * FROM
DB1.dbo.Table1 T1
JOIN DB2.dbo.Table1 T2 ON T1.field = T2.field

will work.

Also with a self join you're forced to alias.

Answer 6

Personally, I always use aliases, even on a single table query. I also make it a habit to use that alias with every column reference, even when the column name is unique and the alias is not strictly required. As long as your aliases are meaningful (e.g., alias Products as pr, not as A), you shouldn't lose much readability.

See also: Brent Ozar's Blog Entry on this topic.

On a related note, I also always include the schema name on every table reference (e.g., dbo.Products vs. Products) even when it's the default schema.

Answer 7

It's more common that SQL IDEs support intellisense:

• SQL Server Management Studio 2008 does (but only against 2008+ instances)
• Oracle's PLSQL Developer
• RedGate's SqlPrompt

...which makes it really easy to work with aliases.

That said, I don't think it is subjective to use table aliases.

1. Who really wants to re-write the table name (especially three/four name syntax in TSQL) for every column reference that needs to be qualified? That's just making more work, not less--and it increases the possibility of typos.
2. Every column reference should be qualified; SELECT * is to be avoided unless actually using all columns. By having the table alias present in a column reference, it's clear where the column is from and can be relied upon for errors if the column is either altered or disappears from the table. Nothing like getting an error about "erroneous column 'id' " that you then have to chase down--include the table alias, and you've minimized the amount of work necessary to debug the issue.
3. Table aliases are required if you want to have multiple instances of the same table in a single query.

Conclusion

The ability to maintain SQL is made that much easier by using table aliases.

Answer 8

I always use aliases as soon as more than one table is involved.

I think that makes JOINs much more readable, especially because we have quite a few tables with composite keys. If you always have to type the table names, it's more work and less readable:

select *
from TableWithLongName
inner join AnotherTableWithLongName on TableWithLongName.Column1 = AnotherTableWithLongName.Column1 and TableWithLongName.Column2 = AnotherTableWithLongName.Column2

...than this one:

select *
from TableWithLongName t
inner join AnotherTableWithLongName a on t.Column1 = a.Column1 and t.Column2 = a.Column2

Plus, you wouldn't normally do SELECT * in a real world app, you would specify which columns to select. If you join two or more tables, you have to prefix the columns with the table names.

Again, aliases to the rescue...because I'd prefer this one:

select t.Column1, t.Column2, a.Column1, a.Column2
from ...

...and not:

select TableWithLongName.Column1, TableWithLongName.Column2, AnotherTableWithLongName.Column1, AnotherTableWithLongName.Column2
from ...

Concerning how to name the aliases:
When only two or three tables are involved, I mostly use single letters as aliases. If you only have two tables with completely different names, it's not that hard to memorize that "o" means "Orders" and "p" means "Products".
If short aliases are likely to cause confusion (like "p1" for "People" and "p2" for "Products") I use longer aliases, but still not the whole name (something like "peo" and "pro").