How to left outer join from two tables to a third table

Question

I've got three tables A B and C (the last is the result I want)

A.id        B.age         C.id C.age      result id  age
   1            5            1     5              1    5
   2            6            2     0           null null
   3            7            0     7           null null
   4            8            4     8              4    8
   5            9            5     9              5    9

I want to do an outer join from A and B to C such that I end up with result above. If either of the columns are missing from C, it should yield null. If I do an inner join:

select a.id, b.age where a.id = c.id and b.age = c.age

I'll get 3 rows back. I still want 5 rows back. I'm using IBM db2 v9 something. I'm trying to figure out using the newer left outer join syntax how to make it go, but I'm not seeing it. I can see how I'd do it in the old sybase *= outer join syntax, but I can't see how to do it the new style way. Is this even possible?

Somebody suggested doing a union of half the results of each. Unions just make a mess, seems to me, I should be able to do a left outer join from both tables.

Help? Thanks.

What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `RDBMS` stands for *Relational Database Management System*. `RDBMS is the basis for SQL`, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo, Feb 28 '13 at 16:45
... why do you want the nulls? They don't really tell you any useful information. — Clockwork-Muse, Feb 28 '13 at 16:59
Because this is a very simplified example of what I'm actually doing. There's other useful information in the result set of the larger query I'm actually doing. — stu, Feb 28 '13 at 17:12
The only thing I know will work is to turn a+b into a derived table then I can do an left outer join between ab and c. But in the real world situation, a and b are both already really complicated derived tables. I was hoping for a sql syntax thing I was missing. — stu, Feb 28 '13 at 19:13

score 1 · Answer 1 · answered Feb 28 '13 at 16:49

1

Do you mean this?

SELECT a.id, b.age
FROM a
CROSS JOIN b
LEFT OUTER JOIN c on a.id = c.id AND b.age = c.age

Your question is not clear on whether you cross join A and B or whether B is also left joined:

SELECT a.id, b.age
FROM a
LEFT OUTER JOIN c on a.id = c.id
LEFT OUTER JOIN b on b.age = c.age

There are even more possible combinations...

answered Feb 28 '13 at 16:49

devio

36,858
7
80
143

I tried this, but db2 seems to balk on this syntax. Actually I tried something different, let me give your example a whirl, thanks. – stu Feb 28 '13 at 16:56
no go, I got a cartesian-ey type result. It generated every combination of id and age. But a good idea, thanks. – stu Feb 28 '13 at 17:08

Joseph Wright · Answer 2 · 2013-02-28T23:32:36.083

-- A Dummy table
with A as (
  select
    2 as id
  from
    sysibm.sysdummy1)

-- B Dummy table
, B as (
  select
    6 as age
  from
    sysibm.sysdummy1)

-- C Dummy table    
, C as (
  select
    2 as id,
    0 as age
  from
    sysibm.sysdummy1)


-- Actual result query
select
  A.id as "A.id",
  B.age as "B.age",
  C.id as "C.id",
  C.age as "C.age",
  case
    when A.id = C.id and B.age = C.age then
      C.id
    else
      null
  end as "result id",
  case
    when A.id = C.id and B.age = C.age then
      C.age
    else
      null
  end as "result age"
from
  sysibm.sysdummy1 as Dummy
  left outer join A as A on 1=1
  left outer join B as B on 1=1
  left outer join C as C on 1=1

Here's my solution that will produce matching results to the original post. Just swap out the values in A, B, and C dummy tables with the examples provided.

Edit: The same result without using a case statement:

select
  A.id as "A.id",
  B.age as "B.age",
  C.id as "C.id",
  C.age as "C.age",
  R.id as "result id",
  R.age as "result age"
from
  sysibm.sysdummy1 as Dummy
  left outer join A as A on 1=1
  left outer join B as B on 1=1
  left outer join C as C on 1=1
  left outer join C as R 
    on A.id = R.id
    and B.age = R.age

Sunil · Answer 3 · 2017-03-26T06:04:37.837

It's a late response to this question but the query below should do it.

I have created an online sqlfiddle at http://sqlfiddle.com/#!3/07d74/5

You can verify your requirements using above online sqlfiddle. It gives the exact same results that you asked for.

I could not find an online tool for running DB2 queries even after extensive googling, but I checked IBM's docs on DB2 syntax for cross join and left join before coming up with this query. So, it should work in DB2, but please let me know if you see any issues. If you know an online tool for DB2 please let me know. The sqlfiddle is based on Sql Server since there is no option for DB2 on sqlfiddle, but like I said the query is compatible with DB2 as per the docs I read.

The DB2 compatible query is as below.

 SELECT D.id,
       D.age
FROM C
LEFT OUTER JOIN
  (SELECT A.id,
          B.age
   FROM A
   CROSS JOIN B) D ON D.id = C.id
AND D.age = C.age;

bwperrin · Answer 4 · 2015-11-18T19:53:26.950

-1

Sorry about incomplete and incorrect "FULL JOIN" post.

Wouldn't it be:

select case when b.age is not null then a.id end as id
     , case when a.id is not null then b.age end as age
from c
  left join a on a.id  = c.id
  left join b on b.age = c.age

edited Nov 18 '15 at 19:53

answered Feb 28 '13 at 16:48

bwperrin

680
5
12

2

... I'm afraid you're going to need to provide an actual example – Clockwork-Muse Feb 28 '13 at 16:57
can you explain how to cross join three tables? This one's new to me. – stu Feb 28 '13 at 16:57
I looked it up, a full join would give me a Cartesian product, which I do not want. – stu Feb 28 '13 at 17:21
@stu: a full outer join is **not** a cross join. Its something completely different – Mar 01 '13 at 00:02
yes, I misspoke, a full join still wouldn't work for me though. – stu Mar 01 '13 at 21:29

How to left outer join from two tables to a third table

4 Answers4