T SQL - Why is AS not required here?

Question

The solution: https://stackoverflow.com/a/439768/857994 gives the following code:

    DELETE TableA 
    FROM TableA a
    INNER JOIN
    TableB b on b.Bid = a.Bid
    and [my filter condition]

My question is:

How come we don't need an AS in the FROM to alias TableA to a here? Wouldn't we need the AS in a select query like SELECT E.FirstName FROM Employee AS E;?

What's the difference?

Honestly, I'm stuck on a computer with no admin rights till IT gives them to me (new job). So, #1 I cant, and #2 that wouldn't tell me if there's a reason for the difference and why there's a preference if it does work :) — John Humphreys, Apr 11 '12 at 15:55

Duane Theriot · Accepted Answer · 2012-04-11T16:09:51.453

The "AS" keyword there is optional. You don't need it in SELECT queries either. (Try it!)

Here's some of the syntax of "FROM" from the MSDN:

[ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
    ...
}

As you can see, the [ AS ] is in brackets, which means it's optional. You never need to add it, though I suppose some may argue that it makes the query more readable. I disagree, but that's a matter of personal preference (or your DBA's personal preference).

See http://msdn.microsoft.com/en-us/library/ms177634.aspx for more detail.

score 2 · Answer 2 · answered Apr 11 '12 at 15:58

2

They are the same thing, either aliased with an 'as' or with out. See: http://www.tsqltutorials.com/aliases.php

answered Apr 11 '12 at 15:58

Iron Ninja

434
1
3
13

score -1 · Answer 3 · answered Apr 11 '12 at 15:58

-1

You need an AS when aliasing a column name to be selected, but not when aliasing a table name in the FROM clause. And, no, you wouldn't need the AS in that second, shorter SELECT.

There's no "why" to this, other than "that's the defined syntax of the language." I find that trying to second-guess the Secret Hidden Motives of language designers leads to no good.

answered Apr 11 '12 at 15:58

mjfgates

3,351
1
18
15

This. You need it in a SELECT statement for a column, not in a FROM for a table. – PRNDL Development Studios Apr 11 '12 at 16:01
1

"This", unfortunately, is simply incorrect. [The documentation for the SELECT clause](http://msdn.microsoft.com/en-us/library/ms176104.aspx) clearly shows that the `AS` for a column alias is **optional**. Try it. – AakashM Apr 11 '12 at 16:08
You definitely don't need "AS" for column aliases. These work just fine: `SELECT Column1 alias1 FROM MyTable;` or `SELECT alias1 = Column1 FROM MyTable;`. – Duane Theriot Apr 11 '12 at 16:18
I've been working in Access recently; in its dialect of SQL, you *do* need the "AS" when aliasing column entries. Which, yeah, Access isn't exactly the language standard, but that's where the notion comes from. – mjfgates Apr 11 '12 at 17:31
The question is tagged with tsql which means related to SQL Server, not Access. I've added the sql-server tag for clarity. – Valentino Vranken Jul 31 '12 at 08:44

T SQL - Why is AS not required here?

3 Answers3