在 sql 连接或嵌套查询中使用别名总是一个好习惯吗？
使用 -
Select E.Id,D.DeptName from Employee E join Dept D on E.DeptId=D.Id
而不是 -
Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Id
始终是最佳实践吗？除了查询的可读性和减少长度之外，使用别名还有什么好处？当我咨询我们的数据库专家时，他说如果有时没有别名，查询可能会中断...我完全不明白...如果有人愿意，我将不胜感激分享他们的想法以及应遵循的最佳实践是什么......非常感谢。
Is it always a best practice to use -
Select E.Id,D.DeptName from Employee E join Dept D on E.DeptId=D.Id
instead of -
Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Id
Other than readability and reducing length of the query what are benefits of using aliases ? When I consulted with our Database expert he says query may break if there are no aliases at times...which I dont understand completely...I would appreciate if anyone would share their thoughts and what are the best practices to be followed...Thanks a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
当提到破坏事物时，您可能会混淆“需要使用表前缀”和“需要使用别名”。
如果不使用表前缀，添加联接后查询确实更有可能中断；当您的原始表和新添加的表共享同名列时。因此，为了将来的维护，对于查询中的所有列始终使用表前缀是一个好主意。
但是，这个问题可以通过在列前面使用任何表前缀来解决，无论是真实的表名还是别名。
当您两次使用同一个表时，需要别名（而不是实际的表名称）。
根据维护大量复杂 SQL 的大量经验，我必须说我的观点与您的观点 100% 相反。
也就是说，使用短的（尤其是 1 个字母的）表别名会使代码更难阅读/维护。
当您在生产紧急情况下凌晨 2 点调试一段具有复杂连接的长 SQL 时，来回查看上面 10-15 行以查看哪个表与别名“e”匹配要困难得多。
这一点有两个例外
当查询的业务逻辑使用表的目的与表名称非常不同时。
当表名由于您无法控制的情况而变得不合理长且不合理时 - 那么别名仍然应该是可读且合乎逻辑的。例如，“
EmployeeTableIndexedByUIDSourcedFromHR”可以而且通常应该是“Employee”的别名，但不能是“E”此外，为了避免字符串过长，如果您使用换行符和对齐方式格式化查询，则会非常有帮助：
Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Idvs
You are probably confusing "needing to use a table prefix" and "needing to use an ALIAS" when referring to breaking things.
The query might indeed be more likely to break after adding a join if you don't use a table prefix; when your original table and a newly added table share a column with the same name. So for the sake of future maintenance, always using a table prefix is a GOOD idea for all columns in the query.
However, this problem is solved by using ANY table prefix in front of columns, whether real table name or alias name.
The alias is needed (as opposed to actual table name) when you use the same table twice.
From a lot of experience with maintaining lots of complex SQL, I must say that my view is 100% opposite of yours.
Namely, using a short - especially 1-letter - table alias makes for a HARDER to read/maintain code.
When you're debugging a long piece of SQL with complex joints at 2am in the morning during production emergency, looking back/forth 10-15 lines above to see what table matches alias "e" is MUCH harder.
This point has 2 exceptions
when the business logic of the query uses the table for a purpose which is VERY distinct from the table name.
when the table name is unreasonably long and unreasonable due to circumstances beyond your control - and then the alias should still be something readable and logical. E.g. "
EmployeeTableIndexedByUIDSourcedFromHR" can and usually should be aliases as "Employee", but not as "E"Also, to avoid having overly long strings, it helps hreatly if you format your queries using newlines and alignment:
Select Employee.Id,Dept.DeptName from Employee join Dept on Employee.DeptId=Dept.Idvs
当您两次引用同一个表时，必须使用别名。
除此之外，我想不出任何技术原因。
When you reference the same table twice, you have to use an alias.
Other than that, there isn't any technical reason I can think of.
我几乎总是这样做，因为我讨厌输入完整的表名，如果不这样做，最终可能会得到不明确的列名。只是不要使用无意义的别名，例如 t1、t2 等。
I almost always do it because I hate to type out the full table name, and if you don't do it you can end up with ambiguous column names. Just don't use meaningless aliases such as t1, t2, etc.
最易读的方法是使用完全限定的名称来明确；我和你的数据库专家都同意这一点。
但是当你开发它们时......别名是你的朋友，毫无疑问。
The most readable way is to be explicit with a fully qualified name; on that your DB expert and I agree.
But when you're developing them... aliases are your friend, hands down.
在大多数情况下，我更喜欢使用完全限定的表名。如果单独数据库中的两个表之间需要内部联接，我通常只对表使用别名，只是为了使其更具可读性。
但是，我建议为您的字段使用别名，以防止调用下游应用程序所需的更改。
I prefer to use the fully qualified table names in most cases. I usually only use an alias name for tables if a inner join is required between two tables in separate databases, just to make it a little more readable.
However, I would recommend using alias names for your fields in order to prevent changes required to calling applications down stream.
我对 SQL 还算陌生，但我是一名多年的专业程序员（C、C++、Python）。
在我的新工作中，我真的很“震惊”，因为每个人都在使用短表别名。我到处都遇到了“a”、“b”、“c”和“t1”、“t2”、“t3”别名。
使用短别名 100% 违背我对任何专业编码/设计规则的了解。对象、属性和函数的名称应该是描述性的。短别名永远不具有描述性。
对我来说阅读带有别名的 SQL 是非常困难的。这些 SQL 的长度可能约为 100 或 500 行。那太疯狂了！
此外，SQL 是一种奇怪的语言：别名是在定义之前使用的。这使得阅读变得更加困难。
I am quiet new to SQL, but I am a professional programmer for many years (C, C++, python).
In my new job I was really "shocked", that everybody was using short table aliases. I encountered "a", "b", "c" and "t1", "t2", "t3" aliases everywhere.
Using short aliases is 100% contrary to my knowledge about any professional coding/design rules. Names of objects, properties and functions should be descriptive. Short aliases are never descriptive.
Its very hard for me to read SQLs with aliases. Those SQLs can be like 100 or 500 lines long. Thats just insane!
In addition SQL is a weired language: The aliases are used before they are defined. That makes it even harder to read.