Why Would You Use "As" When Aliasing a SQL Table

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 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.

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

When was aliasing using = versus as introduced and what is the name of that version

This is of a great surprise to me. It seems that the column_alias = expression has always been part of the original syntax of T-SQL.

The AS command was added to T-SQL.80 (2000) to be SQL-92 compatible.

Excerpt of SQL2000_release.pdf page 1459

The AS clause is the syntax defined in the SQL-92 standard for assigning a name to a result set column. This is the preferred
syntax to use in Microsoft® SQL Server™.

column_name AS column_alias

Or

result_column_expression AS derived_column_name

Transact-SQL also supports the following syntax for compatibility with earlier versions of SQL Server:

column_alias = column_name

whats is the importance of table alias in sql join query?

No, your assumption is not true.

There are at least four reasons to use table aliases:

• If the alias is shorter than the name it allows you to type less.
• If you self-join a table you are required to give an alias to one or both of the tables to allow you to distinguish between them.
• Derived tables must have an alias in some database systems (not Oracle though, I think).
• The alias can make it clearer what the role of the table is in that particularly query.

There is no significant performance penalty from using them.

What's the purpose of SQL keyword AS ?

There is no difference between both statements above. AS is just a more explicit way of mentioning the alias

Are you required to use the alias when referencing a table after aliasing?

Yes, table alias renames the table in the context of the query, making the original name inaccessible.

For example, from PostgreSQL documentation:

The alias becomes the new name of the table
reference so far as the current query is
concerned — it is not allowed to refer to the
table by the original name elsewhere in the query.

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

---

Related Topics