Multiple FULL OUTER JOIN on multiple tables

Question

I have multiple outer joins

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 A.month= C.month
)

Now the last join is having a problem, its repeating when month of A is more than B but if B has more month that A we have OUTER JOIN in C with month of A which now repeats, so I guess having a FULL OUTER JOIN within two table might solve the problem?? Any indepth links??

Sample Data(Incorrect)

╔════════════╦═════════╦═════════════╗
║  Revenue   ║ Budget  ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║     6.9172 ║ 3.5046  ║ Jan         ║
║     7.3273 ║ 3.7383  ║ Feb         ║
║     7.3273 ║ 3.9719  ║ Mar         ║
║     7.2726 ║ 4.2056  ║ Apr         ║
║     7.2595 ║ 6.7757  ║ May         ║
║     7.2726 ║ 6.7757  ║ Jun         ║
║     0.41   ║ 0.00    ║ Jul         ║
║     0.41   ║ 0.00    ║ Aug         ║
║     0.41   ║ 0.00    ║ Sep         ║
║     0.41   ║ 0.00    ║ Oct         ║
║     7.4696 ║ 0.00    ║ Nov         ║
║     7.4696 ║ 0.00    ║ Dec         ║
║     0.00   ║ 9.3457  ║ Sep         ║
║     0.00   ║ 16.3551 ║ Dec         ║
║     0.00   ║ 6.3084  ║ Jul         ║
║     0.00   ║ 14.0186 ║ Oct         ║
║     0.00   ║ 16.3551 ║ Nov         ║
║     0.00   ║ 6.1915  ║ Aug         ║
╚════════════╩═════════╩═════════════╝

Correct Data

╔════════════╦═════════╦═════════════╗
║  Revenue   ║ Budget  ║ ActualMonth ║
╠════════════╬═════════╬═════════════╣
║     6.9172 ║ 3.5046  ║ Jan         ║
║     7.3273 ║ 3.7383  ║ Feb         ║
║     7.3273 ║ 3.9719  ║ Mar         ║
║     7.2726 ║ 4.2056  ║ Apr         ║
║     7.2595 ║ 6.7757  ║ May         ║
║     7.2726 ║ 6.7757  ║ Jun         ║
║     0.41   ║ 6.3084  ║ Jul         ║
║     0.41   ║ 6.1915  ║ Aug         ║
║     0.41   ║ 9.3457  ║ Sep         ║
║     0.41   ║ 14.0186 ║ Oct         ║
║     7.4696 ║ 16.3551 ║ Nov         ║
║     7.4696 ║ 16.3551 ║ Dec         ║
╚════════════╩═════════╩═════════════╝

The outer joins are commutative. `A outer join B outer join C` is the same as `A outer join C outer join B`. In fact it is even the same as `A outer join (B outer join C)` I don't understand your problem *(note: this applies since both B and C join to A)* — RichardTheKiwi, Apr 23 '13 at 11:21
its actually a bar chart with basemonth as X-axis and revenue as Y-axis with budget as line bar — Sandip Bantawa, Apr 23 '13 at 12:00
@techdo: I guess where Revenue>0 wont work coz we might actually get zero value too — Sandip Bantawa, Apr 23 '13 at 12:04
Please add table1, table2 and table3 data. OR you just need a query on the sample data given? — TechDo, Apr 23 '13 at 12:11
You probably need months table over entire possible history, this way you could use left joins. Another way would be writing last join condition like c.month=isnull(a.month,b.month) - I've not tried, but this should exclude some unneeded combinations. — Arvo, Apr 23 '13 at 12:42

score 78 · Accepted Answer · answered Apr 25 '13 at 14:17

78

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
)

answered Apr 25 '13 at 14:17

Serge

6,554
5
30
56

2

this should fix your issue with C. Feel free to use the same logic (ISNULL) with B if needed. – Serge Apr 25 '13 at 14:22
5

depending on environment, is the ISNULL() equivalent to coalesce or oracle's NVL()? Does joining on NVL(A.ID, B.ID) = C.ID operate the same way – justin cress Apr 01 '14 at 18:31
5

I think it would be useful to add that if you have more than 3 tables you want to do a full outer join on, you either have to start nesting `ISNULL`, or you can just use `COALESCE` instead, which should work on all standard compliant databases. – Paschover Oct 07 '16 at 12:27
2

i originally had an or in my join clause, but this saved me orders of magnitude on performance. – FistOfFury Feb 17 '17 at 19:57

score 26 · Answer 2 · answered Apr 16 '15 at 12:00

26

One of the ways to do this could be create "anchor" table from all possible data from all three tables and then use left outer join:

select
    A.column2,
    B.column2,
    C.column2
from (
    select distinct month from table1
    union
    select distinct month from table2
    union
    select distinct month from table3
) as X
    left outer join table1 as A on A.month = X.month
    left outer join table2 as B on B.month = X.month
    left outer join table3 as C on C.month = X.month

answered Apr 16 '15 at 12:00

Roman Pekar

107,110
28
195
197

1

This is a great answer and perfectly fitted my needs. Thanks. – Ocean Airdrop Aug 05 '20 at 04:59
Great answer, easily readable. I attempted extending it to the case where there are records at several dates and the amounts were not summed by month yet. So far, there is some error in my code for the summed amounts: https://dba.stackexchange.com/questions/282958/get-monthly-sums-from-several-tables-with-same-date-amount-columns – OuzoPower Jan 10 '21 at 19:08
Great answer Roman. This solution is very simple – Sairam Cherupally Jan 19 '23 at 10:00

score 9 · Answer 3 · answered Apr 25 '13 at 14:13

Use option with COALESCE function to determine a column grouping.

SELECT COALESCE(t1.Month, t2.Month, t3.Month) AS Month, 
       SUM(ISNULL(t1.Col1, 0)) AS t1Col1, 
       SUM(ISNULL(t2.Col1, 0)) AS t2Col1, 
       SUM(ISNULL(t3.Col1, 0)) AS t3Col1
FROM dbo.table1 t1 FULL OUTER JOIN dbo.table2 t2 ON t1.Month = t2.Month
                   FULL OUTER JOIN dbo.table3 t3 ON t1.Month = t3.Month
GROUP BY COALESCE(t1.Month, t2.Month, t3.Month)

score 5 · Answer 4 · answered Apr 23 '13 at 13:33

I can think of 2 ways off the bat that you can address this, depending on what the actual logic is to define the results you want.

The first, and most fool-proof way, is to use GROUP BY month, and use aggregate functions like MAX(column2) to get the non-zero rows only, or if there are multiple non-zero rows you want to add, use SUM(). This is the best solution if there is an aggregate function that fulfills your logical intent.

Another is to include more conditions in your JOIN, like "WHERE a.month=b.month AND b.column2 > 0", but that still won't solve the problem if there can be more than one non-zero row.

I agree with Tab, use GROUP BY and SUM() or MAX() – Kevin Suchlicki Apr 23 '13 at 13:35 — Kevin Suchlicki, Apr 23 '13 at 13:35

score 2 · Answer 5 · answered Apr 23 '13 at 13:49

something like

select month, sum(a) a,  sum(b) b, sum(c) c from (
  SELECT month, column2 A, 0 B, 0 C FROM table1 
    union 
  SELECT month, 0 A, column2 B, 0 C FROM table2
    union 
  SELECT month, 0 A, 0 B, column2 C FROM table3
) x
group by month

score 2 · Answer 6 · answered Jan 24 '21 at 05:46

Instead of doing all the outer joins at once, how about doing them one at a time like this:

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

i.e., do full outer join on A and B, then do full outer join of that and C, then do full outer join of that and D, then do full outer join of that and E, etc.

This is less complex than the other answers above and can be repeated for as many tables as you'd like to full outer join.

Multiple FULL OUTER JOIN on multiple tables

6 Answers6

Linked