9.9     Table joins

This is NOT the latest copy of this book; click here for the latest version.

Joining two tables together in one query requires large, often complicated SQL statements because there are many ways to perform such joins. For example, do you match each table row for row and ignore any rows which do not match?

Very often you can use a simple join query to get what you want, which avoids the hassle of more complicated operations. Simple joins are good for combining two tables together when you know each row from table A will have one matching row in table B. For example, each company ID in an employees table would match one company in a companies table.

Consider the following schema: we've got a golfmembers table and a golfcentres table. Each member in golfmembers has a CentreNum ID number that stores the golf centre where they usually play, as well as their name.

Here is how that table looks:

The golfcentres table simply contains an ID number for each golf centre, as well as the name of each centre, like this:

The question is, how do you get MySQL to print out a list of all members, except with the name of their centre rather than just the number? The answer is that you need to join the two tables together, using a query like this:

That is a lot more complicated than the SQL statements used so far, but do not worry - it all make sense. First things first - look at the FROM part of the query - there is "Members m, GolfCentres c". This is known as table aliasing in SQL, and allows you to refer to a table as a different name for use in your query. This is important for two reasons: firstly, table aliases are usually shorter (I have used m and c rather than Members and GolfCentres), and secondly because you need to specify which table each field comes from so that MySQL does not get confused.

Look at the fields the query is selecting - notice that when designating fields to read, they are prefixed with the alias of the table we want to select from and a full-stop character . - this is enables MySQL to resolve any ambiguity without problem.

The WHERE statement is also important - it tells MySQL that we want to match the Centre Number from the members table against the ID number of each golf centre from the GolfCentres table.

Here is how things look once we use the new query:

As you can see, with just one query we've combined the two sets of data together to provide much more meaningful results. Also note that MySQL lets you keep making your statement longer and longer, even separating it with new lines (as in the picture) until you use a semi-colon.

Using the simple join technique detailed above, you can join two, three, four or even more tables together as you need. Generally, though, two tables should be more than enough.