SQL-除了本专栏之外，为什么没有其他方法可以全选 [英] SQL - Why isn't there a way to SELECT ALL, except this column

问题描述

我的一般问题是，我想从左联接中选择所有列，而我不需要知道联接两个表的ID.我知道没有必要全部选择，但是由于您需要除id之外的所有字段，为什么没有一种更短的方法:SELECT *除了"this column"，我觉得这样做的操作时间应该更短相反的方式?

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".

推荐答案

SELECT *是用于快速建立查询原型的一种简写，但是强烈建议不要将其用于生产代码(EXISTS表达式之外)-如果存在的话如果进行任何模式更改，那么无论使用哪种查询结果，都将出现意外列.同样，您要求的表格也会有同样的问题.

SELECT * is a shorthand that's useful for quickly prototyping a query, but is strongly not recommended for production code (outside of EXISTS expressions) - 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.

一旦适当地调整了查询​​的形状(正确连接后，等等)，您应该返回SELECT子句并显式列出您实际要检索的列.检索超出所需数量的列(包括意外的新列)可能会导致SQL Server必须使用效率较低的计划，或检索使用者永远不会使用的大量数据.

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.

这篇关于SQL-除了本专栏之外，为什么没有其他方法可以全选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！