何时使用 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.

两个示例:一个员工表，其中包含引用主管的员工 ID 的主管 ID 列.

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

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

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.

养成好习惯.

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