MySQL Cookbook, 4th Edition

Chapter 16. Using Joins and Subqueries

16.0 Introduction

Most queries in earlier chapters used a single table, but for any application of even moderate complexity, you’ll likely 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 combine the information from multiple sources:

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

• Hold intermediate results for a multiple-stage operation

• Modify rows in one table based on information from another

This chapter focuses on two types of statements that use multiple tables: joins between tables and subqueries that nest one SELECT within another. It covers the following topics:

Comparing tables to find matches or mismatches

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

Deleting unmatched rows

If two datasets are related, but imperfectly, you can determine which rows are unmatched and remove them as necessary.

Comparing a table to itself

Some problems require comparing a table to itself. This is similar to performing a join between different tables, except that you must use table aliases to disambiguate table references.

Producing candidate-detail and many-to-many relationships

Joins enable the production of ...