What is the purpose of the SQL keyword "AS"?

Question

You can set table aliases in SQL by typing the identifier immediately after the table name.

SELECT * FROM table t1; 

You can even use the AS keyword to specify an alias.

 SELECT * FROM table AS t1; 

What is the difference between them, if any?

I see that older DBA users tend to write statements without AS , but most newer manuals use it.

Update: I know what the purpose of table and column aliases is. I'm curious why you have a separate keyword for setting aliases while it works without it.

Answer 1

There is no difference between the two statements above. AS is just a more explicit way to mention an alias

Answer 2

All who answered before me are faithful. You use it as the name of the alias label for the table when you have long queries or queries that have joins. Here are some 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 is recommended to use the AS keyword and highly recommended, but you can run the same query without one (and 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 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 

Conclusion of Example 4

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

Answer 3

If you don’t know which syntax to choose, especially if there seems to be not enough to separate the selection, consult a book on heuristics. As far as I know, the only heuristic book for SQL is Joe Celko's SQL Programming Style:

A more frequent correlation name is called an alias, but I will be formal. In SQL-92, they can have an optional AS , and it must be used to make it clear that something is getting a new name. [P16]

That way, if your team doesn't like the agreement, you can blame Celko - I know what I'm doing;)

---

UPDATE 1: IIRC for a long time, Oracle did not support the AS keyword (previous correlation name), which may explain why some older timers do not use it normally.

---

UPDATE 2: the term “correlation name”, although used by the SQL standard, is inappropriate. The basic concept is a range variable.

---

UPDATE 3: I just re-read what Tselko wrote, and he is wrong: the table is not renamed! Now I think:

The name of the correlation is most often called an alias, but I will be formal. In standard SQL, they may have an optional AS keyword, but should not be used because it may give the impression that something is renamed when it is not. In fact, it should be omitted to indicate that it is a range variable.

Answer 4

The AS keyword is naming ALIAS the database table or table column. In your example, both statements are correct, but there are circumstances in which an AS clause is required (although the AS operator itself is optional), for example

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

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

Sorry if my explanations are not effective.

---

Update based on your comment, you're right, my previous statement was invalid. The only reason I can think of this is because the AS clause has existed for a long time in the SQL world, which RDMS has now included for backward compatibility.

Answer 5

Use is more obvious if you are not using "SELECT *" (this is a bad habit that you must break out of):

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

Answer 6

This is a formal way to specify a correlation name for an object so that you can easily access it in another part of the query.

Answer 7

AS in this case is an optional keyword defined in ANSI SQL 92 to define a <<correlation name> , commonly called alias for a table.

 <table reference> ::= <table name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] | <joined table> <derived table> ::= <table subquery> <derived column list> ::= <column name list> <column name list> ::= <column name> [ { <comma> <column name> }... ] Syntax Rules 1) A <correlation name> immediately contained in a <table refer- ence> TR is exposed by TR. A <table name> immediately contained in a <table reference> TR is exposed by TR if and only if TR does not specify a <correlation name>. 

It seems best practice NOT to use the AS keyword for table aliases, as it is not supported by a number of commonly used databases.

Answer 8

If you create a query using the query editor in SQL Server 2012, for example, you get the following:

  SELECT e.EmployeeID, s.CompanyName, o.ShipName FROM Employees AS e INNER JOIN Orders AS o ON e.EmployeeID = o.EmployeeID INNER JOIN Shippers AS s ON o.ShipVia = s.ShipperID WHERE (s.CompanyName = 'Federal Shipping') 

However, AS removal does not make any difference, as in the following:

  SELECT e.EmployeeID, s.CompanyName, o.ShipName FROM Employees e INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID INNER JOIN Shippers s ON o.ShipVia = s.ShipperID WHERE (s.CompanyName = 'Federal Shipping') 

In this case, the use of AS is redundant, but in many other places it is necessary.

Answer 9

In the early days of SQL, it was chosen as a solution to the problem of how to handle duplicate column names.

Borrow a request from another answer:

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

The ProductID column (and possibly others) is common to both tables, and since the syntax of the join condition requires a reference to both, dot qualification provides values.

Of course, the best solution was to never duplicate column names in the first place! Fortunately, if you use the new NATURAL JOIN , the need for P and O range variables disappears:

 SELECT ProductName, ProductRetailPrice, Quantity FROM Products NATURAL JOIN Orders WHERE OrderID = 123456