如何在SQL中加入多个select语句

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语句中选择所有在左连接右侧的记录集中没有对应记录的记录。来自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.

谢谢你！

3 个解决方案

#1

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.someothercolumn instead of the USING condition, for readability and clarity. But this might well be personal preference :-)

至于风格，我建议使用显式的左连接条件语法……在表1。somecolumn =表二。为了可读性和清晰度，用其他列代替使用条件。但这很可能是我个人的偏好:

#2

I don't see the need for the 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.

这两个都应该返回表1中没有表2或表3中的任何ColumnA记录。

#3

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);

#1