Re: Tricky join question

Tim Tassonis <timtas@cubic.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