173

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.

viam0Zah
  • 25,949
  • 8
  • 77
  • 100
  • 13
    From http://msdn.microsoft.com/en-us/library/ms179300.aspx *The AS clause is the syntax defined in the ISO standard for assigning a name to a result set column. This is the preferred syntax to use in SQL Server 2005.* – Adriaan Stander Nov 12 '10 at 12:47
  • 3
    It is also used to separate the declaration of a procedure with its script. `CREATE PROC Test @Param1 INT AS SELECT @Param1` – Tom 'Blue' Piddock Nov 12 '10 at 14:20

9 Answers9

161

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

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • 14
    Actually, there is no difference in SQL but some dependent tools/libraries can be depended on this small keyword. As example: JDBC 4.0. Depending of using aliases w/ 'AS cause' and w/o you will receive different behavior - see this answer http://stackoverflow.com/a/4271250/814304. I would like to recommend ALWAYS use the full form of semantic to avoid such issues. – iMysak Aug 22 '16 at 22:24
  • Can i have aliases for more than one column? like, two columns with single aliases? – Deepak Keynes Dec 30 '17 at 06:03
  • @Keynes Yes. Just concatenate (||) the columns and then give it an alias e.g. SELECT foo || bar AS foobar. – Rupert Madden-Abbott Dec 10 '18 at 14:26
  • Yep @RupertMadden-Abbott, thanks! but i waited a bit long, i was in terms of context. – Deepak Keynes Dec 11 '18 at 11:19
  • Alias in select can't be used in where clause but in from ... as X can be used in where clause – Muhammad Umer Jul 30 '20 at 18:49
45

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
Alexander
  • 105,104
  • 32
  • 201
  • 196
XstreamINsanity
  • 4,176
  • 10
  • 46
  • 59
23

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.

Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
13

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..

Buhake Sindi
  • 87,898
  • 29
  • 167
  • 228
5

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...
Nicholas Knight
  • 15,774
  • 5
  • 45
  • 57
  • 4
    I know what's the purpose of table aliases. I'm curious, what's the reason of having a separate keyword for setting aliases while it works without it as well. – viam0Zah Nov 12 '10 at 12:42
5

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

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • "Of course, the better solution was to never have allowed duplicate column names in the first place! " - so company.name and country.name should not be allowed ? and what if I was joining a table to itself ? "In the early days of SQL, it was chosen ..." do you have a reference for this / is this rationale documented somewhere ? – Bob Dec 14 '18 at 17:40
  • @Bob I've updated my answer with a note (with citation) of the history of dot qualification in SQL, plus my admittedly vague recollection of why the `AS` keyword is optional (no citation, obviously!). Hugh retired a few years ago. I think Celko might be active still - would his recollections add weight? Evidence and paper trail simply does not exist :( – onedaywhen Jan 02 '19 at 16:52
  • "would his recollections add weight?"; No need to bother Mr Celko; the BS12 doc has Darwen's own words on the shortcomings of dot qualification - '70s memory constraints and repeated joins hadn't occurred to me. I will concede it does seem very possible aliasing made it into SQL for the same reason. – Bob Jan 03 '19 at 17:08
4

The AS in this case is an optional keyword defined in ANSI SQL 92 to define a <<correlation name> ,commonly known as 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 a best practice NOT to use the AS keyword for table aliases as it is not supported by a number of commonly used databases.

Geert Bellekens
  • 12,788
  • 2
  • 23
  • 50
  • 1
    Do you have any examples of dbs that don't use the 'as' keyword? – djones Jun 07 '16 at 18:48
  • 3
    I believe Oracle is one of them that doesn't support the `as`keyword for table aliases. – Geert Bellekens Jun 07 '16 at 18:58
  • 2
    "The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query." http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm . Also related http://stackoverflow.com/a/8451257/1359796 – HEDMON Sep 08 '16 at 07:48
  • I would say the best practice is to always include the AS keyword. – Colin Feb 13 '21 at 05:09
3

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.

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
-1

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.

user2063329
  • 443
  • 2
  • 5
  • 15