Using MySQL, Joins
Workshop Requirements
You should have completed Parts One, Two, Three and Four of this series.
You should also have access to the MySQL command line client software.
You should also have full permissions on a database.
Introduction
In this Virtual Workshop we will look at retrieving data from a relational database structure, i.e. with multiple tables, using the SQL JOIN Syntax. Most databases have multiple tables to avoid repeating data. i.e. why enter the details of a customer over and over again.
NOTE: For brevity I will only show 5 records in the examples in this page.
Creating Another Table
In order to see how JOINs work we need to create another table and populate it with data. So let's create a new table called 'genres' with the following properties.
| genreID | Unique Identifier | 01 |
| genre | Music Genre | Heavy Metal |
| boughtby | Type of person that buys this music | Greasy Haired Bikers |
mysql> CREATE TABLE genres (
-> genreID INT(2) auto_increment primary key,
-> genre VARCHAR(20),
-> boughtby VARCHAR(30)
->);
Insert the following genres into your genres table. The descriptions for the people that buy the music (boughtby) - I'll leave to you. You could also add other genres that are perhaps more relevant to your music collection.
| Pop | Example INSERT:
mysql> INSERT INTO genres VALUES (
-> '?',
-> 'Heavy Metal',
-> 'Greasy Haired Bikers'
-> ); |
| Easy Listening | |
| 'Classic' Rock | |
| Heavy Metal | |
| Soul | |
| Seventies | |
| Eighties | |
| Hip Hop | |
| Jazz | |
| Guitar Bands |
This could (depending on what you enter as descriptions) result in a table like this:
mysql> SELECT *
-> FROM genres;
+---------+----------------+------------------------------------------------+
| genreID | genre | boughtby |
+---------+----------------+------------------------------------------------+
| 1 | Pop | Little girls and adults who should know better |
| 2 | Easy Listening | Crushed velvet wearing lounge lizards |
| 3 | "Classic" Rock | Middle-aged men reliving their youth |
| 4 | Heavy Metal | Wannabe Bikers - who work PT at safeway |
| 5 | Soul | White Boys in thin leather ties |
| 6 | Seventies | Those not born til 1980 |
| 7 | Eighties | Those born in the '70's |
| 8 | Hip Hop | Middle-class Ghetto wannabes |
| 9 | Jazz | Those that *think* they are better |
| 10 | Guitar Bands | Those stuck in 1996 |
+---------+----------------+------------------------------------------------+
10 rows in set (0.00 sec)
Adding a genre to the 'cds' table
Next we need to add a 'genreID' column to our 'cds' table so we can store information about the genre of each cd. This will be an Integer (numeric) field as it will correspond to the unique identifier ('genreID') from the genres table.
mysql> ALTER TABLE cds
-> ADD genreID INT(2);
Check this has worked by using the describe command.
mysql> DESCRIBE cds; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | cdID | int(3) | | PRI | NULL | auto_increment | | artist | varchar(20) | YES | | NULL | | | title | varchar(30) | YES | | NULL | | | year | int(4) | YES | | NULL | | | label | varchar(20) | YES | | NULL | | | bought | date | YES | | NULL | | | tracks | int(2) | YES | | NULL | | | genreID| int(2) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec)
IMPORTANT: As we now have a column called 'genreID' in both tables we need to distinguish which we are talking about. This is why we have been prefixing the column name with the table name to ensure we can tell the difference. For example 'cds.genreID' and 'genres.genreID' are easily distinguishable in our example.
We are now ready to enter the genre type for each cd into our CDs table.
If we decide that our 'Jamiroquai' album is a soul album we need the 'genres.genreID' number from our 'genres' table for the soul entry, i.e. '5'. We must UPDATE the Jamiroquai record in the CDs table so that the 'cds.genreID' column also has a value of '5'.
mysql> UPDATE cds
-> SET cds.genreID = 5
-> WHERE (cds.cdID = 2);
If we issue a SELECT all command we can see the effect this has.
mysql> select * from cds; +------+-------------------+------------------+------+---------+ | cdid | artist | title | year | genreID | +------+-------------------+------------------+------+---------+ | 2 | Jamiroquai | A Funk Odyssey | 2001 | 5 |
Continue and enter the rest of the genres into the CDs table.
Beginning with Joins
Before starting with joins we should say a little about what exactly a join is. Basically it is the combining of two rows based on the comparative values in selected columns. This 'super-row' exists only for the duration of the query that creates it. We need to use joins to temporarily create 'complete' records from a database which may split related data across several tables (perhaps as a result of normalisation).
Cross-Join
Syntax:
SELECT <column_name> FROM <table1>, <table2>
A cross-join between two tables takes the data from each row in table1 and joins it to the data from each row in table2. To give an example lets look at two very simple tables.
| id | animal |
| 1 | Cat |
| 2 | Dog |
| 3 | Cow |
| id | Food |
| 1 | Milk |
| 2 | Bone |
| 3 | Grass |
A cross-join on these tables would produce the following result.
| 1 | Cat | 1 | Milk |
| 1 | Cat | 2 | Bone |
| 1 | Cat | 3 | Grass |
| 2 | Dog | 1 | Milk |
| 2 | Dog | 2 | Bone |
| 2 | Dog | 3 | Grass |
| 3 | Cow | 1 | Milk |
| 3 | Cow | 2 | Bone |
| 3 | Cow | 3 | Grass |
Where every row from one table is joined to every row in the other table. We can also see the effect of this by using an SQL cross-join on our tables (although in this example we are asking only to display one column from each table).
mysql> SELECT cds.artist, genres.genre
-> FROM cds, genres;
You should see every artist associated with every genre. This is obviously not a very useful join, but does specify the need for a join that uses some kind of comparison between the two tables.
The Equi-Join or Inner Join
Syntax:
SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)
In the equi-join the comparison we are making between two columns is that they match the same value. We can use this method to select certain fields from both tables and only the correct rows will be joined together. So if we were to use this join on the cds and genres tables in our own database (using the CDs that Mark provided in the last virtual workshop as an example).
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds, genres
-> WHERE (cds.genreID = genres.genreID);
+------------+------------------------------+----------------+
| artist | title | genre |
+------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Abba | Abbas Greatest Hits | Seventies |
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
| Various | Eurovision Song contest 2001 | Easy Listening |
+------------+------------------------------+----------------+
5 rows in set (0.00 sec)
Obviously your data will be different as you should have different CDs and different genres. If we compare the cross-join and equi-join we can see that the equi-join is just the cross join with a very restrictive WHERE condition, that forces only the rows in the second table RELEVANT to the rows in the first table to be retrieved. This method is fine if all we want to do is look at normalised data in a temporarily flat database view. However if we want to filter this data, we have to start adding more conditions to our WHERE clause and it seems rather redundant to have to specify joining conditions as part of the WHERE condition every time.
The Left Join
The left join is a mechanism used to join tables before we add other conditions such as WHERE etc.
Syntax:
SELECT <column_name> FROM <Table1> LEFT JOIN <Table2> ON Table1.column = Table2.column
Without adding a WHERE clause the Left Join produces the same results as the equi-join example above.
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID;
+-------------------+------------------------------+----------------+
| artist | title | genre |
+-------------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Abba | Abbas Greatest Hits | Seventies |
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
| Various | Eurovision Song contest 2001 | Easy Listening |
+-------------------+------------------------------+----------------+
5 rows in set (0.00 sec)
An important thing to note with this particular join is that even if there are no records in the second table (in this case 'genres') data will still be displayed from the first table. Or in other words data from the LEFT of the join will be displayed and is where the term LEFT JOIN comes from. To demonstrate this, UPDATE a genreID from row four (cdID of 5, because we deleted one row in part three) of the cds table (in this case westlife) to a value that doesn't exist in the genres table.
mysql> UPDATE cds SET cds.genreID = 100 WHERE (cds.cdID = 5);
...and run the query again....
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID;
+-------------------+------------------------------+----------------+
| artist | title | genre |
+-------------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Abba | Abbas Greatest Hits | Seventies |
| Various | Now 49 | Pop |
| westlife | westlife | NULL |
| Various | Eurovision Song contest 2001 | Easy Listening |
+-------------------+------------------------------+----------------+
5 rows in set (0.00 sec)
The artist and title are still displayed even though there is no data in the genre (and thus NULL is shown). To further illustrate this we can issue a RIGHT JOIN which is a variation where all the data on the RIGHT side of the join (the second table) is returned regardless of the presence of data from the first table.
Reset row four of the cds table to have the correct genreID value.
mysql> UPDATE cds
-> SET cds.genreID = 1
-> WHERE (cds.cdID = 5);
And run the RIGHT JOIN query including genres.genreID.
mysql> SELECT cds.artist, cds.title, genres.genreID, genres.genre
-> FROM cds
-> RIGHT JOIN genres
-> ON cds.genreID = genres.genreID;
+--------------------+------------------------------+---------+----------------+
| artist | title | genreID | genre |
+--------------------+------------------------------+---------+----------------+
| Various | Now 49 | 1 | Pop |
| westlife | westlife | 1 | Pop |
| Various | Eurovision Song contest 2001 | 2 | Easy Listening |
| NULL | NULL | 3 | "Classic" Rock |
| NULL | NULL | 4 | Heavy Metal |
| Jamiroquai | A Funk Odyssey | 6 | Soul |
| Abba | Abbas Greatest Hits | 6 | Seventies |
| NULL | NULL | 7 | Eighties |
| NULL | NULL | 8 | Hip Hop |
| NULL | NULL | 9 | Jazz |
| NULL | NULL | 10 | Guitar Bands |
+--------------------+------------------------------+---------+----------------+
19 rows in set (0.00 sec)
Note that where there aren't any cds in a genre then a NULL value is returned. This is because every record of the RIGHT side must be returned at least once by the RIGHT JOIN.
Adding a WHERE Clause to our Join
Now we have the join occurring out with the WHERE clause, we can begin to add other conditions. For example if we want to select only the pop CDs
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> WHERE genres.genre = 'pop';
+----------+----------+-------+
| artist | title | genre |
+----------+----------+-------+
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
+----------+----------+-------+
2 rows in set (0.00 sec)
The USING Clause
A variation on the Left Join is the 'USING' clause. You can use this if the columns you are carrying out the join on have the same name.
Syntax:
SELECT <column_name> FROM <Table1> LEFT JOIN <Table2> USING (<column_name>)
In our example we are joining the tables where cds.genreID is the same as genres.genreID thus genreID is the name of a column in BOTH of tables we are using for the join.
mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> USING (genreID);
+-------------------+------------------------------+----------------+
| artist | title | genre |
+-------------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Abba | Abbas Greatest Hits | Seventies |
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
| Various | Eurovision Song contest 2001 | Easy Listening |
+-------------------+------------------------------+----------------+
5 rows in set (0.00 sec)
Mini Exercise
Practice joining the 'CDs' and 'genre' tables to retrieve different data about the CDs, e.g.
mysql> SELECT cds.artist, genres.boughtby
-> FROM cds
-> LEFT JOIN genres
-> USING (genreID);
+-------------------+------------------------------------------------+
| artist | boughtby |
+-------------------+------------------------------------------------+
| Jamiroquai | White Boys in thin leather ties |
| Abba | Those not born til 1980 |
| Various | Little girls and adults who should know better |
| westlife | Little girls and adults who should know better |
| Various | Crushed velvet wearing lounge lizards |
+-------------------+------------------------------------------------+
5 rows in set (0.00 sec)
Preparing to Join more than two tables
It is also possible to join more than two tables. In order to do this, however, we will need to make a third table - this time an 'artist' table containing the artistID and name. This will use a lot of the techniques we've used in the previous workshops, so you may have to refer back to refresh your memory as to what you are doing, which will serve as more good revision. We are going to create our new table using a five stage process
- CREATE the new table
- INSERT our artist data using a SELECT DISTINCT query
- ALTER TABLE to add a new 'artistID' COLUMN to the cds TABLE
- UPDATE the cds.artistID fields to match the values assigned to an artist in the new table
- DROP the artist column from the cds TABLE
We'll go through each step in more detail
CREATE the new table
A simple enough revision of what we've done before.
mysql> CREATE TABLE artists (
-> artistID int(3) auto_increment primary key,
-> Artist varchar(20)
-> );
INSERT our artist data using a SELECT DISTINCT query
This uses similar syntax to that in Workshop Four. We are going to insert the artists from the cds table into the artist.Artist column.
mysql> INSERT INTO artists (artists.Artist)
-> SELECT DISTINCT cds.artist
-> FROM cds;
You can check this has worked by using a (by now) standard SELECT statement.
mysql> SELECT *
-> FROM artists;
ALTER TABLE to add a new 'artistID' COLUMN to the cds TABLE
As we are going to refer to the artist by their artistID rather than their name, we need to create a column in the cds table to hold that ID.
mysql> ALTER TABLE cds
-> ADD artistID int(3);
Check this has worked with a describe statement.
mysql> DESCRIBE cds; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | cdID | int(3) | | PRI | NULL | auto_increment | | artist | varchar(20) | YES | | NULL | | | title | varchar(30) | YES | | NULL | | | year | int(4) | YES | | NULL | | | label | varchar(20) | YES | | NULL | | | bought | date | YES | | NULL | | | tracks | int(2) | YES | | NULL | | | genreID | int(2) | YES | | NULL | | | artistID | int(3) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec)
UPDATE the cds.artistID fields without Joins
For those with access to older versions of MySQL this will have to be done by hand. If you are lucky enough to have MySQL 4.0 installed, then you could use UPDATE joins to speed up this stage, but for consistency between versions MySQL I will demonstrate the method common to older and newer versions of MySQL.
mysql> UPDATE cds
-> SET cds.artistID = 1
-> WHERE (cds.artist = 'Jamiroquai');
......etc usng all the artistIDs from the artists table. Check that this has worked with a few joins between the cds and artists TABLEs eg
mysql> SELECT artists.Artist, cds.title
-> FROM artists
-> LEFT JOIN cds
-> USING (artistID)
-> WHERE (cds.artistID = 1);
+------------+----------------+
| name | title |
+------------+----------------+
| jamiroquai | A Funk Odyssey |
+------------+----------------+
DROP the artist column from the cds TABLE
As we can retrieve the artist name by using a join and the artistID we can remove the cds.artist column.
mysql> ALTER TABLE cds
-> DROP artist;
Query OK, 16 rows affected (0.15 sec)
Records: 16 Duplicates: 0 Warnings: 0
We are now ready to join all three tables together.
Joining Three Tables
Before we start with statements let's just recap what we can expect to do. We have a cds table that contains the foreign keys (i.e. values that correspond to primary keys in another table) called cds.genreID and cds.artistID which also exist in the genres and artists tables. A three table join can be achieved using another version of the Equi-Join or Inner Join where we can use the WHERE clause to limit the returned records based on comparing the artistID and the genreID.
mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds, artists, genres
-> WHERE (
-> (cds.artistID = artists.artistID)
-> AND (cds.genreID = genres.genreID)
-> );
+-----------------+------------------------------+----------------+
| name | title | genre |
+-----------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Abba | Abbas Greatest Hits | Seventies |
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
| Various | Eurovision Song contest 2001 | Easy Listening |
+-----------------------------------------------------------------+
The problem with this is, once more, that we already have a fairly complex WHERE clause just to join ALL the records properly. Ideally what we want is to have a LEFT / RIGHT JOIN, but this is a problem as we cannot compare just two tables (Right vs Left). The solution to this is to use a series of joins. This is where one join is made, but rather than being used to evaluate and display the data from it, the result is passed to a second join and THEN the data can be displayed. A good way to think of this is that the first JOIN creates a virtual table (from joining tables one and two) which can then be joined to the third table.
So let us first make a LEFT JOIN between the 'cds' and 'genres' tables.
mysql> SELECT cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID;
+------------------------------+----------------+
| title | genre |
+------------------------------+----------------+
| A Funk Odyssey | Soul |
| Abbas Greatest Hits | Seventies |
| Now 49 | Pop |
| westlife | Pop |
| Eurovision Song contest 2001 | Easy Listening |
+------------------------------+----------------+
5 rows in set (0.02 sec)
Next we add another JOIN and SELECT the artists.Artist as well. The order in which you SELECT COLUMNs determines how your results will look, so we should place artists.Artist first.
mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID;
+------------+------------------------------+----------------+
| name | title | genre |
+------------+------------------------------+----------------+
| Jamiroquai | A Funk Odyssey | Soul |
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
| Various | Eurovision Song contest 2001 | Easy Listening |
| Abba | Abbas Greatest Hits | Seventies |
+------------+------------------------------+----------------+
5 rows in set (0.01 sec)
We can now add a WHERE clause to restrict the output.
mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> WHERE (genres.genre = 'Pop');
+----------+----------+-------+
| name | title | genre |
+----------+----------+-------+
| Various | Now 49 | Pop |
| westlife | westlife | Pop |
+----------+----------+-------+
2 rows in set (0.01 sec)
We could also join a fourth table, imagine we were to repeat the steps above to create a 'label' table. We could issue the a statement to join all four tables and display the results.
mysql> SELECT artists.Artist, cds.title, label.Label, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> LEFT JOIN label
-> ON cds.labelID = label.labelID;
+------------+------------------------------+------------+----------------+
| Artist | title | Label | genre |
+------------+------------------------------+------------+----------------+
| Jamiroquai | A Funk Odyssey | Sony soho2 | Soul |
| Various | Now 49 | virgin | Pop |
| westlife | westlife | jive | Pop |
| Various | Eurovision Song contest 2001 | EMI | Easy Listening |
| Abba | Abbas Greatest Hits | EMI | Seventies |
+------------+------------------------------+------------+----------------+
5 rows in set (0.01 sec)
This method of adding tables and performing JOINS will work only if one TABLE has all the foreign keys. Joins CAN be more complex mixing types etc, but I don't think it is necessary to go into that here.
UPDATE and DELETE Joins
Requires MySQL 4.0 or later.
As MySQL 4.0 has been deemed to be stable enough for production use it is worth mentioning some of the functionality that has been added and significantly this includes UPDATE and DELETE joins. To find out the version of MySQL that you have access to you are usually told when you login at the command-line:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2471 to server version: 4.0.12 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
But you can also check from the command-line using a 'STATUS' command:
mysql> STATUS -------------- mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i586) Connection id: 2563 Current database: Current user: kbrown@localhost SSL: Not in use Current pager: stdout Using outfile: '' Server version: 4.0.12
If you do indeed have access to MySQL 4.0 then you can continue with this workshop.
UPDATE Joins
Before looking at the syntax let's pause for a moment and think why we would want a to perform a Join as part of a UPDATE statement? Well the answer is to update more than one table using only one statement or indeed to insert the values from one table into another.
The syntax for UPDATE joins is very similar to a normal UPDATE statement only including one of the Joins specified above in much the same way as the select statement did. Thus to use an Equi-Join in the statement, we specify the tables together, what we want to SET and then use a WHERE condition to apply the constraint.
UPDATE <table1>, <table2> SET <column_name> = 'new_value' WHERE <conditions>
For Example let's temporarily change the values of the 'A Funk Odyssey' album so that it was recorded by 'George Clinton' (see bio if you're confused), and called 'The Funkship Odyssey' (one of the lesser known works ;-). Thus we have to update the 'cds' (to change the title) and the 'artists' table, use one condition to constrain the join (cds.artistID = artists.artistID) and then a final condition to only effect that row (and not all rows).
mysql> UPDATE cds, artists
-> SET
-> cds.title = 'The Funkship Odyssey',
-> artists.Artist = 'George Clinton'
-> WHERE (artists.artistID = cds.artistID)
-> AND (cds.cdID = '2');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
You can use a SELECT join described above to view how this has changed the values.
mysql> SELECT artists.Artist, cds.title
-> FROM artists
-> LEFT JOIN cds
-> USING (artistID)
-> WHERE (cds.artistID = 1);
+----------------+----------------------+
| name | title |
+----------------+----------------------+
| George Clinton | The Funkship Odyssey |
+----------------+----------------------+
1 row in set (0.00 sec)
Of course by replacing 'Jamiroquai' in the 'artists' table it now means that George has recorded all their albums and since George didn't record 'The Funkship Odyssey' we'd better put things back the way they were. This time we will use a LEFT JOIN to UPDATE the values. In a SELECT statement the LEFT JOIN allowed us to join the tables before applying any constraints and in an UPDATE join this is no different. First we make the LEFT JOIN, then use SET to provide new and finally use the WHERE condition to restrict which rows this effects.
mysql> UPDATE cds->LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> SET
-> cds.title = 'A Funk Odyssey',
-> artists.name = 'Jamiroquai'
-> WHERE (cds.cdID = '2');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
The other purpose of the UPDATE join is to set the value of a column in one table to that of a column in another table. This isn't particularly spectacular as any column value in a table can be set to the value of another column in the same row and as any join results in a 'big virtual table' this can be done also.
To illustrate let's recreate the artist field in the cds table that we deleted above and repopulate the column using an UPDATE join. First modify the cds table:
mysql> ALTER TABLE cds
-> ADD artist VARCHAR(20);
Next set a value of 'Unknown' for each row.
mysql> UPDATE cds
-> SET artist = 'Unknown';
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> SELECT cds.artist, cds.title
-> FROM cds;
+---------+------------------------------+
| artists | title |
+---------+------------------------------+
| Unknown | A Funk Odyssey |
| Unknown | Now 49 |
| Unknown | westlife |
| Unknown | Eurovision Song contest 2001 |
| Unknown | Abbas Greatest Hits |
+---------+------------------------------+
5 rows in set (0.01 sec)
Next we will use an UPDATE Join to merge with the 'artists' table and SET the value of the of cds.artist = artists.Artist. Let's only do one artist (Jamiroquai again) to see how this works.
mysql> UPDATE cds
-> LEFT JOIN artists
-> USING (artistID)
-> SET cds.artists = artists.Artist
-> WHERE (cds.cdID = '2');
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT cds.artists, cds.title
-> FROM cds;
+------------+------------------------------+
| artists | title |
+------------+------------------------------+
| Jamiroquai | A Funk Odyssey |
| Unknown | Now 49 |
| Unknown | westlife |
| Unknown | Eurovision Song contest 2001 |
| Unknown | Abbas Greatest Hits |
+------------+------------------------------+
5 rows in set (0.01 sec)
We can rerun the query without the final WHERE condition that constrains the row and all the artists will be correctly identified by the join.
mysql> UPDATE cds
-> LEFT JOIN artists
-> USING (artistID)
-> SET cds.artists = artists.name;
Query OK, 8 rows affected (0.02 sec)
Rows matched: 9 Changed: 8 Warnings: 0
mysql> SELECT cds.artists, cds.title
-> FROM cds;
+-------------+------------------------------+
| artists | title |
+-------------+------------------------------+
| Jamiroquai | A Funk Odyssey |
| Various | Now 49 |
| westlife | westlife |
| Various | Eurovision Song contest 2001 |
| Abba | Abbas Greatest Hits |
+-------------+------------------------------+
5 rows in set (0.01 sec)
Notice: These last two examples have included a USING clause as part of the Join.
DELETE Joins
The final Join that I am going to discuss in this workshop is the DELETE Join. This is pretty much a case of 'same again' so I'll only give a quick examples in which I'll delete Westlife who have an artistID = '3' (if only it were that easy ;->.
To DELETE from just the 'cds' table include only the 'cds' table between DELETE and FROM (the join is made after FROM so both tables are needed there).
mysql> DELETE cds
-> FROM cds, artists
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');
To DELETE from both tables:
mysql> DELETE cds, artist
-> FROM cds, artist
-> WHERE (cds.artistID = artists.artistID)
-> AND (cds.artistID = '3');
Phew.. I bet you're glad that's over. ;-)