Loading....
Coupon Accepted Successfully!

 

JOINS

Now lets work with multiple tables and see how information can be pulled out of the data.

mysql> SELECT names.contact_id, FirstName, LastName, Email FROM names, email WHERE names.contact_id = email.contact_id;
+------------+-----------+----------+-------------------+
| contact_id | FirstName | LastName | Email |
+------------+-----------+----------+-------------------+
| 1 | Yamila | Diaz | [email protected] |
| 2 | Nikki | Taylor | [email protected] |
| 3 | Tia | Carrera | [email protected] |
+------------+-----------+----------+-------------------+
3 rows in set (0.11 sec)

mysql> SELECT DISTINCT names.contact_id, FirstName, Email, TelephoneWork FROM names, email, telephones WHERE names.contact_id=email.contact_id=telephones.contact_id;
+------------+-----------+-------------------+---------------+
| contact_id | FirstName | Email | TelephoneWork |
+------------+-----------+-------------------+---------------+
| 1 | Yamila | [email protected] | 333-60000 |
| 2 | Nikki | [email protected] | 333-60000 |
| 3 | Tia | [email protected] | 333-60000 |
+------------+-----------+-------------------+---------------+
3 rows in set (0.05 sec)

 

So what's a JOIN ?

JOIN is the action performed on multiple tables that returns a result as a table. It's what makes a database 'relational'.

There are several types of joins. Let's look at LEFT JOIN (OUTER JOIN) and RIGHT JOIN

Let's first check out the contents of the tables we're going to use-

 

mysql> SELECT * FROM names;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM email;
+------------+-------------------+
| contact_id | Email |
+------------+-------------------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+------------+-------------------+
3 rows in set (0.00 sec)

 

LEFT JOIN

 

mysql> SELECT * FROM names LEFT JOIN email USING (contact_id);
+------------+-----------+----------+------------+------------+------------------+
| contact_id | FirstName | LastName | BirthDate | contact_id | Email|
+------------+-----------+----------+------------+------------+-------------------+
| 3 | Tia | Carrera | 1975-09-18 | 3 | [email protected] |
| 2 | Nikki | Taylor | 1972-03-04 | 2 | [email protected] |
| 1 | Yamila | Diaz | 1974-10-13 | 1 | [email protected] |
+------------+-----------+----------+------------+------------+-------------------+
3 rows in set (0.16 sec)

 

To find the people who have a home phone number.

 

mysql> SELECT names.FirstName FROM names LEFT JOIN telephones ON names.contact_id = telephones.contact_id WHERE TelephoneHome IS NOT NULL;
+-----------+
| FirstName |
+-----------+
| Tia |
| Nikki |
| Yamila |
+-----------+
3 rows in set (0.02 sec)

 

These same query leaving out 'names' (from names.FirstName) is still the same and will generate the same result.

mysql> SELECT FirstName FROM names LEFT JOIN telephones ON names.contact_id = telephones.contact_id WHERE TelephoneHome IS NOT NULL;
+-----------+
| FirstName |
+-----------+
| Tia |
| Nikki |
| Yamila |
+-----------+
3 rows in set (0.00 sec)

 

RIGHT JOIN

 

mysql> SELECT * FROM names RIGHT JOIN email USING(contact_id);
+------------+-----------+----------+------------+------------+----------------- --+
| contact_id | FirstName | LastName | BirthDate | contact_id | Email |
+------------+-----------+----------+------------+------------+-------------------+
| 1 | Yamila | Diaz | 1974-10-13 | 1 | [email protected] |
| 2 | Nikki | Taylor | 1972-03-04 | 2 | [email protected]
|
| 3 | Tia | Carrera | 1975-09-18 | 3 | [email protected]
|
+------------+-----------+----------+------------+------------+-----------------
--+
3 rows in set (0.03 sec)

 

BETWEEN

This conditional statement is used to select data where a certain related contraint falls between a certain range of values. The following example illustrates it's use.

 

mysql> SELECT * FROM names;
+------------+-----------+----------+------------+
| contact_id | FirstName | LastName | BirthDate |
+------------+-----------+----------+------------+
| 3 | Tia | Carrera | 1975-09-18 |
| 2 | Nikki | Taylor | 1972-03-04 |
| 1 | Yamila | Diaz | 1974-10-13 |
+------------+-----------+----------+------------+
3 rows in set (0.06 sec)

mysql> SELECT FirstName, LastName FROM names WHERE contact_id BETWEEN 2 AND 3;
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Tia | Carrera |
| Nikki | Taylor |
+-----------+----------+
2 rows in set (0.00 sec)

 





Test Your Skills Now!
Take a Quiz now
Reviewer Name