CODEWITHSUNDEEP
sqlt-sqljoinouter-join
Gawie Kellerman
Gawie Kellerman

Reputation: 151

2 Right Outer Join in SQL Statement

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.

Upvotes: 1

Views: 2028

Answers (4)

Gawie Kellerman
Gawie Kellerman

Reputation: 41

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)

Upvotes: 0

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332731

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

Upvotes: 1

Will A
Will A

Reputation: 25008

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).

Upvotes: 0

Related Questions