Because MySQL is a relational database system, the relationships between tables are important, and you can take advantage of these relationships my using joins. There are a number of different ways to join tables and we'll take a look at the two most popular types - a generic join and a left join. Note that we will be demonstrating joins with two tables, but you can join as many tables as you like. Be careful though, because a badly constructed join can return the number of rows in each table, multiplied together. This isn't much of an issue if you have 100 rows per table (10000 rows returned), but when you have 1.5 million rows per table and return 2,250,000,000,000 rows, you can expect your database server to crash pretty quickly.

First we need a new second table to demonstrate joins with. We'll call this table "orders":

INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('1', '34.99')
INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('2', '17.12')
INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('1', '25.86')
INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('3', '41.98')
INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('1', '34.99')
INSERT INTO `order`(`customerid`, `orderprice`) VALUES ('2', '9.99')

Now that we have some orders set up for our customers (we can see the relationship between the customers and the orders is `customerid` in both tables), we can use a join to get a list of customers with their order numbers and prices:

SELECT `orders.ordernumber`, `customers.customername`, `orders.orderprice` FROM `orders`, `customers` WHERE `customers.customerid` = `orders.customerid`

In this SQL statement, we have indicated we want to do a generic join by selecting from two tables (FROM `orders`, `customers`) and that we would like to join the rows together where they have the same customerid (WHERE `customers.customerid` = `orders.customerid`). Note that in order to prevent ambiguity, we now prefix column names with the name of the table which contain them. If we run this SQL statement, we get the following results

Left joins are very similar to generic joins, however preserve every entry in the first table (duplicating if necessary).

SELECT `orders.ordernumber`, `customers.customername`, `orders.orderprice` FROM `customers` LEFT JOIN `orders` WHERE `customers.customerid` = `orders.orderid` ORDER BY `customername` ASC

Notice that the second row of these results does not have an `ordernumber` or an `orderprice` as our examples did not include any orders for the fourth customer we had created. There is also a "RIGHT JOIN" in MySQL which works in the same way (but with the rows preserved for the second table). The MySQL documentation recommends only using LEFT JOIN for portability and coherence. For more information on joins, you can read the MySQL Join Reference

If you have anything to contribute to this tutorial, found a bug, or know a better way of achieving the same goal, please leave your response below.