2 Right Outer Join in SQL Statement

Question

Hi,

I have an issue in understanding a very simple yet interesting query concerning 2 right outer joins with 'non-sequential' on-expressions. Here is the query:

select * from C 
right outer join A on A.F1 = C.F1 
right outer join B on B.F1 = C.F1;

Here are the tables:

create table A ( F1 varchar(200)); 
create table B ( F1 varchar(200)); 
create table C ( F1 varchar(200));

Here are some rows:

insert into A values ('A'); 
insert into A values ('B');
insert into A values ('C');
insert into B values ('B'); 
insert into B values ('C');
insert into B values ('D');
insert into C values ('A'); 
insert into C values ('C'); 
insert into C values ('D');

Note: The query select * from C right outer join A on A.F1 = C.F1 right outer join B on B.F1 = C.F1; join expressions both refers to table C.

The query returns (in columns then rows)

(NULL,NULL, B),(C, C, C).(NULL, **NULL**, D)

and I expected (in my little understanding of SQL)

(NULL,NULL, B),(C, C, C),(NULL, **D**, D)

What is the logical sequence whereby SQL (tested on Microsoft SQL as well as MySQL) get to these values.

In my "execution" sequence I sit with on table A values of A, null (for B), C, null (for D) and in Table B, null (for A), B, C, D before the "product" is amalgamated with C (B,C,D).

Gawie PS: I have test this using MySQL as well as Microsoft SQL 2008... with the same results.

Answer 1

Table A doesn't contain a field 'D', so there's no way that D can appear in the second column in the result set, Gawie. The fields in the result set will be C.F1, A.F1, B.F1 (in the same order that the tables appear in the joins).

Answer 2

Right joins are evaluated in the order in which they appear (from left to right.) Let's start with from C:

C        -->  (A), (C), (D)

Then right join A, joining on A.F1 = C.F1:

C, A     -->  (A, A), (NULL, B), (C, C)

Then right join B (matching the first column, since the join is on B.F1 = C.F1):

C, A, B  -->  (NULL, NULL, B), (C, C, C), (NULL, NULL, D)

So because from C right join A does not contain an D in the first column, the right join B fails to match, and appends a row containing NULL for the column from C and A, and D for the column from B.

Answer 3

There's no D value in table "A" to join to, which is why it returns NULL instead of the D you expect. It's easier to see when you identify which table the value is coming from via column alias:

SELECT c.f1 AS c, a.f1 AS a, b.f1 AS b 
  FROM c 
RIGHT JOIN A on A.F1 = C.F1 
RIGHT JOIN B on B.F1 = C.F1

Answer 4

Hi Andomar,

Thanks for you post. Your post makes sense to me.

Even though I have good faith in your answer I would like to add (this after sleeping on it and tickling it early this morning again) to all the SQL wannabies like me:

I have done at least 9 other combinations of right outer joins on these tables with different criteria and ordering of joins and:

1. Order of tables does matter in right joins.
2. However, for ALL these I get the same answer manually sequentially executing from left to right as I get sequentially right to left... In fact, my answer and the amount of values worked with is smaller and simpler going right to left in right joins; however look at point 3...
3. Point 2 works easily and straight forward where the on expression works on the current and the previous table e.g. (note equality is in shorthand on the 'key')

select * from A right outer join B on B = A right outer join C on C = B - BUT It has some weird AND logic (which works consistently somehow) when doing something like

select * from A right outer join B on B = A right outer join C on C = A;

Again Andomar, thanks - I'll redo my tests left to right, using your pattern.

Also, don't want to clutter the thread - but I want to say many thanks to StackOverflow. First time posting here (after several reads) and have to say you make this interaction simple, effective and efficient. Thanks again!

Answer 5

Hi Andomar,

C, A --> (A, A), (NULL, B), (C, C)

Then right join B (matching the first column, since the join is on B.F1 = C.F1):

C, A, B --> (NULL, NULL, B), (C, C, C), (NULL, NULL, D)

This is where I get lost...

Why is C, A, B not equal to (NULL, B, B), ... etc

for C, A for column B equals (NULL, B) right outer join (NULL, B) with B should yield (NULL, B, B)... unless the matching is reversed! C, A, B --> (NULL, B, B) (which is obviously wrong - just don't completely understand why)