SQL SQL关键字“AS”的目的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4164653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the purpose of SQL keyword "AS"?
提问by viam0Zah
You can set table aliases in SQL typing the identifier right after the table name.
您可以在 SQL 中设置表别名，在表名之后输入标识符。
SELECT * FROM table t1;
You can even use the keyword ASto indicate the alias.
您甚至可以使用关键字AS来表示别名。
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.
我看到老 DBA 人倾向于编写不带 的语句AS，但大多数新教程都使用它。
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.
更新：我知道表和列别名的目的是什么。我很好奇，有一个单独的关键字来设置别名的原因是什么，而它也可以在没有它的情况下工作。
采纳答案by Sachin Shanbhag
There is no difference between both statements above. AS is just a more explicit way of mentioning the alias
以上两种说法没有区别。AS 只是一种更明确的提及别名的方式
回答by XstreamINsanity
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
示例 1
SELECT P.ProductName,
P.ProductGroup,
P.ProductRetailPrice
FROM Products AS P
Example 2
示例 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 3It'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).
示例 3使用 AS 关键字是一种很好的做法，非常推荐，但也可以不使用关键字来执行相同的查询（我经常这样做）。
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.
如您所知，我在上一个示例中省略了 AS 关键字。它可以用作别名。
Example 4
示例 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
示例 4 的输出
Product Retail Price Quantity Ordered
Blue Raspberry Gum pk/ Case 2 Cases
Twizzler pk/ Case 10 Cases
回答by onedaywhen
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':
当您不确定要选择哪种语法时，尤其是当似乎没有太多可区分选择的语法时，请查阅有关启发式的书籍。据我所知，唯一的 SQL 启发式书籍是“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
ASoperator, and it should be used to make it clear that something is being given a new name. [p16]
相关名称更常被称为别名，但我会比较正式。在 SQL-92 中，它们可以有一个可选的
AS操作符，它应该用于明确某些东西正在被赋予一个新名称。[第 16 页]
This way, if your team doesn't like the convention, you can blame Celko -- I know I do ;)
这样，如果你的团队不喜欢这个惯例，你可以责怪 Celko——我知道我喜欢；)
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 1：IIRC很久了，Oracle不支持AS（前置相关名）关键字，这或许可以解释为什么一些老手不习惯使用它。
UPDATE 2: the term 'correlation name', although used by the SQL Standard, is inappropriate. The underlying concept is that of a ‘range variable'.
更新 2：术语“相关名称”虽然被 SQL 标准使用，但并不合适。基本概念是“范围变量”的概念。
UPDATE 3: I just re-read what Celko wrote and he is wrong: the table is not being renamed! I now think:
更新 3：我刚刚重读了 Celko 写的内容，他错了：表没有被重命名！我现在认为：
A correlation name is more often called an alias, but I will be formal. In Standard SQL they can have an optional
ASkeyword 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.
相关名称更常被称为别名，但我会比较正式。在标准 SQL 中，它们可以有一个可选的
AS关键字，但不应该使用它，因为它可能给人的印象是某物正在被重命名，而实际上它并没有被重命名。事实上，为了强调它是一个范围变量，应该省略它。
回答by Buhake Sindi
The ASkeyword is to give an ALIASname 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 ASoperator itself is optional), e.g.
该AS关键字是给一个ALIAS名到数据库表或表列。在您的示例中，这两个语句都是正确的，但在某些情况下需要 AS 子句（尽管AS运算符本身是可选的），例如
SELECT salary * 2 AS "Double salary" FROM employee;
In this case, the Employeetable has a salarycolumn and we just want the double of the salary with a new name Double Salary.
在这种情况下，该Employee表有一个salary列，我们只需要一个新名称的薪水的两倍Double Salary。
Sorry if my explanation is not effective.
对不起，如果我的解释无效。
Updatebased on your comment, you're right, my previous statement was invalid. The only reason I can think of is that the ASclause has been in existence for long in the SQL world that it's been incorporated in nowadays RDMS for backward compatibility..
根据您的评论更新，您说得对，我之前的声明无效。我能想到的唯一原因是该AS子句在 SQL 世界中已经存在很长时间了，现在为了向后兼容，它已被合并到 RDMS 中。
回答by Nicholas Knight
The use is more obvious if you don't use 'SELECT *' (which is a bad habit you should get out of):
如果您不使用 'SELECT *'（这是一个您应该改掉的坏习惯），则使用会更加明显：
SELECT t1.colA, t2.colB, t3.colC FROM alongtablename AS t1, anotherlongtablename AS t2, yetanotherlongtablename AS t3 WHERE t1.colD = t2.colE...
回答by Preet Sangha
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.
这是为实体指定相关名称的正式方式，以便您可以在查询的另一部分轻松解决它。
回答by Geert Bellekens
The ASin this case is an optional keyword defined in ANSI SQL 92to define a <<correlation name>,commonly known as aliasfor a table.
在AS这种情况下是在所定义的可选关键字ANSI SQL 92以限定<<correlation name>，通常称为别名为表。
<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>.
<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 ASkeyword for table aliases as it is not supported by a number of commonly used databases.
最好不要将AS关键字用于表别名，因为许多常用数据库不支持它。
回答by onedaywhen
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).
在 SQL 的早期，它被选为如何处理重复列名问题的解决方案（见下面的注释）。
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.
该列ProductID（也可能是其他列）对两个表都是通用的，并且由于连接条件语法需要同时引用这两个表，因此“点限定”可以消除歧义。
Of course, the better solution was to never have allowed duplicate column names in the first place! Happily, if you use the newer NATURAL JOINsyntax, the need for the range variables Pand Ogoes away:
当然，更好的解决方案是从一开始就不允许重复的列名！令人高兴的是，如果你使用新NATURAL JOIN语法，需要对一系列变量P和O消失：
SELECT ProductName, ProductRetailPrice, Quantity
FROM Products NATURAL JOIN Orders
WHERE OrderID = 123456
But why is the ASkeyword optional? My recollection from a personal discussion with a member of the SQL standard committee (either Joe Celko or Hugh Darwen) was that theirrecollection 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!
但是为什么AS关键字是可选的呢？从SQL标准委员会成员的个人问题，我的记忆（无论是乔·塞科或休·达文）是他们的回忆是，在界定的标准，一个供应商的产品（微软？）需要将其列入的时间和其他供应商的产品（Oracle 的？）要求省略它，因此选择的折衷方案是使其可选。我对此没有任何引证，你信不信！
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.)
在关系模型的早期，标题不相交的关系的叉积（或 theta-join 或 equi-join）似乎会产生具有相同名称的两个属性的关系；Codd 在他的关系演算中解决这个问题的方法是使用点限定，后来在 SQL 中得到了模拟（后来意识到所谓的自然连接是原始的没有损失的；即自然连接可以替换所有的 theta-joins 和甚至交叉产品。）
资料来源：Business System 12，Hugh Darwen 于 2011 年 6 月 2 日至 3 日在诺桑比亚大学 TTM 实施者研讨会上提供的演示文稿幻灯片的注释
回答by user2063329
If you design query using the Query editor in SQL Server 2012 for example you would get this:
例如，如果您使用 SQL Server 2012 中的查询编辑器设计查询，您将得到：
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:
但是，删除 AS 没有任何区别，如下所示：
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.
在这种情况下，使用 AS 是多余的，但在许多其他地方却是需要的。
