何时使用 SQL 表别名

When to use SQL Table Alias

提问by Rossini

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.

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

Here's an example:

下面是一个例子：

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.

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

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.

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

回答by Walter Mitty

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.

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

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.

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

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

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

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.

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

It's a good habit to get into.

入门是个好习惯。

回答by BoltBait

I use them to save typing. However, I always use letters similar to the function. So, in your example, I would type:

我用它们来节省打字。但是，我总是使用类似于函数的字母。所以，在你的例子中，我会输入：

SELECT t.TripNum, s.SegmentNum, s.StopNum, s.ArrivalTime 
FROM Trip t, Segment s 
WHERE t.TripNum = s.TripNum

That just makes it easier to read, for me.

对我来说，这只是让阅读更容易。

回答by mattruma

As a general rule I always use them, as there are usually multiple joins going on in my stored procedures. It also makes it easier when using code generation tools like CodeSmith to have it generate the alias name automatically for you.

作为一般规则，我总是使用它们，因为在我的存储过程中通常有多个连接。当使用 CodeSmith 之类的代码生成工具让它自动为您生成别名时，它也变得更加容易。

I try to stay away from single letters like a & b, as I may have multiple tables that start with the letter a or b. I go with a longer approach, the concatenation of the referenced foreign key with the alias table, for example CustomerContact ... this would be the alias for the Customer table when joining to a Contact table.

我尽量远离像 a & b 这样的单个字母，因为我可能有多个以字母 a 或 b 开头的表格。我采用更长的方法，将引用的外键与别名表串联，例如 CustomerContact ...这将是加入 Contact 表时 Customer 表的别名。

The other reason I don't mind longername, is due to most of my stored procedures are being generated via code CodeSmith. I don't mind hand typing the fewthat I may have to build myself.

我不介意更长的名称的另一个原因是我的大部分存储过程都是通过代码 CodeSmith 生成的。我不介意手写一些我可能需要自己构建的东西。

Using the current example, I would do something like:

使用当前的例子，我会做这样的事情：

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

回答by MJB

Can I add to a debate that is already several years old?

我可以添加到已经有几年的辩论吗？

There is another reason that no one has mentioned. The SQL parser in certain databases works better with an alias. I cannot recall if Oracle changed this in later versions, but when it came to an alias, it looked up the columns in the database and remembered them. When it came to a table name, even if it was already encountered in the statement, it re-checked the database for the columns. So using an alias allowed for faster parsing, especially of long SQL statements. I am sure someone knows if this is still the case, if other databases do this at parse time, and if it changed, whenit changed.

还有一个没有人提到的原因。某些数据库中的 SQL 解析器使用别名效果更好。我不记得 Oracle 在以后的版本中是否改变了这一点，但是当涉及到别名时，它会查找数据库中的列并记住它们。当涉及到一个表名时，即使它已经在语句中遇到过，它也会重新检查数据库中的列。因此，使用别名可以加快解析速度，尤其是长 SQL 语句。我敢肯定有人知道这是否仍然存在，其他数据库是否在解析时执行此操作，以及是否已更改，何时更改。

回答by Erv

In simple queries I do not use aliases. In queries whit multiple tables I always use them because:

在简单查询中，我不使用别名。在查询多个表时，我总是使用它们，因为：

• they make queries more readable (my aliases are 2 or more capital letters that is a shortcut for the table name and if possible a relationship to other tables)
• they allow faster developing and rewriting (my table names are long and have prefixes depending on role they pose)

• 它们使查询更具可读性（我的别名是 2 个或更多大写字母，这是表名的快捷方式，如果可能的话，与其他表的关系）
• 它们允许更快的开发和重写（我的表名很长，并且根据它们所扮演的角色有前缀）

so instead of for example:

所以而不是例如：

SELECT SUM(a.VALUE) 
       FROM Domesticvalues a, Foreignvalues b 
       WHERE a.Value>b.Value
       AND a.Something ...

I write:

我写的：

select SUM(DVAL.Value) 
       from DomesticValues DVAL, ForeignValues FVAL 
       where DVAL.Value > FVAL.Value
       and   DVAL.Something ...

回答by Milan Babu?kov

I use it always, reasons:

我总是使用它，原因：

• leaving full tables names in statements makes them hard to read, plus you cannot have a same table twice
• not using anything is a very bad idea, because later you could add some field to one of the tables that is already present in some other table

• 在语句中留下完整的表名会使它们难以阅读，而且你不能有两次同一个表
• 不使用任何东西是一个非常糟糕的主意，因为稍后您可以向其他表中已经存在的表中添加一些字段

Consider this example:

考虑这个例子：

select col1, col2
from tab1
join tab2 on tab1.col3 = tab2.col3

Now, imagine a few months later, you decide to add column named 'col1' to tab2. Database will silently allow you to do that, but applications would break when executing the above query because of ambiguity between tab1.col1 and tab2.col1.

现在，想象几个月后，您决定将名为“col1”的列添加到 tab2。数据库将默默地允许您这样做，但由于 tab1.col1 和 tab2.col1 之间的歧义，应用程序会在执行上述查询时中断。

But, I agree with you on the naming: a, b, c is fine, but tand swould be much better in your example. And when I have the same table more than once, I would use t1, t2, ... or s1, s2, s3...

但是，我同意您的命名：a、b、c 很好，但在您的示例中t和s会好得多。当我多次使用同一张表时，我会使用 t1, t2, ... 或 s1, s2, s3 ...

回答by HLGEM

One thing I've learned is that especially with complex queries; it is far simpler to troubleshoot six months later if you use the alias as a qualifier for every field reference. Then you aren't trying to remember which table that field came from.

我学到的一件事是，特别是对于复杂的查询；如果您使用别名作为每个字段引用的限定符，则在六个月后进行故障排除会简单得多。那么您就不会试图记住该字段来自哪个表。

We tend to have some ridiculously long table names, so I find it easier to read if the tables are aliased. And of course you must do it if you are using a derived table or a self join, so being in the habit is a good idea. I find most of our developers end up using the same alias for each table in all their sps,so most of the time anyone reading it will immediately know what pug is the alias for or mmh.

我们往往有一些长得可笑的表名，所以我发现如果表别名更容易阅读。当然，如果您使用派生表或自连接，则必须这样做，因此养成习惯是个好主意。我发现我们的大多数开发人员最终在他们所有的 sps 中为每个表使用相同的别名，所以大多数时候，任何阅读它的人都会立即知道 pug 是什么或 mmh 的别名。

回答by onedaywhen

I always use them. I formerly only used them in queries involving just one table but then I realized a) queries involving just one table are rare, and b) queries involving just one table rarely stay that way for long. So I always put them in from the start so that I (or someone else) won't have to retro fit them later. Oh and BTW: I call them "correlation names", as per the SQL-92 Standard :)

我总是使用它们。我以前只在只涉及一张表的查询中使用它们，但后来我意识到 a) 只涉及一张表的查询很少见，b) 只涉及一张表的查询很少会长时间保持这种状态。所以我总是从一开始就把它们放进去，这样我（或其他人）以后就不必再改造它们了。哦，顺便说一句：根据 SQL-92 标准，我称它们为“相关名称”:)

回答by Noah Yetter

Tables aliases should be four things:

表别名应该是四件事：

1. Short
2. Meaningful
3. Always used
4. Used consistently

1. 短的
2. 有意义
3. 一直使用
4. 一直使用

For example if you had tables named service_request, service_provider, user, and affiliate (among many others) a good practice would be to alias those tables as "sr", "sp", "u", and "a", and do so in every query possible. This is especially convenient if, as is often the case, these aliases coincide with acronyms used by your organization. So if "SR" and "SP" are the accepted terms for Service Request and Service Provider respectively, the aliases above carry a double payload of intuitively standing in for both the table and the business object it represents.

例如，如果您有名为 service_request、service_provider、user 和affiliate（以及许多其他表）的表，一个好的做法是将这些表别名为“sr”、“sp”、“u”和“a”，然后这样做在每个可能的查询中。如果通常情况下这些别名与您的组织使用的首字母缩写词一致，这将特别方便。因此，如果“SR”和“SP”分别是服务请求和服务提供者接受的术语，那么上面的别名带有双重有效载荷，即直观地代表表和它所代表的业务对象。

The obvious flaws with this system are first that it can be awkward for table names with lots of "words" e.g. a_long_multi_word_table_name which would alias to almwtn or something, and that it's likely you'll end up with tables named such that they abbreviate the same. The first flaw can be dealt with however you like, such as by taking the last 3 or 4 letters, or whichever subset you feel is most representative, most unique, or easiest to type. The second I've found in practice isn't as troublesome as it might seem, perhaps just by luck. You can also do things like take the second letter of a "word" in the table as well, such as aliasing account_transaction to "atr" instead of "at" to avoid conflicting with account_type.

该系统的明显缺陷首先是对于包含大量“单词”的表名来说可能会很尴尬，例如 a_long_multi_word_table_name 将别名为 almwtn 或其他东西，并且很可能你最终会得到命名为它们缩写相同的表. 第一个缺陷可以随心所欲地处理，例如取最后 3 或 4 个字母，或者您认为最具代表性、最独特或最容易输入的任何子集。我在实践中发现的第二个并不像看起来那么麻烦，也许只是运气。您还可以执行一些操作，例如取表中“单词”的第二个字母，例如将 account_transaction 别名为“atr”而不是“at”以避免与 account_type 冲突。

Of course whether you use the above approach or not, aliases should be short because you'll be typing them very very frequently, and they should always be used because once you've written a query against a single table and omitted the alias, it's inevitable that you'll later need to edit in a second table with duplicate column names.

当然，无论您是否使用上述方法，别名都应该很短，因为您将非常频繁地输入它们，并且应该始终使用它们，因为一旦您针对单个表编写了查询并省略了别名，它就是不可避免的是，您稍后需要在具有重复列名的第二个表中进行编辑。

回答by Benjamin Ford

There are many good ideas in the posts above about when and why to alias table names. What no one else has mentioned is that it is also beneficial in helping a maintainer understand the scope of tables. At our company we are not allowed to create views. (Thank the DBA.) So, some of our queries become large, even exceeding the 50,000 character limit of a SQL command in Crystal Reports. When a query aliases its tables as a, b, c, and a subquery of that does the same, and multiple subqueries in that one each use the same aliases, it is easy for one to mistake what level of the query is being read. This can even confuse the original developer when enough time has passed. Using unique aliases within each level of a query makes it easier to read because the scope remains clear.

上面的帖子中有很多关于何时以及为什么要别名表名的好主意。其他人没有提到的是，它也有助于维护人员了解表的范围。在我们公司，我们不允许创建视图。（感谢 DBA。）因此，我们的一些查询变得很大，甚至超过了 Crystal Reports 中 SQL 命令的 50,000 个字符限制。当查询将其表别名为 a、b、c 并且其子查询执行相同操作，并且该查询中的多个子查询均使用相同的别名时，很容易弄错正在读取查询的哪个级别。当足够的时间过去时，这甚至会使原始开发人员感到困惑。在查询的每个级别中使用唯一的别名可以更容易阅读，因为范围仍然清晰。