What s the purpose of SQL keyword AS

Question

You can set table aliases in SQL typing the identifier right after the table name   SELECT   FROM table t1    You can even use the keyword AS to indicate the alias   SELECT   FROM table AS t1    What s the difference between them if any   I see old DBA people tend to write statements without AS  but most of the new tutorials use it   Update  I know what s the purpose of table and column aliases  I m curious  what s the reason of having a separate keyword for setting aliases while it works without it as well

User · Answer

In the early days of SQL  it was chosen as the solution to the problem of how to deal with duplicate column names  see below note    To borrow a query 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 column ProductID  and possibly others  is common to both tables and since the join condition syntax requires reference to both  the  dot qualification  provides disambiguation   Of course  the better solution was to never have allowed duplicate column names in the first place  Happily  if you use the newer NATURAL JOIN syntax  the need for the range variables P and O goes away   SELECT ProductName  ProductRetailPrice  Quantity   FROM Products NATURAL JOIN Orders  WHERE OrderID   123456   But why is the AS keyword optional  My recollection from a personal discussion with a member of the SQL standard committee  either Joe Celko or Hugh Darwen  was that their recollection was that  at the time of defining the standard  one vendor s product  Microsoft s   required its inclusion and another vendor s product  Oracle s   required its omission  so the compromise chosen was to make it optional  I have no citation for this  you either believe me or not     In the early days of the relational model  the cross product  or theta-join or equi-join  of relations whose headings are not disjoint appeared to produce a relation with two attributes of the same name  Codd s solution to this problem in his relational calculus was the use of dot qualification  which was later emulated in SQL  it was later realised that so-called natural join was primitive without loss  that is  natural join can replace all theta-joins and even cross product     Source  Business System 12  Notes keyed to slides of the presentation given at TTM Implementers    Workshop  University of Northumbria  2-3 June 2011 by Hugh Darwen

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you design query using the Query editor in SQL Server 2012 for example you would get this     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 removing the AS 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 use of AS is superfluous but in many other places it is needed

User · Answer

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

User · Answer

The AS in this case is an optional keyword defined in ANSI SQL 92 to define a  lt  lt correlation name gt   commonly known as alias for a table     lt table reference gt                   lt table name gt      AS    lt correlation name gt                     lt left paren gt   lt derived column list gt   lt right paren gt                   lt derived table gt    AS    lt correlation name gt                     lt left paren gt   lt derived column list gt   lt right paren gt                 lt joined table gt         lt derived table gt       lt table subquery gt         lt derived column list gt       lt column name list gt         lt column name list gt                 lt column name gt       lt comma gt   lt column name gt                Syntax Rules       1  A  lt correlation name gt  immediately contained in a  lt table refer-         ence gt  TR is exposed by TR  A  lt table name gt  immediately contained         in a  lt table reference gt  TR is exposed by TR if and only if TR         does not specify a  lt correlation name gt      It seems a best practice NOT to use the AS keyword for table aliases as it is not supported by a number of commonly used databases

User · Answer

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

[sql] What's the purpose of SQL keyword "AS"?

Examples related to sql