What's the purpose of SQL keyword "AS"?

Question

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

Everyone who answered before me is correct. You use it kind of as an alias shortcut name for a table when you have long queries or queries that have joins. Here's a couple examples.

Example 1

SELECT P.ProductName,
       P.ProductGroup,
       P.ProductRetailPrice
FROM   Products AS P

Example 2

SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Quantity
FROM   Products AS P
LEFT OUTER JOIN Orders AS O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

Example 3 It's a good practice to use the AS keyword, and very recommended, but it is possible to perform the same query without one (and I do often).

SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Quantity
FROM   Products P
LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

As you can tell, I left out the AS keyword in the last example. And it can be used as an alias.

Example 4

SELECT P.ProductName AS "Product",
       P.ProductRetailPrice AS "Retail Price",
       O.Quantity AS "Quantity Ordered"
FROM   Products P
LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

Output of Example 4

Product             Retail Price     Quantity Ordered
Blue Raspberry Gum  $10 pk/$50 Case  2 Cases
Twizzler            $5 pk/$25 Case   10 Cases

When you aren't sure which syntax to choose, especially when there doesn't seem to be much to separate the choices, consult a book on heuristics. As far as I know, the only heuristics book for SQL is 'Joe Celko's SQL Programming Style':

A correlation name is more often called an alias, but I will be formal. In SQL-92, they can have an optional AS operator, and it should be used to make it clear that something is being given a new name. [p16]

This way, if your team doesn't like the convention, you can blame Celko -- I know I do ;)

---

UPDATE 1: IIRC for a long time, Oracle did not support the AS (preceding correlation name) keyword, which may explain why some old timers don't use it habitually.

---

UPDATE 2: the term 'correlation name', although used by the SQL Standard, is inappropriate. The underlying concept is that of a ‘range variable’.

---

UPDATE 3: I just re-read what Celko wrote and he is wrong: the table is not being renamed! I now think:

A correlation name is more often called an alias, but I will be formal. In Standard SQL they can have an optional AS keyword but it should not be used because it may give the impression that something is being renamed when it is not. In fact, it should be omitted to enforce the point that it is a range variable.

The AS keyword is to give an ALIAS name to your database table or to table column. In your example, both statement are correct but there are circumstance where AS clause is needed (though the AS operator itself is optional), e.g.

SELECT salary * 2 AS "Double salary" FROM employee;

In this case, the Employee table has a salary column and we just want the double of the salary with a new name Double Salary.

Sorry if my explanation is not effective.

---

Update based on your comment, you're right, my previous statement was invalid. The only reason I can think of is that the AS clause has been in existence for long in the SQL world that it's been incorporated in nowadays RDMS for backward compatibility..

The use is more obvious if you don't use 'SELECT *' (which is a bad habit you should get out of):

SELECT t1.colA, t2.colB, t3.colC FROM alongtablename AS t1, anotherlongtablename AS t2, yetanotherlongtablename AS t3 WHERE t1.colD = t2.colE...

It's a formal way of specifying a correlation name for an entity so that you can address it easily in another part of the query.