Chapter 14. Using Joins and Subqueries
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:
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
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 master-detail and many-to-many relationships
Joins enable production of ...
Get MySQL Cookbook, 3rd 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.