在SQL中何时需要为表名赋予别名? [英] When is it required to give a table name an alias in SQL?

问题描述

当我用多个JOIN进行查询时，我注意到我的查询无法工作，除非我为其中一个表名指定了别名.

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.

下面是一个简单的例子来说明这一点:

Here's a simple example to explain the point:

此无效有效:

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

此确实:

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

有人可以解释吗?

推荐答案

您正在使用同一表，在查询中购买了两次.您需要使用其他名称来区分它们.

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

您需要提供一个别名:

• When the same table name is referenced multiple times

想象一下，两个人的约翰·道伊(John Doe)完全一样.如果您致电John，双方都会回复您的电话.您不能给两个人使用相同的名字，并假设他们会知道您要呼叫的人.同样，当您给相同的结果集命名为完全相同时，SQL无法识别要从中获取值的结果集.您需要使用不同的名称来区分结果集，以免使SQL引擎感到困惑.

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.

脚本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

如果某人没有名字，您可以呼叫他们，并且由于您无法呼叫该人，因此他们不会回复您.同样，当您生成派生表输出或子查询输出时，SQL引擎对此并不了解，也不会调用.因此，您需要给派生的输出命名，以便SQL引擎可以适当地处理派生的输出.

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.

脚本2 : t1 是此处的别名.

SELECT col1
FROM
(
    SELECT col1
    FROM   table1
) t1

这篇关于在SQL中何时需要为表名赋予别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！