Multiple Full Outer Join on Multiple Tables

Multiple FULL OUTER JOIN on multiple tables

SELECT  A.column2
        , B.column2
        , C.column2
FROM 
(
    (SELECT month, column2 FROM table1) A
    FULL OUTER JOIN
    (SELECT month, column2 FROM table2) B on A.month= B.month
    FULL OUTER JOIN 
    (SELECT month, column2 FROM table3) C on ISNULL(A.month, B.month) = C.month
)

What is a good way to make multiple full outer join?

This fiddle illustrates the problem.

If you want the rows from tables B and C to join, you need to accomodate the fact that maybe the data comes from table B and not A. The easiest is probably to use COALESCE.

Your join should therefore look like:

SELECT a.*, b.*, c.*
FROM tableA a
FULL JOIN tableB b ON a.id = b.id
FULL JOIN tableC c ON COALESCE(a.id, b.id) = c.id
-- FULL JOIN tableD d ON COALESCE(a.id, b.id, c.id) = d.id
-- FULL JOIN tableE e ON COALESCE(a.id, b.id, c.id, d.id) = e.id

Multiple Full Outer Joins

SELECT COALESCE(X.id,t3.id) AS id, *-- specific columns here instead of the *
FROM 
    (
       SELECT COALESCE(t1.id,t2.id) AS id, * -- specific columns here instead of the *
       FROM T1 FULL OUTER JOIN T2 on T1.id = T2.id
    ) AS X
    FULL OUTER JOIN T3 on X.id = t3.id

How to perform full outer joins on multiple tables

You don't want a FULL JOIN. You want a LEFT JOIN:

select s.name, sp.name 
from student s left outer join
     plays p
     on s.usn = p.usn left outer join
     sport sp
     on p.sport_id = sp.sport_id;

A left join keeps all rows in the first table and matching rows in the subsequent tables -- which is exactly what you want.

FULL JOIN is rarely needed. I write a lot of SQL and months go by without my using full join in any database.

In this example, a FULL JOIN would be used if you also wanted all sports that have no students. That is NULL values could appar in any column.

SQL Full Outer Join with Multiple Tables

You need to do one of two things (and both of these assume that Table0 has all instances of num) -

1. If all rows are already summed for the 'leaf' tables (1 - 4), then a simple LEFT JOIN (with a COALESCE() in the select) will suffice - you don't even need the GROUP BY.

2. If you need the rows summed, you're going to need to sum them before the join, given that otherwise multiple rows per num in different tables will cause the results to multiply.

Something like this:

SELECT Table0.num, COALESCE(Table1.qty, 0), COALESCE(Table2.qty, 0), 
                   COALESCE(Table3.qty, 0), COALESCE(Table4.qty, 0)
FROM Table0
LEFT JOIN (SELECT num, SUM(qty1) as qty
           FROM Table1
           GROUP BY num) Table1
ON Table1.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty2) as qty
           FROM Table2
           GROUP BY num) Table2
ON Table2.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty3) as qty
           FROM Table3
           GROUP BY num) Table3
ON Table3.num = Table0.num
LEFT JOIN (SELECT num, SUM(qty4) as qty
           FROM Table4
           GROUP BY num) Table4
ON Table4.num = Table0.num

(working SQLFiddle example)

Oracle FULL OUTER JOIN three tables with two conditions

I basically combined the answers of @dandarc and @thorsten-kettner (thank you very much for you valuable input):

Since MEASUREMENT_VOLUME and MEASUREMENT_AREA are much bigger than MEASUREMENT, I split up the JOINs:

SELECT *
FROM 
(
  SELECT *
  FROM MEASUREMENT
  JOIN MEASUREMENT_AREA
    USING(ID)
  WHERE ID = 1000
)
FULL JOIN
(
  SELECT *
  FROM MEASUREMENT
  JOIN MEASUREMENT_VOLUME
    USING(ID)
  WHERE ID = 1000
) USING (ID, MEAS_NAME, NAME);

For my purposes it is important that the big tables are first joined to MEASUREMENT and then those results are combined (might as well work with UNION ALL and GROUP BY as suggested by @dandarc).

This efficiently solves my problem. The FULL JOIN on three tables took more than 3 minutes with query 2. With this solution it takes seconds.

Note that my real life problem is more complicated since I have so select dozens of columns and can't simply use SELECT *. Thus, I can't use USING(ID, MEAS_NAME, NAME) but need to stick with the ON syntax.

---

Related Topics