Critical Skill 5.3 Join Data in Multiple Tables


Sometimes, the data you need for a report exists in more than one table. When you select data from more than one table, it is called joining . One way to show you are selecting fields from specific tables is to specify both the table and field name in the command, separated by a period: table_name . field_name. Alternatively, you can use one of several JOIN functions. This section covers Cartesian products, simple or INNER JOINS, and the LEFT JOIN, and RIGHT JOIN functions.

When you are joining data from more than one table and selecting a field from a table, you must list that table in the FROM clause of the SELECT command. If any of the fields within the tables you are selecting happen to have the identical field name (such as cust_num), you must specify the table name, and then the desired field name, separated by a period, as shown in the previous paragraph.

Understanding Cartesian Product vs. Simple Join Operations

A Cartesian join is when every field in one table is joined with every field in another table. For instance, if a table called here held the values One and Two, and a table called there held the values a, b, and c, a Cartesian join of those two tables would result in the report shown in Figure 5-18. The information repeats itself until all the permutations of the join are covered. Because there is so much repetition of data, this type of join is rarely useful.


Figure 5-18: A Cartesian join

The following example shows a simple joining of two tables without using a formal JOIN function:

 SELECT here.stuff, there.stuff FROM here, there WHERE here.Ndex=there.Ndex; 

The results, shown in the first example in Figure 5-19, use the primary key fields called Ndex in each table to match up the data in the fields called stuff. The third entry in the there table, the value c, does not appear in the results, because there is not a corresponding third value and resulting matching index number in the here table.


Figure 5-19: Results from simple joins

The second example in Figure 5-19 shows the result of the same code after a third field, Three, is added to the here table. The command is the same, but now the tables have the same number of matching records, so the results differ . This type of join risks a result with incomplete data, unless you know without question that the tables involved are of equal size .

Figure 5-20 shows how the same result can be achieved using the INNER JOIN function with its ON clause replacing the WHERE clause in the previous example. Using a formal JOIN function this way has the advantage of better communicating your intentions to subsequent maintainers of the code.


Figure 5-20: Using an INNER JOIN function
 SELECT here.stuff, there.stuff FROM here INNER JOIN there ON here.Ndex=there.Ndex; 

These examples join tables that have identical column names , and so they require the table_name . field_name syntax to make their meaning clear to MySQL. As shown in Figure 5-21, if the stuff column in the there table is renamed junk, those fields no longer need to have the table specified in the SELECT command. However, since both tables still have a key field named Ndex, the table must be specified when they are used in the ON clause. You can choose to use the table specification syntax only when necessary for the sake of brevity in the code, or you can always use it to make the intentions of the code clearer and more consistent.


Figure 5-21: Specifying the table name only when necessary

Deciding Whether to Use LEFT JOIN or RIGHT JOIN

Continuing with the examples from the previous section, Figure 5-22 shows what happens when a fourth value, d, has been added to the junk table, causing junk and stuff to once again contain a different number of rows. In the first example in Figure 5-22, the results of the LEFT JOIN is identical to the results of the previous INNER JOIN. The value d is left off of the results, because it has no corresponding index value in the other table.


Figure 5-22: Using RIGHT and LEFT JOIN on noncorresponding tables

In order to make it show in the results, you must use either a LEFT JOIN or RIGHT JOIN, depending on how the fields and tables are arranged in the commands. In the second example in Figure 5-22, the JOIN has been changed from an LEFT JOIN to a RIGHT JOIN. Since the here table is listed first in the SELECT line, the there table is on its right, and since the there table has more rows, a RIGHT JOIN instructs MySQL to place a NULL value in the table across from any value in the right table that has no corresponding value in the left table.

The LEFT JOIN works in precisely the opposite way, as demonstrated in the third example in Figure 5-22. In this example, the here and there tables have swapped sides, necessitating a LEFT JOIN to have the value d to appear in the result.

Deciding whether to use LEFT JOIN or RIGHT JOIN to mold your information into its desired form depends on how the information is stored in its tables and how the desired format of the results differs from the original format. Often, you will need to experiment to get the results you want. The best way to understand how the JOIN commands work is to try them in simple ways, which will provide the insight you need to know how to use them in more complex combinations.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

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