SQL Table Aliases - Good or Bad

SQL Table Aliases - Good or Bad?

Table aliases are a necessary evil when dealing with highly normalized schemas. For example, and I'm not the architect on this DB so bear with me, it can take 7 joins in order to get a clean and complete record back which includes a person's name, address, phone number and company affiliation.

Rather than the somewhat standard single character aliases, I tend to favor short word aliases so the above example's SQL ends up looking like:

select person.FirstName
,person.LastName
,addr.StreetAddress
,addr.City
,addr.State
,addr.Zip
,phone.PhoneNumber
,company.CompanyName
from tblPeople person
left outer join tblAffiliations affl on affl.personID = person.personID
left outer join tblCompany company on company.companyID = affl.companyID

... etc

How does table alias names affect performance?

The alias doesn't affect performance in any practical or measurable way at all (italics added on edit). That is, it would add a barely (if it all) measurable delay to query compilation. Once compiled (and re-used), it has no effect.

An alias removes ambiguity when you have more than one table because you know which table it comes from. It also prevents future table changes from breaking a query. Say, you add an audit column to one table where it already exists in another table. Queries without aliases using both tables will break.

An alias is also mandatory in some cases e.g. schema bound views.

The SQL parsing engine (that reads all queries before executing them, and uses the information to cache the compiled queries in the future for faster execution) is the only thing that looks at the aliases, and uses it to help remove ambiguities in symbol lookups. The system would already produce symbols, just like any other compilable statement in any other language, when it's being parsed prior to execution-storage.

Is it always a good practice to use aliases in sql joins or nested queries?

  1. You are probably confusing "needing to use a table prefix" and "needing to use an ALIAS" when referring to breaking things.

    The query might indeed be more likely to break after adding a join if you don't use a table prefix; when your original table and a newly added table share a column with the same name. So for the sake of future maintenance, always using a table prefix is a GOOD idea for all columns in the query.

    However, this problem is solved by using ANY table prefix in front of columns, whether real table name or alias name.

  2. The alias is needed (as opposed to actual table name) when you use the same table twice.

  3. From a lot of experience with maintaining lots of complex SQL, I must say that my view is 100% opposite of yours.

    Namely, using a short - especially 1-letter - table alias makes for a HARDER to read/maintain code.

    When you're debugging a long piece of SQL with complex joints at 2am in the morning during production emergency, looking back/forth 10-15 lines above to see what table matches alias "e" is MUCH harder.

    This point has 2 exceptions

    • when the business logic of the query uses the table for a purpose which is VERY distinct from the table name.

    • when the table name is unreasonably long and unreasonable due to circumstances beyond your control - and then the alias should still be something readable and logical. E.g. "EmployeeTableIndexedByUIDSourcedFromHR" can and usually should be aliases as "Employee", but not as "E"

  4. Also, to avoid having overly long strings, it helps hreatly if you format your queries using newlines and alignment:

    Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Id

vs

SELECT  Employee.Id
,Dept.DeptName
FROM Employee
JOIN Dept
ON Employee.DeptId=Dept.Id

Why would you use AS when aliasing a SQL table?

It's syntactic sugar and it takes a little bit longer to type but some people find it more readable and clear. The reason I use it is that when reading a large query, it is easier to pick out the aliases by looking for the AS's.

Another reason, sometimes the full table name is long and cumbersome to type. Aliasing to something shorter can sometimes make things easier for when you don't have fancy features like autocomplete - or for when you're just feeling lazy. ;)

...And as some others have pointed out before me, it can be useful when doing self-joins.

What table will be taken if table alias is not provided?

If SQL Server (or any database) finds an unqualified column name, then it looks to see which tables/subqueries in the FROM clause or outer queries might be providing it.

If the column is in exactly one table/subquery in the FROM clause, then the column is assumed to come from the table/subquery.

If the column is in multiple tables/subqueries in the FROM clause, then the query returns an error.

If the column does not exist in the FROM clause nor in any outer queries, the query returns an error.

If the column does not exist in the FROM clause, but does in an outer query, then that reference is used.

These rules go by the name "scoping". That is a common term in computer languages for figuring out the value of a variable.

What is the point using AS keyword in SQL when aliasing can be done without it?

I think the reason is simple. Consider code such as the following:

select a, b, c, d
. . .

It is very easy to occasionally skip the comma:

select a b, c, d

If you don't use as then this looks like correct code and it can be difficult to figure out. If you always use as for column aliases, then you know it is incorrect.



Related Topics



Leave a reply



Submit