MySQL Cookbook, 2nd Edition

Introduction

For the most part, recipes in earlier chapters have used single tables. But for any application of even moderate complexity, it’s likely that you’ll need to use multiple tables. Some questions simply cannot be answered using a single table, and the real power of a relational database comes into play when you start to combine the information from multiple sources. There are several reasons to use multiple tables:

• To combine rows from tables to obtain more comprehensive information than can be obtained from individual tables alone

• To hold intermediate results for a multiple-stage operation

• To modify rows in one table based on information from another

A statement that uses multiple tables can be a join between tables, a subquery that nests one SELECT within another, or a union that combines the results of multiple SELECT statements. Subqueries have already been touched on in earlier chapters to some extent. In this chapter, the primary focus is on joins and unions, although subqueries occur on occasion as well. The following topics are covered here:

Joining tables to find matches or mismatches between rows in different tables

To solve such problems, you should know which types of joins apply. Inner joins show which rows in one table are matched by rows in another. Outer joins show matching rows, but they can also be used to find which rows in one table are not matched by rows in another.

Comparing a table to itself

Some problems require that you compare a table to itself. This ...