CODEWITHSUNDEEP
sqloracle-database
Ewaver
Ewaver

Reputation: 1181

How to do a Oracle SQL Left Outer Join on 3 tables with two left tables?

I am trying to figure out the best way to join 3 table with unique IDs where 2 of the tables need to be the 'Left' Tables compared to the 3rd table. The 3rd table would provide all the nulls that I need for my analysis.

For example:

table 1 = table_r, table 2 = table_n, table 3 = table_t

unique_r    unique_n     unique_t    match
abc                      abc          yes
cde                      null         no
            efg          efg          yes
            jkl          null         no

This is an example result that I want to get, where table_r compared to table_t gives me the matches and the nulls and table_n compared to table_t gives me the matches and the null. Then I would do a simple case statement to compare the result into one 'match' column and I would know what is missing.

My SQL of sorts looks like this which only give me the one left side.

select * from table_r left join table_t
on unique_r = unique_t
left join table_n
on unique_n = unique_t;

Thanks for any advice :)

Upvotes: 0

Views: 1046

Answers (3)

John Bollinger
John Bollinger

Reputation: 180058

Based on your example query, you want the results to contain all the columns from all three tables. Furthermore table_r and table_n seem to be unrelated, but I suppose you don't want a cross product of their rows. This is a rather strange scenario, but you should be able to achieve it like this:

SELECT *
FROM
  table_r
  FULL OUTER JOIN table_n
    ON 1 = 0
  LEFT JOIN table_t
    ON unique_r = unique_t OR unique_n = unique_t

Alternatively, this might perform better:

SELECT *
FROM
  (
    SELECT *
    FROM table_r
      LEFT JOIN (
        SELECT *
        FROM table_n
        WHERE unique_n IS NULL
      )

    UNION ALL

    SELECT * FROM (
        SELECT *
        FROM table_r
        WHERE unique_r IS NULL
      )
      RIGHT JOIN table_n
  )
  LEFT JOIN table_t
    ON unique_r = unique_t OR unique_n = unique_t

That supposes no unique_r or unique_n value in the base tables is NULL. The two innermost subqueries thus select result sets that contain all the columns of their respective base tables, but no rows. As a result, the LEFT and RIGHT outer joins in the middle subqueries should be very fast, yet they should produce results with the correct columns, in corresponding order, just as needed for a UNION ALL (which will also be very fast). Obviously, this is an ugly, muddy mess. Don't even consider it if the first alternative is fast enough.

Upvotes: 2

DRapp
DRapp

Reputation: 48129

start with a centralized union from ALL tables, then left-join on them back to each original table...

select
      r.unique_r,
      n.unique_n,
      t.unique_t
      case when (
           case when r.unique_r is null then 0 else 1 end
         + case when n.unique_n is null then 0 else 1 end
         + case when t.unique_t is null then 0 else 1 end >= 2 ) then 'yes' else 'no' end as Matched
   from 
      ( select unique_r as base1 from table_r
        union select unique_n from table_n
        union select unique_t from table_t ) allBase
         left join table_r r
            on allBase.base1 = r.unique_r
         left join table_n n
            on allBase.base1 = n.unique_n
         left join table_t t
            on allBase.base1 = t.unique_t

Upvotes: 0

wgitscht
wgitscht

Reputation: 2766

If you have the same fields in table_r and table_n, you can do a UNION with *, otherwise, specify the fields you want to select, e.g an unique_n IS NOT NULL as match

select * from table_r left join table_t
on unique_r = unique_t
Union
select * from table_n left join table_t
on unique_n = unique_t

Upvotes: 0

Related Questions