何时使用 SQL 表别名
我很好奇人们如何使用表别名。 我工作的其他开发人员总是使用表别名，并且总是使用 a、b、c 等别名。
下面是一个例子：
SELECT a.TripNum, b.SegmentNum, b.StopNum, b.ArrivalTime
FROM Trip a, Segment b
WHERE a.TripNum = b.TripNum
我不同意他们的观点，并认为表别名应该更加谨慎地使用。
我认为当在查询中两次包含同一个表时，或者当表名很长并且在查询中使用较短的名称将使查询更易于阅读时，应该使用它们。
我还认为别名应该是一个描述性名称而不仅仅是一个字母。 在上面的示例中，如果我觉得需要使用 1 个字母的表别名，我会使用 t 表示 Trip 表，使用 s 表示段表。
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（17）
使用表别名有两个原因。
首先是化妆品。 使用表别名时，这些语句更容易编写，并且可能也更容易阅读。
第二个更实质性。 如果一个表在 FROM 子句中出现多次，则需要表别名以保持它们不同。 当表包含引用同一表的主键的外键时，自连接很常见。
两个示例：员工表，其中包含引用主管的员工 ID 的主管 ID 列。
二是零件爆炸。 通常，这是在具有三列的单独表中实现的：ComponentPartID、AssemblyPartID 和 Quantity。 在这种情况下，不会有任何自联接，但该表和对零件表的两个不同引用之间通常会存在三向联接。
这是一个值得养成的好习惯。
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.
Two examples: an employees table that contains a supervisorID column that references the employeeID of the supervisor.
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.
我用它们来节省打字时间。 不过，我总是使用与功能类似的字母。 因此，在您的示例中，我会输入：
这对我来说更容易阅读。
I use them to save typing. However, I always use letters similar to the function. So, in your example, I would type:
That just makes it easier to read, for me.
作为一般规则我总是使用它们，因为我的存储过程中通常会发生多个联接。 使用 CodeSmith 等代码生成工具自动为您生成别名也变得更加容易。
我尽量避免使用像 a & 这样的单个字母。 b，因为我可能有多个以字母 a 或 b 开头的表。 我采用更长的方法，将引用的外键与别名表连接起来，例如 CustomerContact ...这将是连接到联系人表时客户表的别名。
我不介意更长名称的另一个原因是我的大多数存储过程都是通过 CodeSmith 代码生成的。 我不介意手动输入我可能需要自己构建的少数。
使用当前的示例，我会执行以下操作：
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.
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.
The other reason I don't mind longer name, is due to most of my stored procedures are being generated via code CodeSmith. I don't mind hand typing the few that I may have to build myself.
Using the current example, I would do something like:
我总是使用它，原因是：
考虑以下示例：
现在，想象一下几个月后，您决定将名为“col1”的列添加到 tab2。 数据库将默默地允许您执行此操作，但由于 tab1.col1 和 tab2.col1 之间的歧义，应用程序在执行上述查询时会中断。
但是，我同意你的命名：a、b、c 很好，但在你的示例中 t 和 s 会更好。 当我多次使用同一张表时，我会使用 t1、t2、... 或 s1、s2、s3...
I use it always, reasons:
Consider this example:
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.
But, I agree with you on the naming: a, b, c is fine, but t and s would 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...
我可以对已经有几年历史的争论进行补充吗？
还有一个没有人提及的原因。 某些数据库中的 SQL 解析器使用别名可以更好地工作。 我不记得 Oracle 在以后的版本中是否改变了这一点，但是当涉及到别名时，它会查找数据库中的列并记住它们。 当涉及到表名时，即使在语句中已经遇到过它，它也会重新检查数据库中的列。 因此，使用别名可以加快解析速度，尤其是长 SQL 语句。 我确信有人知道情况是否仍然如此，其他数据库是否在解析时执行此操作，以及如果它发生了变化，何时它发生了变化。
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, when it changed.
使用全名会使阅读变得更加困难，特别是对于较大的查询或 Order/Product/OrderProduct 场景，
我会使用 t 和 s。 或 o/p/op
如果您使用 SCHEMABINDING，则无论如何列都必须被限定
如果您将列添加到基表，则限定会减少查询中出现重复的机会（例如“注释”列）
因此资格，始终使用别名是有意义的。
使用 a 和 b 是对奇怪标准的盲目服从。
Using the full name makes it harder to read, especially for larger queries or the Order/Product/OrderProduct scenari0
I'd use t and s. Or o/p/op
If you use SCHEMABINDING then columns must be qualified anyway
If you add a column to a base table, then the qualification reduces the chance of a duplicate in the query (for example a "Comment" column)
Because of this qualification, it makes sense to always use aliases.
Using a and b is blind obedience to a bizarre standard.
在简单查询中我不使用别名。 在带有多个表的查询中，我总是使用它们，因为：
别名是 2 个或更多大写字母，是表名的快捷方式，如果可能的话
与他人的关系
表）
重写（我的表名很长，并且根据它们所扮演的角色有前缀），
不是例如：
所以我写的
In simple queries I do not use aliases. In queries whit multiple tables I always use them because:
aliases are 2 or more capital letters that is a shortcut for the table name and if possible
a relationship to other
tables)
rewriting (my table names are long and have prefixes depending on role they pose)
so instead of for example:
I write:
撇开我们必须使用别名的情况不谈，我得出的结论是最好避免使用表别名。
随着时间的推移，您会逐渐适应数据库，并且会了解它的表和列（即使名称不好）。 因此，您将看到重复的连接、列、谓词等模式。这可以加快完全解释不熟悉的查询所需的时间，并且当您必须修复 2000 中的关键错误时，这会大有帮助。周五下午 4 点运行存储过程。 换句话说，别名使维护变得更加困难。
如果表有别名，我们必须在头脑中解码别名，然后才能确定查询或块的意图。 不断引用
FROM子句来解码别名既耗时又麻烦。 表名总是指同一个事物。 另外，长表名对于现代 IDE 和智能感知来说并不是什么大问题。别名也因人而异。 在一个查询中，
customer表的别名为c，而在另一查询中，cost表的别名为c。 解决方案是实施标准别名实践。 那么为什么不直接重命名表，或者如果不能的话，将其包装在视图中，这样您就不必考虑它了。 大多数数据库都为您提供了在数据上构建有用的抽象层的工具。Leaving aside situations where we must alias, I have concluded that Table Aliases are best avoided.
Over time you become acclimatized to a Database and will get to know its tables and columns (even if poorly named). As such, you will come to see repeated patterns of joins, columns, predicates, etc. This can accelerate the time it takes to fully interpret an unfamiliar query and goes a long way when you've got to fix a critical bug in a 2000 line stored procedure at 4pm on Friday. Another way of putting it, Aliases make maintenance harder.
If tables are aliased, we must mentally decode the alias, before we can workout the intent of the query or block. Constantly referring back to the
FROMclause to decode an alias is time consuming and cumbersome. A table name always refers to the same thing. Plus long table names aren't a big deal with modern IDEs and intellisense.Aliases also vary person to person. The
customertable is aliasedcin one query, while in another query thecosttable is aliasedc. A solution to this is to implement standard aliasing practices. Well why not just rename the table, or if you can't, wrap it in a view so you never have to think about it. Most Databases give you tools to build useful layers of abstraction over your data.我觉得你应该尽可能多地使用它们，但我确实同意 t & s 比 a & 更好地代表实体 b.
与其他一切一样，这可以归结为偏好。 我喜欢当每个开发人员以相同的方式使用别名时，您可以依赖遵循相同约定的存储过程。
去说服你的同事与你达成共识，否则这一切都是毫无价值的。 另一种方法是您可以将 Zebra 表作为第一个表，并将其别名为 a。 那会很可爱。
I feel that you should use them as often as possible but I do agree that t & s represent the entities better than a & b.
This boils down to, like everything else, preferences. I like that you can depend on your stored procedures following the same conventions when each developer uses the alias in the same manner.
Go convince your coworkers to get on the same page as you or this is all worthless. The alternative is you could have a table Zebra as first table and alias it as a. That would just be cute.
我仅在需要区分字段来自哪个表时才使用它们
在上面的示例中，两个表都有一个 InventoryTypeId 字段，但其他字段名称是唯一的。
始终使用表的缩写作为名称，以便代码更有意义 - 询问其他开发人员是否将其局部变量命名为 A、B、C 等！
唯一的例外是在极少数情况下，SQL 语法需要表别名但没有引用它，例如
在上面，SQL 语法需要子选择的表别名，但它没有在任何地方引用，所以我变得懒惰并且使用 X 或类似的东西。
i only use them when they are necessary to distinguish which table a field is coming from
In the example above both tables have an InventoryTypeId field, but the other field names are unique.
Always use an abbreviation for the table as the name so that the code makes more sense - ask your other developers if they name their local variables A, B, C, etc!
The only exception is in the rare cases where the SQL syntax requires a table alias but it isn't referenced, e.g.
In the above, SQL syntax requires the table alias for the subselect, but it isn't referenced anywhere so I get lazy and use X or something like that.
我觉得这只不过是一种偏好。 如上所述，别名可以节省输入，尤其是对于长表/视图名称。
I find it nothing more than a preference. As mentioned above, aliases save typing, especially with long table/view names.
我学到的一件事是，尤其是对于复杂的查询； 如果您使用别名作为每个字段引用的限定符，那么六个月后的故障排除会简单得多。 那么您就不会试图记住该字段来自哪个表。
我们往往有一些长得离谱的表名，所以我发现如果表有别名会更容易阅读。 当然，如果您使用派生表或自连接，则必须这样做，因此养成这个习惯是个好主意。 我发现我们大多数开发人员最终在所有 sps 中为每个表使用相同的别名，因此大多数时候任何阅读它的人都会立即知道 pug 或 mmh 的别名是什么。
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.
我总是使用它们。 我以前只在涉及一张表的查询中使用它们，但后来我意识到 a) 只涉及一张表的查询很少见，b) 只涉及一张表的查询很少会长期保持这种状态。 所以我总是从一开始就把它们放进去，这样我（或其他人）就不必在以后重新安装它们。 哦，顺便说一句：根据 SQL-92 标准，我将它们称为“相关名称”:)
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 :)
表别名应该是四件事：
例如，如果您有名为 service_request、service_provider、user 和affiliate（以及许多其他）的表，一个好的做法是将这些表别名为“sr”、“sp” 、“u”和“a”，并在每个可能的查询中执行此操作。 如果这些别名与您的组织使用的首字母缩略词一致（通常情况下），这将特别方便。 因此，如果“SR”和“SP”分别是服务请求和服务提供商的可接受术语，则上面的别名带有双重有效负载，直观地代表表及其代表的业务对象。
这个系统的明显缺陷首先是，对于包含大量“单词”的表名来说可能会很尴尬，例如 a_long_multi_word_table_name ，它会别名为 almwtn 或其他东西，并且很可能您最终会得到命名为它们缩写相同的表。 第一个缺陷可以按照您喜欢的方式处理，例如采用最后 3 或 4 个字母，或者您认为最具代表性、最独特或最容易键入的子集。 我在实践中发现的第二个并不像看起来那么麻烦，也许只是运气好。 您还可以执行诸如取表中“单词”的第二个字母之类的操作，例如将 account_transaction 别名为“atr”而不是“at”，以避免与 account_type 冲突。
当然，无论您是否使用上述方法，别名都应该很短，因为您将非常频繁地键入它们，并且应该始终使用它们，因为一旦您针对单个表编写了查询并省略了别名，它就您稍后将不可避免地需要在具有重复列名的第二个表中进行编辑。
Tables aliases should be four things:
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.
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.
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.
上面的帖子中有很多关于何时以及为何为表名添加别名的好主意。 其他人没有提到的是，它也有利于帮助维护人员理解表的范围。 在我们公司，我们不允许创建视图。 （感谢 DBA。）因此，我们的一些查询变得很大，甚至超过了 Crystal Reports 中 SQL 命令的 50,000 个字符的限制。 当一个查询将其表别名为 a、b、c，并且该查询的子查询执行相同的操作，并且该查询中的多个子查询均使用相同的别名时，人们很容易错误地读取正在读取的查询级别。 当足够的时间过去后，这甚至会使原始开发人员感到困惑。 在查询的每个级别中使用唯一的别名可以使查询更容易阅读，因为范围仍然清晰。
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.
总是。 让它成为一种习惯。
Always. Make it a habit.
因为我总是完全限定我的表，所以当涉及 JOINed 表时，我使用别名为要选择的字段提供较短的名称。 我认为这使我的代码更容易遵循。
如果查询仅处理一个源 - 没有 JOIN - 那么我不使用别名。
只是个人喜好问题。
Because I always fully qualify my tables, I use aliases to provide a shorter name for the fields being SELECTed when JOINed tables are involved. I think it makes my code easier to follow.
If the query deals with only one source - no JOINs - then I don't use an alias.
Just a matter of personal preference.