Why would you use "AS" when aliasing a SQL table?

Question

I just came across a SQL statement that uses AS to alias tables, like this:

SELECT all, my, stuff
FROM someTableName AS a
INNER JOIN someOtherTableName AS b
    ON a.id = b.id

What I'm used to seeing is:

SELECT all, my, stuff
FROM someTableName a
INNER JOIN someOtherTableName b
    ON a.id = b.id

I'm assuming there's no difference and it's just syntactic sugar, but which of these is more prevalent/wide-spread? Is there any reason to prefer one over the other?

Edited to clarify:

I appreciate all the answers and all the points made, but the question was not why or why not to use table aliases. The question was purely about using the "AS" keyword for table aliasing or leaving it out.

Answer 1

Not all databases support the AS statement as far as I know. (Oracle?) But for some reason it looks more readable.

Edit: Oracle doesn't support the 'AS' keyword over here;

ORA-00933: SQL command not properly ended

Answer 2

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.

Answer 3

It's generally preferred. Consider what happens if you're using old 'comma notation' for joins, and you miss a comma.

Instead of:

select *
from Orders, Customers;

You end up with:

select *
from Orders Customers; --Customers is now the alias for Orders.

And whilst this isn't fixed by introducing 'as', you can more easily tell if it's intended (since I may have actually wanted to alias Orders as Customers, depending on what else I was doing to it during my query).

Answer 4

Probably by using AS you can quickly see which are the tables getting used as Alias.

Answer 5

First of all, aliasing with "a" is actually often considered a Bad Thing (we officially prohibit it in our coding standard). The reason is that in a long complicated multi-table query people lose track of which alias stands for which table.

Saving 2 seconds on typing - especially in this era of intellisenseish IDEs - is kind of idiotic when weighed against readability/maintainability.

The main legit use of aliasing is to do self-joins

Answer 6

I think the first one is more "polite" and parser friendly

What about this:

SELECT 
     f1 = Field1
     ,f2 = Field2
FROM someTableName

and

SELECT 
         Field1 AS f1
         ,Field2 AS f2
    FROM someTableName

Answer 7

Field aliases are for readability of the output. Table aliases are for readability of the query structure. Especially when your're dealing with long table names and possibly even cross-database references.

If you have duplicate table references in your query, you should always use table aliases to distinguish one table from the other. For instance, a parent-child join could look somewhat like this:

SELECT parent.Name AS ParentName,
child.Name AS ChildName
FROM MyTable AS parent
INNER JOIN MyTable as child
ON parent.ID = child.ParentID

Answer 8

If you have a huge SQL statement with various joins, aliases make it easier to read/understand where the columns are coming from

One of our applications can't handle hyphens in column names (don't ask me why), so aliases are a perfect method for converting COLUMN-NAME to COLUMN_NAME