JOIN

Allows data from more than one table to be output as the result set.

 

Consider the two tables in the same database:

customers table:

mysql> select * from customers;
+--------+-------+---------+------+
| CustID | First | Surname | Age  |
+--------+-------+---------+------+
|      1 | Pam   | Frost   |   21 |
|      2 | James | Cross   |   20 |
|      3 | Andy  | Jones   |   34 |
+--------+-------+---------+------+
3 rows in set (0.00 sec)

 orders table

mysql> select * from orders;
+---------+--------+---------+------+
| OrderID | CustID | Part    | Cost |
+---------+--------+---------+------+
|       1 |      3 | CPU     |  975 |
|       2 |      1 | RAM     |  433 |
|       3 |      2 | SSD     |  768 |
|       4 |      3 | Monitor |  250 |
+---------+--------+---------+------+
4 rows in set (0.00 sec)

 

It can be seen that the orders table has a CustID field, that relates to the customers table.

 

We can therefore use this to obtain specific data from the two tables, for instance the orders relating to just a specific customer:

mysql> SELECT orders.OrderID, orders.Part, orders.Cost FROM customers, orders WHERE customers.Surname = 'Jones' AND customers.CustID = orders.CustID;
+---------+---------+------+
| OrderID | Part    | Cost |
+---------+---------+------+
|       1 | CPU     | 975  |
|       4 | Monitor | 250  |
+---------+---------+------+
2 rows in set (0.00 sec)

 

 

In this case a JOIN has been made (although not explicitly stated) by use of the comma , between the two tables specified after FROM.

 

The comma , is short hand equivalent to INNER JOIN:

mysql> select orders.OrderID, orders.Part, orders.Cost from customers INNER JOIN orders where customers.Surname = 'Jones' and customers.CustID = orders.CustID;
+---------+---------+------+
| OrderID | Part    | Cost |
+---------+---------+------+
|       1 | CPU     |  975 |
|       4 | Monitor |  250 |
+---------+---------+------+
2 rows in set (0.00 sec)

 

 

The Surname field of the customers table is then used to query the data WHERE it equals 'Jones' and each table has the same CustID.

 

 

SELECT orders.OrderID, orders.Part, orders.Cost FROM customers, orders WHERE customers.Surname = 'Jones' AND customers.CustID = orders.CustID ;

Leave a Reply