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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.