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

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 2

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 3

As far as when AS should be explicit specified, it depends on the syntax supported by the particular engine and personal preference (or even policy).

In SQL Server (which is all I deal with), AS is optional in the case of after a relation name in a FROM or JOIN. In such cases I skip the keyword due to my preference and that I find that it does not "decrease readability" when used with a consistent line-oriented join form.

However, when using derived queries, SQL Server requires the AS keyword and so I diligently include it in such cases.

Once again, in output clauses due to preference, I include AS. I believe my choice of the keyword here is due to the fact that, unlike with my join formatting, it is often the case that multiple identifiers appear on the same line.

So, for me in SQL Server:

SELECT a.all AS my, a.stuff  -- AS    (preference)
FROM someTableName a         -- no AS (preference)
INNER JOIN (
    SELECT ..
    ) AS b                   -- AS (required)
  ON a.id = b.id

Answer 4

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

Answer 5

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 6

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