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