How to join multiple select statements in SQL

join, oracle, performance, sql, subquery

Solution

I think you could rewrite this query to the following (see this 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 :-)

Problem

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

Original source