Full Outer Self Join

Question

The problem is to return the rows which contain nulls as well. Below is SQL code to create table and populate it with sample data.

I'm expecting below, but query does not show the two rows with null values.

src_t1  id1_t1  id2_t1  val_t1     src_t2  id1_t2  id2_t2  val_t2

                                        b       z      z        4
a        w       w      100             b       w      w        1
a        x       x      200             b       x      x        2
a        y       y      300

Data:

CREATE TABLE sample (
    src VARCHAR(6)
    ,id1 VARCHAR(6)
    ,id2 VARCHAR(6)
    ,val FLOAT
);

INSERT INTO sample (src, id1, id2, val)
VALUES ('a', 'w', 'w', 100)
      ,('b', 'w', 'w', 1) 
      ,('a', 'x', 'x', 200)
      ,('b', 'x', 'x', 2) 
      ,('a', 'y', 'y', 300)
      ,('b', 'z', 'z', 4) 
;

This is my test query. It does not show results when t1.src = 'a' and t1.id1 = 'y' or when t2.src = 'b' and t2.id1 = 'z'.

Why?

What's the correct query?

SELECT t1.src, t1.id1, t1.id2, t1.val
  ,t2.src as src2, t2.id1, t2.id2, t2.val
FROM sample t1 FULL OUTER JOIN sample t2
  ON t1.id1 = t2.id1 AND t1.id2 = t2.id2
WHERE (t1.src = 'a' AND t2.src = 'b') 
  OR (t1.src IS NULL AND t1.id1 IS NULL AND t1.id2 IS NULL)
  OR (t2.src IS NULL AND t2.id1 IS NULL AND t2.id2 IS NULL)

I've also tried moving the conditions in the WHERE clause to the ON clause as well.

TIA.

To adapt the SQL from my answer in that other question, here it would look like `from (select * from sample where src = 'a') t1 full outer join (select * from sample where src = 'b') t2 on t2.id1 = t1.id1 and t2.id2 = t1.id2`: http://www.sqlfiddle.com/#!6/e7b1e/3 — , Jun 22 '13 at 16:21
@hvd If you post it as an answer, I'll be happy to accept it. — Karl, Jun 22 '13 at 16:32
Thanks, I appreciate that, but I intentionally only posted it as a comment: duplicate questions shouldn't get duplicate answers, if they can simply get a pointer to the answer already given elsewhere. :) — , Jun 22 '13 at 16:36

Pieter Geerkens · Accepted Answer · 2013-06-22T16:35:53.573

6

The WHERE clause evaluates too late, effectively converting your query into an inner join.

Instead, write your query like this using proper JOIN syntax:

SELECT t1.src, t1.id1, t1.id2, t1.val
  ,t2.src as src2, t2.id1, t2.id2, t2.val
FROM (
    select * from sample
    where src='a'
) t1 FULL OUTER JOIN (
    select * from sample 
    where src='b'
)  t2
  ON t1.id1 = t2.id1 AND t1.id2 = t2.id2

yielding this result set:

src  id1  id2  val         src2 id1  id2  val
---- ---- ---- ----------- ---- ---- ---- -----------
a    w    w    100         b    w    w    1
a    x    x    200         b    x    x    2
NULL NULL NULL NULL        b    z    z    4
a    y    y    300         NULL NULL NULL NULL

Update:
Note also the use of two sub-queries to clearly separate the source table into two distinct relvars. I missed this for a minute on my first submission.

edited Jun 22 '13 at 16:35

answered Jun 22 '13 at 16:27

Pieter Geerkens

11,775
2
32
52

-1 because the question explicitly includes the expected output, which is different from your output. – Jun 22 '13 at 16:29
@hvd is correct. Pieter, notice how your results include 'a' and 'b' values in both source columns. The point is to have only 'a' on one side and 'b' on the other. – Karl Jun 22 '13 at 16:35
Downvote removed. Although harmless, `and (t1.src = 'a' AND t2.src = 'b')` on your last line is redundant now. – Jun 22 '13 at 16:37
@PieterGeerkens, the `and (t1.src = 'a' AND t2.src = 'b')` is not necessary (and maybe would not work) since the two table expressions already filter on the required condition. – Karl Jun 22 '13 at 16:44
@Karl: That is the difference between condition in a WHERE clause and a JOIN clause. Putting that restriction in a WHERE clause effectively converts to an INNER JOIN, as you note; however, I had it in the JOIN clause where it does not create that effect. – Pieter Geerkens Jun 22 '13 at 16:49
@Karl: That is one of the many reasons why all joins should be converted to JOIN syntax, even in legacy code. It results in correcting unidentified existing bugs. ;-) – Pieter Geerkens Jun 22 '13 at 16:52
@PieterGeerkens what technique did you use to post your formatted output? I spent considerable time trying to post my desired output in original question with decent formattting. – Karl Jun 23 '13 at 14:58
In Query Analyser, I right-clicked and sent results to text. Then Copy-Pasted and set as a code block. – Pieter Geerkens Jun 23 '13 at 17:59

score 5 · Answer 2 · answered Jun 23 '13 at 14:56

5

Actually, I think the solution is a bit cleaner if a CTE is used:

WITH A AS (
  select * from sample where src='a'
),
B AS (
  select * from sample where src='b'
)
SELECT *
FROM A FULL OUTER JOIN B
    ON A.ID1 = B.ID1 AND A.ID2 = B.ID2
;

answered Jun 23 '13 at 14:56

Karl

1,814
1
25
37

Full Outer Self Join

2 Answers2