Inner and Outer Joins

To Demonstrate inner and outer joins, the first thing I need to do is to add some data in to the database that isn't referenced by another table. So here I'm adding a publisher that I have no CDs for. Alas, no Beatles in my collection!

mysql> insert into publisher (name) values ('Apple Records');
Query OK, 1 row affected (0.00 sec)

A normal join would not tell us about Apple Records, as there is nothing to report.

mysql> SELECT c.title, p.name FROM cdtable c, publisher p
    -> where c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
+--------------------+----------------+
8 rows in set (0.00 sec)

mysql>

We get the same results when we code an Inner Join,

mysql> SELECT c.title, p.name FROM cdtable c INNER JOIN publisher p ON c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
+--------------------+----------------+
8 rows in set (0.00 sec)

mysql>

When we code a righthand outer join on Publisher, we see Apple, but with no CDs of course.

mysql> SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;
+--------------------+----------------+
| title              | name           |
+--------------------+----------------+
| Waking Up          | EMI            |
| Bethany Dillon     | EMI            |
| Imagination        | EMI            |
| Guaranteed         | SONY           |
| 15                 | SONY           |
| Made To Love Magic | Island Records |
| Five Leaves Left   | Island Records |
| Bryter Layter      | Island Records |
| NULL               | Apple Records  |
+--------------------+----------------+
9 rows in set (0.00 sec)

mysql>