Sunday, August 21, 2011

MySQL Joins


Using MySQL, Joins

Lets consider we have one cds table and one generes tables
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 | |
+--------+-------------+------+-----+---------+----------------+
mysql> DESCRIBE generes;
genreIDUnique Identifier01
genreMusic GenreHeavy Metal
boughtbyType of person that buys this musicGreasy Haired Bikers
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)

Joins

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.

idanimal
1Cat
2Dog
3Cow

idFood
1Milk
2Bone
3Grass

A cross-join on these tables would produce the following result.
1Cat1Milk
1Cat2Bone
1Cat3Grass
2Dog1Milk
2Dog2Bone
2Dog3Grass
3Cow1Milk
3Cow2Bone
3Cow3Grass

Where every row from one table is joined to every row in the other table. 

The Equi-Join or Inner Join

When we want ONLY the matching data from both tables to be returned.

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.
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)
WHERE condition, that forces only the rows in the second table RELEVANT to the rows in the first table to be retrieved.




The Left Join


When we want ALL the data from Left table (even unmatching) with right table.

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.
// set genreID to any number that is not there in cds table - that will never match
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 pur Join

mysql> SELECT cds.artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> WHERE genres.genre = 'pop'; 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>)
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)
Preparing to Join more than two tables
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)
-> ); 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);
Joining Three Table

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 |
+-----------------------------------------------------------------+
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)
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;
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.

UPDATE Joins


UPDATE <table1>, <table2>
SET <column_name> = 'new_value'
WHERE <conditions>
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 mysql> UPDATE cds -> LEFT JOIN artists -> USING (artistID) -> SET cds.artists = artists.Artist -> WHERE (cds.cdID = '2'); Notice: These last two examples have included a USING clause as part of the Join. DELETE Joins 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');

No comments:

Post a Comment