Section 12.0. Introduction


12.0. 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 is similar to performing a join between different tables, except that you must use table aliases to disambiguate table references.


Using unions to combine result sets

For some queries, the required information consists of multiple result sets, either selected from different tables or selected in different ways from the same table. To produce such a result, use a UNION that combines the result sets from multiple SELECT statements.


Deleting unmatched rows

If two related datasets have an imperfect relationship, you can determine which rows are unmatched and remove them if they are unwanted.


Performing joins between tables that are not in the same database

When you use multiple tables, they might come from the same database or from different databases. On occasion, you may even need to use tables that come from databases hosted by different MySQL servers. For the first two cases, you need to know how to refer to columns from the different tables, which may involve using table aliases or qualifying table names with a database name. In the third case, you can set up a FEDERATED table to enable one MySQL server to access a table hosted by another or open a connection to each server and combine the information from them yourself.

The scripts that create the tables used in this chapter can be found in the tables directory. For scripts that implement some of the techniques discussed here, look in the joins and unions directories.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net