SQL SQL中如何连接多个select语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15581236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join multiple select statements in SQL
提问by Eduardo
I would like to know how a query of this form could be improved on in terms of 1) Style and 2) Performance. In case it matters, I'm using Oracle as my database. Also, the purpose of this query is to select all records from the first select statement that do not have a corresponding record in the set of records to the right of the LEFT JOIN. The records from ColumnA are not necessarily unique in any of the tables.
我想知道如何在 1) 样式和 2) 性能方面改进这种形式的查询。以防万一，我使用 Oracle 作为我的数据库。此外，此查询的目的是从第一个 select 语句中选择所有记录，这些记录在 LEFT JOIN 右侧的记录集中没有相应的记录。来自 ColumnA 的记录在任何表中不一定是唯一的。
select ColumnA
from
(Select ColumnA
from Table1)
left join
((select ColumnA, ColumnB
from Table2)
union
(select ColumnA, ColumnB
from Table3))
using (ColumnA)
where ColumnB is null
group by ColumnA;
Thank you.
谢谢你。
采纳答案by Josien
I think you could rewrite this query to the following (see this SQL Fiddle):
我认为您可以将此查询重写为以下内容（请参阅此SQL Fiddle）：
SELECT DISTINCT ColumnA
FROM (SELECT ColumnA
FROM Table1)
MINUS
(SELECT ColumnA
FROM Table2
UNION
SELECT ColumnA
FROM Table3);
As for style, I'd recommend using the explicit join condition syntax LEFT JOIN ... ON table1.somecolumn = table2.someothercolumninstead of the USINGcondition, for readability and clarity. But this might well be personal preference :-)
至于样式，为了可读性和清晰度，我建议使用显式连接条件语法LEFT JOIN ... ON table1.somecolumn = table2.someothercolumn而不是USING条件。但这很可能是个人喜好:-)
回答by sgeddes
I don't see the need for the UNION:
我认为不需要UNION：
select T1.ColumnA
from Table1 T1
left join Table2 T2 ON T1.ColumnA = T2.ColumnA
left join Table3 T3 ON T1.ColumnA = T3.ColumnA
where T2.ColumnA IS NULL
or T3.ColumnA IS NULL
group by T1.ColumnA;
Another option would be to use NOT IN:
另一种选择是使用NOT IN：
select distinct ColumnA
from Table1
where ColumnA not in (select ColumnA from Table2)
and ColumnA not in (select ColumnA from Table3);
Both of these should return any ColumnA records in Table1 that aren't in Table2 or Table3.
这两个都应该返回 Table1 中不在 Table2 或 Table3 中的任何 ColumnA 记录。
回答by Ronnis
Here are three alternatives.
这里有三个替代方案。
select distinct ColumnA
from Table1 a
left join Table2 b using(ColumnA)
left join Table3 c using(ColumnA)
where b.ColumnB is null
and c.ColumnB is null;
.
.
select distinct ColumnA
from Table1 a
where ColumnA not in(select ColumnA from Table2)
and ColumnA not in(select ColumnA from Table3);
.
.
select distinct ColumnA
from Table1 a
where ColumnA not in(select ColumnA from Table2 union
select ColumnA from Table3);
