Joining Tables

When accessing information within database tables, we may sometimes need to look at data from two or more different tables, as we saw in the previous example. There is another useful way to access relational data, using a technique called joining. When we say "joining," we mean that we compare actual fields by their names in different tables, rather than the actual data within them (although when the statement is executed the actual data is compared).

For an example of how we can use joining, let's look back to the previous example. Let's say that we wanted to retrieve a detailed listing of Henry's enemies and not just their names; in the previous example, we would first execute this statement:

SELECT enemy FROM relateenemies WHERE player = 'Henry'

Then, for each name on the retrieved list, we would call the following SQL statement:

SELECT * FROM playerdata WHERE username = '????';     (where ???? represents each name on the list in turn)

With the joining technique, however, this can be done in a single SQL statement, meaning less code. It will also execute faster (rather than using two statements). Here is the join statement we would require to do this:

mysql> SELECT * FROM playerdata, relateenemies WHERE     playerdata.username = relateenemies.enemy AND     relateenemies.player = 'Henry';

When we execute this statement, we can expect the following output in the MySQL console window:

Figure 15-52: Using a join statement

So first we are selecting all the data from both the playerdata and relateenemies tables and then placing a condition upon the joining of the tables, so that data will only be selected where the username field in the playerdata table is equal to the enemy field in the relateenemies table. If we just left it with the single condition, it would return the full results from both tables for each enemy in the relateenemies table. So our second condition limits the results to only show Henry's enemies by comparing the player field in the relateenemies table to the string Henry. Useful, eh?