Using JOIN

Using JOIN

Several types of JOINs can be used in MySQL, all of which refer to the order in which the tables are put together and the results are displayed. The type of JOIN used with the fruit and color tables is called an INNER JOIN, although it wasn't written explicitly as such. To rewrite the SQL statement using the proper INNER JOIN syntax, you would use

 mysql> select fruitname, colorname from fruit inner join color     -> on fruit.id = color.id; +-----------+-----------+ | fruitname | colorname | +-----------+-----------+ | apple     | red       | | orange    | orange    | | grape     | purple    | | banana    | yellow    | +-----------+-----------+ 4 rows in set (0.00 sec) 

The ON clause replaced the WHERE clause, in this instance telling MySQL to join together the rows in the tables where the IDs match each other. When joining tables using ON clauses, you can use any conditions that you would use in a WHERE clause, including all the various logical and arithmetic operators.

Another common type of JOIN is the LEFT JOIN. When joining two tables with LEFT JOIN, all rows from the first table will be returned, no matter if there are matches in the second table or not. Suppose you have two tables in an address book, one called master_name, containing basic records, and one called email, containing email records. Any records in the email table would be tied to a particular id of a record in the master_name table. For example

 mysql> select name_id, firstname, lastname from master_name; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ |       1 | John      | Smith    | |       2 | Jane      | Smith    | |       3 | Jimbo     | Jones    | |       4 | Andy      | Smith    | |       7 | Chris     | Jones    | |      45 | Anna      | Bell     | |      44 | Jimmy     | Carr     | |      43 | Albert    | Smith    | |      42 | John      | Doe      | +---------+-----------+----------+ 9 rows in set (0.00 sec) mysql> select name_id, email from email; +---------+------------------+ | name_id | email            | +---------+------------------+ |      42 | jdoe@yahoo.com   | |      45 | annabell@aol.com | +---------+------------------+ 2 rows in set (0.00 sec) 

Using LEFT JOIN on these two tables, you can see that if a value from the email table doesn't exist, NULL will appear in place of an email address:

 mysql> select firstname, lastname, email fom master_name left join email     -> on master_name.name_id = email.name_id; +-----------+----------+------------------+ | firstname | lastname | email            | +-----------+----------+------------------+ | John      | Smith    | NULL             | | Jane      | Smith    | NULL             | | Jimbo     | Jones    | NULL             | | Andy      | Smith    | NULL             | | Chris     | Jones    | NULL             | | Anna      | Bell     | annabell@aol.com | | Jimmy     | Carr     | NULL             | | Albert    | Smith    | NULL             | | John      | Doe      | jdoe@yahoo.com   | +-----------+----------+------------------+ 9 rows in set (0.01 sec) 

A RIGHT JOIN works like LEFT JOIN, but with the table order reversed. In other words, when using a RIGHT JOIN, all rows from the second table will be returned, no matter whether there are matches in the first table or not. However, in the case of the master_name and email tables, there are only two rows in the email table, whereas there are nine rows in the master_name table. This means that only two of the nine rows will be returned:

 mysql> select firstname, lastname, email from master_name right join email     -> on master_name.name_id = email.name_id; +-----------+----------+------------------+ | firstname | lastname | email            | +-----------+----------+------------------+ | John      | Doe      | jdoe@yahoo.com   | | Anna      | Bell     | annabell@aol.com | +------+-----+----------------------------+ 2 rows in set (0.00 sec) 

Several different types of JOINs are available in MySQL, and you've learned about the most common types. To learn more about JOINs such as CROSS JOIN, STRAIGHT JOIN and NATURAL JOIN, please visit the MySQL Manual at http://www.mysql.com/doc/J/O/JOIN.html.



Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
Sams Teach Yourself PHP, MySQL and Apache in 24 Hours
ISBN: 067232489X
EAN: 2147483647
Year: 2005
Pages: 263

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net