什么时候需要在SQL中为表名提供别名？

I noticed when doing a query with multiple JOINs that my query didn't work unless I gave one of the table names an alias.

我注意到在使用多个JOIN进行查询时，我的查询不起作用，除非我给其中一个表名别名。

Here's a simple example to explain the point:

这是一个简单的例子来解释这一点：

This doesn't work:

这不起作用：

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases on items.date=purchases.purchase_date
group by folder_id

This does:

这样做：

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases as p on items.date=p.purchase_date
group by folder_id

Can someone explain this?

有人可以解释一下吗？

6 个解决方案

#1

You are using the same table Purchases twice in the query. You need to differentiate them by giving a different name.

您在查询中使用相同的表购买两次。您需要通过提供不同的名称来区分它们。

You need to give an alias:

你需要给别名：

When the same table name is referenced multiple times
多次引用相同的表名时

Imagine two people having the exact same John Doe. If you call John, both will respond to your call. You can't give the same name to two people and assume that they will know who you are calling. Similarly, when you give the same resultset named exactly the same, SQL cannot identify which one to take values from. You need to give different names to distinguish the result sets so SQL engine doesn't get confused.

想象一下两个人拥有完全相同的John Doe。如果你打电话给John，他们都会回应你的电话。你不能给两个人同名，并假设他们会知道你在跟谁打电话。同样，当您为同名的结果集提供完全相同的结果集时，SQL无法识别从中获取值的结果集。您需要提供不同的名称来区分结果集，以便SQL引擎不会混淆。

Script 1: t1 and t2 are the alias names here

脚本1：t1和t2是别名

SELECT      t1.col2
FROM        table1 t1
INNER JOIN  table1 t2
ON          t1.col1 = t2.col1

When there is a derived table/sub query output
当有派生表/子查询输出时

If a person doesn't have a name, you call them and since you can't call that person, they won't respond to you. Similarly, when you generate a derived table output or sub query output, it is something unknown to the SQL engine and it won't what to call. So, you need to give a name to the derived output so that SQL engine can appropriately deal with that derived output.

如果一个人没有姓名，你可以打电话给他们，因为你不能打电话给那个人，他们不会回复你。类似地，当您生成派生表输出或子查询输出时，它是SQL引擎未知的东西，它不会调用什么。因此，您需要为派生输出命名，以便SQL引擎可以适当地处理派生的输出。

Script 2: t1 is the alias name here.

脚本2：t1是此处的别名。

SELECT col1
FROM
(
    SELECT col1
    FROM   table1
) t1

#2

The only time it is REQUIRED to provide an alias is when you reference the table multiple times and when you have derived outputs (sub-queries acting as tables) (thanks for catching that out Siva). This is so that you can get rid of ambiguities between which table reference to use in the rest of your query.

唯一需要提供别名的时候是多次引用表时以及何时有派生输出（子查询充当表）（感谢将其捕获到Siva中）。这样您就可以消除在其他查询中使用哪个表引用之间的歧义。

To elaborate further, in your example:

进一步详细说明，在您的示例中：

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases on items.date=purchases.purchase_date
group by folder_id

My assumption is that you feel that each join and its corresponding on will use the correlating table, however you can use whichever table reference you want. So, what happens is that when you say on items.date=purchases.purchase_date, the SQL engine gets confused as to whether you mean the first purchases table, or the second one.

我的假设是你觉得每个连接及其相应的on将使用相关表，但是你可以使用你想要的任何表引用。所以，当你在items.date = purchases.purchase_date上说话时，SQL引擎会对你是指第一个购买表还是第二个购买表感到困惑。

By adding the alias, you now get rid of the ambiguities by being more explicit. The SQL engine can now say with 100% certainty which version of purchases that you want to use. If it has to guess between two equal choices, then it will always throw an error asking for you to be more explicit.

通过添加别名，您现在可以通过更明确地消除歧义。 SQL引擎现在可以100％确定地说您要使用哪个版本的购买。如果它必须猜测两个相同的选择，那么它总是会抛出一个错误，要求你更明确。

#3

It is required to give them a name when the same table is used twice in a query. In your case, the query wouldn't know what table to choose purchases.purchase_date from.

在查询中使用两次相同的表时，需要为它们指定名称。在您的情况下，查询将不知道从哪个表中选择purcha.purchase_date。

#4

In this case it's simply that you've specified purchases twice and the SQL engine needs to be able to refer to each dataset in the join in a unique way, hence the alias is needed.

在这种情况下，只是您已经指定了两次购买，并且SQL引擎需要能够以独特的方式引用连接中的每个数据集，因此需要别名。

As a side point, do you really need to join into purchases twice? Would this not work:

作为一个侧面，你真的需要加入购买两次？这会不起作用：

SELECT 
    subject
from 
    items
    join purchases 
        on items.folder_id=purchases.item_id
        and items.date=purchases.purchase_date
group by folder_id

#5

The alias are necessary to disambiguate the table from which to get a column.

别名对于消除从中获取列的表的歧义是必要的。

So, if the column's name is unique in the list of all possible columns available in the tables in the from list, then you can use the coulmn name directly.

因此，如果列的名称在from列表中的表中可用的所有可能列的列表中是唯一的，则可以直接使用coulmn名称。

If the column's name is repeated in several of the tables available in the from list, then the DB server has no way to guess which is the right table to get the column.

如果在列表中的几个表中重复列的名称，则DB服务器无法猜测哪个是获取列的正确表。

In your sample query all the columns names are duplicated because you're getting "two instances" of the same table (purchases), so the server needs to know from which of the instance to take the column. SO you must specify it.

在您的示例查询中，所有列名称都是重复的，因为您正在获取同一个表（购买）的“两个实例”，因此服务器需要知道从哪个实例获取该列。所以你必须指定它。

In fact, I'd recommend you to always use an alias, unless there's a single table. This way you'll avoid lots of problems, and make the query much more clear to understand.

事实上，我建议你总是使用别名，除非有一个表。这样您就可以避免许多问题，并使查询更加清晰易懂。

#6

You can't use the same table name in the same query UNLESS it is aliased as something else to prevent an ambiguous join condition. That's why its not allowed. I should note, it's also better to use always qualify table.field or alias.field so other developers behind you don't have to guess which columns are coming from which tables.

您不能在同一个查询中使用相同的表名，除非它是别名以防止出现模糊的连接条件。这就是为什么它不被允许。我应该注意，使用always qualify table.field或alias.field也更好，这样你身后的其他开发人员就不必猜测哪些列来自哪些表。

When writing a query, YOU know what you are working with, but how about the person behind you in development. If someone is not used to what columns come from what table, it can be ambiguous to follow, especially out here at S/O. By always qualifying by using the table reference and field, or alias reference and field, its much easier to follow.

在编写查询时，您知道自己在使用什么，但在开发过程中，您背后的人是怎样的。如果有人不习惯哪些列来自哪个表，那么可能会有些含糊不清，特别是在S / O处。通过始终使用表引用和字段或别名引用和字段进行限定，它更容易遵循。

select
      SomeField,
      AnotherField
   from
      OneOfMyTables
         Join SecondTable
            on SomeID = SecondID

compare that to

比较一下

select
      T1.SomeField,
      T2.AnotherField
   from
      OneOfMyTables T1
         JOIN SecondTable T2
            on T1.SomeID = T2.SecondID

In these two scenarios, which would you prefer reading... Notice, I've simplified the query using shorter aliases "T1" and "T2", but they could be anything, even an acronym or abbreviated alias of the table names... "oomt" (one of my tables) and "st" (second table). Or, as something super long as has been in other posts...

在这两种情况下，您更喜欢阅读...注意，我使用较短的别名“T1”和“T2”简化了查询，但它们可以是任何内容，甚至是表名的缩写或缩写别名。。“oomt”（我的一张桌子）和“st”（第二张桌子）。或者，像其他帖子一样超长的东西......

Select * from ContractPurchaseOffice_AgencyLookupTable

vs 

Select * from ContractPurchaseOffice_AgencyLookupTable  AgencyLkup

If you had to keep qualifying joins, or field columns, which would you prefer looking at.

如果你不得不保留符合条件的联接或字段列，那么你更喜欢看。

Hope this clarifies your question.

希望这能澄清你的问题。

#1