MySQL 5.1.56.
Setup:
create database join_test;
connect join_test;
create table posts (post_id int not null auto_increment, name varchar(100), primary key (post_id));
create table categories (category_id int not null auto_increment, name varchar(100), primary key (category_id));
create table posts_categories (posts_categories_id int not null auto_increment, post_id int not null, category_id int not null, foreign key (post_id) references posts(post_id), foreign key (category_id) references categories(category_id), primary key (posts_categories_id));
insert into posts (name) values ('post one');
insert into posts (name) values ('post two');
insert into posts (name) values ('post three');
insert into categories (name) values ('category one');
insert into categories (name) values ('category two');
insert into categories (name) values ('category three');
insert into posts_categories (post_id, category_id) values (1, 1);
insert into posts_categories (post_id, category_id) values (1, 2);
insert into posts_categories (post_id, category_id) values (2, 2);
Tests (remember a join is the same as an inner join):
mysql> select posts.*, categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories left outer join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories right outer join posts on posts_categories.post_id = posts.post_id join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id left outer join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories join posts on posts_categories.post_id = posts.post_id right outer join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+----------------+
| post_id | name | category_id | name |
+---------+----------+-------------+----------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
| NULL | NULL | 3 | category three |
+---------+----------+-------------+----------------+
4 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories left outer join posts on posts_categories.post_id = posts.post_id left outer join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+--------------+
| post_id | name | category_id | name |
+---------+----------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
+---------+----------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories right outer join posts on posts_categories.post_id = posts.post_id left outer join categories on posts_categories.category_id = categories.category_id;
+---------+------------+-------------+--------------+
| post_id | name | category_id | name |
+---------+------------+-------------+--------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
| 3 | post three | NULL | NULL |
+---------+------------+-------------+--------------+
4 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories left outer join posts on posts_categories.post_id = posts.post_id right outer join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+----------------+
| post_id | name | category_id | name |
+---------+----------+-------------+----------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
| NULL | NULL | 3 | category three |
+---------+----------+-------------+----------------+
4 rows in set (0.00 sec)
mysql> select posts.*, categories.* from posts_categories right outer join posts on posts_categories.post_id = posts.post_id right outer join categories on posts_categories.category_id = categories.category_id;
+---------+----------+-------------+----------------+
| post_id | name | category_id | name |
+---------+----------+-------------+----------------+
| 1 | post one | 1 | category one |
| 1 | post one | 2 | category two |
| 2 | post two | 2 | category two |
| NULL | NULL | 3 | category three |
+---------+----------+-------------+----------------+
4 rows in set (0.00 sec)
