SQL - 为什么没有办法选择除此列之外的所有内容
我的一般问题是，我想从左连接中选择所有列，并且不需要知道连接两个表的 ID。我知道没有必要选择全部，但是由于您需要除 ids 之外的所有字段，为什么没有更短的方法： SELECT * except "this column"，我觉得操作时间应该通过这样做来缩短相反的方式？
T1:
aID,
c1,
c2,
c3,
c4
t2:
aID,
c1,
c2,
c3,
c4
Select * from t1 left join t2 on t1.aid = t2.aid
result: t1:aid, c1, c2, c3, c4, aid, c1, c2 ,c3 ,c4
我不想选择我想要的每一列，而是只想选择除“aid”之外的所有列。
My general question is that I want to select all columns from a left join, and I don't need to know the ID that joins the two tables. I know that it is unnecessary to select all, but since you need all the fields except the ids, why isn't there a shorter way to: SELECT * except "this column", i feel like the action time should be shorter by doing the reverse way?
T1:
aID,
c1,
c2,
c3,
c4
t2:
aID,
c1,
c2,
c3,
c4
Select * from t1 left join t2 on t1.aid = t2.aid
result: t1:aid, c1, c2, c3, c4, aid, c1, c2 ,c3 ,c4
instead of selecting each of the columns that I want, I just want to select all except "aid".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
SELECT *是一种简写形式，对于快速构建查询原型很有用，但强烈不建议用于生产代码（EXISTS表达式之外） - 如果有任何架构更改，则任何消耗查询结果的内容都会出现意外的列。同样，您请求的表格也会有同样的问题。一旦您适当地调整了查询（正确连接等），您应该返回到 SELECT 子句并显式列出您实际想要检索的那些列。检索超出您需要的列（包括意外的新列）可能会导致 SQL Server 不得不使用效率较低的计划，或者检索使用者永远不会使用的大量数据。
SELECT *is a shorthand that's useful for quickly prototyping a query, but is strongly not recommended for production code (outside ofEXISTSexpressions) - if there are any schema changes, then whatever is consuming the results of the query will get unexpected columns appearing. Similarly, your requested form would have the same issue.Once you've shaped your query appropriately (gotten joins correct, etc), you should go back to your SELECT clause and explicitly list those columns you actually want to retrieve. Retrieving more columns than you need (including unexpected new ones) may cause SQL Server to have to use a less efficient plan, or retrieve massive amounts of data that the consumer is never going to use.
我不太确定这是否真的可能。您可能想查看此实例中的动态查询，其中两个连接表中的所有列（外键除外）都将包含在查询中。
您甚至可以考虑编写一个接受 @Table1、@Table2、@PK、@FK 参数的过程，然后根据提供的参数动态构建查询。
I'm not too sure that's really possible. You might want to look at a dynamic query in this instance, where all columns from the two joining tables will be included in the query, except the foreign key.
You can even consider writing a Procedure of sorts that accepts parameters for @Table1, @Table2, @PK, @FK and then build up the query dynamically based on the parameters supplied.
为什么你关心 id 列是否返回？
另外，是的，这可能是 SQL 设计中的一个疏忽。
Why do you care if the id column is returned or not?
Also, yes this might be a bit of an oversight in the SQL design.
出于同样的原因，SELECT * 也不是一个好主意。在查询中使用 SELECT * 往好了说是很烦人，往坏了说是危险的。如果您正在查询的表或数据源发生更改，那么它可能会出现意外的行为。
For the same reason that SELECT * is not a good idea. It can be annoying at best and dangerous at worst to use SELECT * in your queries. If the table or datasource you are querying changes then it can have unexpected behaviour.
听起来您正在寻找的是
NATURAL JOIN。Sounds like what you are looking for is
NATURAL JOIN.