Creating a View

Let's pretend that we regularly want to be able to select all our publishers with the CDs we have on file for them. We saw in the last post that the SQL is:

SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;

We can create a view to do this:

mysql> CREATE VIEW V_Publisher as
    -> SELECT c.title, p.name FROM cdtable c RIGHT OUTER JOIN publisher p ON c.pubid = p.id;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> describe V_Publisher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| title | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

We can the simply select from this view:

mysql> select * from V_Publisher;
+--------------------+----------------+
| 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>