何时使用SQL表别名 [英] When to use SQL Table Alias

问题描述

我很想知道人们如何使用表别名。其他我在其中工作的开发者总是使用表别名，并且总是使用a，b，c等的别名。

I'm curious to know how people are using table aliases. The other developers where I work always use table aliases, and always use the alias of a, b, c, etc.

这里有一个例子：

SELECT a.TripNum, b.SegmentNum, b.StopNum, b.ArrivalTime
FROM Trip a, Segment b
WHERE a.TripNum = b.TripNum

我不同意他们，认为表别名应该更谨慎。

I disagree with them, and think table aliases should be use more sparingly.

我认为当在查询中包含同一个表两次时，或者当表名很长并且在查询中使用较短的名称时，应该使用它们。查询更容易阅读。

I think they should be used when including the same table twice in a query, or when the table name is very long and using a shorter name in the query will make the query easier to read.

我还认为别名应该是一个描述性名称，而不是一个字母。在上面的例子中，如果我觉得我需要使用1个字母的表别名，我将使用t表示Trip表，s表示段表。

I also think the alias should be a descriptive name rather than just a letter. In the above example, if I felt I needed to use 1 letter table alias I would use t for the Trip table and s for the segment table.

推荐答案

使用表别名有两个原因。

There are two reasons for using table aliases.

第一个是化妆品。这些语句更容易编写，并且在使用表别名时也更容易阅读。

The first is cosmetic. The statements are easier to write, and perhaps also easier to read when table aliases are used.

第二个更具实质性。如果表在FROM子句中多次出现，则需要表别名，以保持它们不同。自动连接在表包含引用同一表的主键的外键的情况下是常见的。

The second is more substantive. If a table appears more than once in the FROM clause, you need table aliases in order to keep them distinct. Self joins are common in cases where a table contains a foreign key that references the primary key of the same table.

两个示例：employees表包含supervisorID列引用了主管的employeeID。

Two examples: an employees table that contains a supervisorID column that references the employeeID of the supervisor.

第二个是零件爆炸。通常，这在具有三个列的单独表中实现：ComponentPartID，AssemblyPartID和Quantity。在这种情况下，不会有任何自联接，但是在这个表和对零件表的两个不同引用之间通常会有三向联接。

The second is a parts explosion. Often, this is implemented in a separate table with three columns: ComponentPartID, AssemblyPartID, and Quantity. In this case, there won't be any self joins, but there will often be a three way join between this table and two different references to the table of Parts.

这是一个好习惯进入。

It's a good habit to get into.

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