Re: Tricky join question

Tim Tassonis <timtas(at)cubic(dot)ch> writes:
> In mysql, the following statement:

> SELECT c.id, c.name, pc.person_id
> FROM person as p
> left outer join person_course as pc on p.id = pc.person_id
> right outer join course as c on pc.course_id = c.id
> where p.id = 2 order by 1;

> will get me the following result:

> +---+-----------------------------------------+----------+
> | id| name | person_id|
> +---+-----------------------------------------+----------+
> | 1 | SQL Beginner | |
> | 2 | SQL Advanced | |
> +---+-----------------------------------------+----------+

Really? It would be unbelievably broken if so, but a quick experiment
with mysql 5.0.27 says they return an empty set same as us.

You *would* get that answer without the WHERE clause, but neither of
those rows meet the WHERE. Look at the complete join output:

regression=# SELECT *
FROM person as p
left outer join person_course as pc on p.id = pc.person_id
right outer join course as c on pc.course_id = c.id
;
id | name | person_id | course_id | id | name
----+------+-----------+-----------+----+--------------
| | | | 1 | SQL Beginner
| | | | 2 | SQL Advanced
(2 rows)

The person-left-join-person_course join produces rows, but none of them
can match course during the right join, so they don't get through.

I think what you want might be a full join for the second step:

regression=# SELECT *
FROM person as p
left outer join person_course as pc on p.id = pc.person_id
full outer join course as c on pc.course_id = c.id
;
id | name | person_id | course_id | id | name
----+------+-----------+-----------+----+--------------
1 | Jack | | | |
2 | Jill | | | |
3 | Bob | | | |
| | | | 1 | SQL Beginner
| | | | 2 | SQL Advanced
(5 rows)

regression=# SELECT *
FROM person as p
left outer join person_course as pc on p.id = pc.person_id
full outer join course as c on pc.course_id = c.id
where p.id = 2 order by 1;
id | name | person_id | course_id | id | name
----+------+-----------+-----------+----+------
2 | Jill | | | |
(1 row)

regression=# insert into person_course values(2,2);
INSERT 0 1
regression=# SELECT *
FROM person as p
left outer join person_course as pc on p.id = pc.person_id
full outer join course as c on pc.course_id = c.id
where p.id = 2 order by 1;
id | name | person_id | course_id | id | name
----+------+-----------+-----------+----+--------------
2 | Jill | 2 | 2 | 2 | SQL Advanced
(1 row)

BTW, I tried to duplicate this in mysql and was surprised to find that
5.0.27 doesn't seem to support full join at all :-(

regards, tom lane