Applying normalization to relational databases tends to promote better accuracy of queries, but it also leads to queries that take a little more work to develop, as the data may be spread amongst several tables. In today's article, we'll learn how to fetch data from multiple tables by using joins.

Normalization is almost universally applied to relational databases such as MySQL in order to optimize tables for general-purpose querying and to rid them of certain undesirable characteristics that could lead to a loss of data integrity. Doing so tends to promote better accuracy of queries, but it also leads to queries that take a little more work to develop, as the data may be spread amongst several tables. In today’s article, we’ll learn how to fetch data from multiple tables by using joins.

Two Syntaxes

There are two accepted syntax styles for writing joins: ANSI-style joins and theta-style joins. ANSI syntax uses the JOIN and ON keywords, as in the following example:

SELECT 	field1, 
	field2, 
FROM 	my_table 	t1
JOIN	my_other_table	t2
ON	t1.primary_id_field = t2.foreign_key_id_field
WHERE	t1.lastname	  = 'Smith';

The JOIN keyword is used to separate the names of the tables being joined, and the ON clause contains the relation showing which column is being used as the join key.

In theta-style syntax, the table joins are simply added to the WHERE clause:

SELECT 	field1, 
	field2, 
FROM 	my_table 	t1,
	My_other_table	t2
WHERE	t1.primary_id_field = t2.foreign_key_id_field
AND	t1.lastname	  = 'Smith';

MySQL also supports a nonstandard extension of the ANSI syntax that can be used as a sort of shorthand for when the join column has the same name in both joined tables:

SELECT 	field1, 
	field2, 
FROM 	my_table 	t1
JOIN	my_other_table	t2
USING	(t1.id_field)
WHERE	t1.lastname	  = 'Smith';

So Which Syntax is Best?

The ANSI syntax is generally preferable to theta style because it’s usually easier to read and understand, particularly when writing joins involving numerous tables. There are also some types of joins that can’t be written using theta-style notation in MySQL.

Join Types

In order to join tables together, there has to be some common data that allow those tables to be connected in some meaningful way. Although it’s possible to have more than one common column between two tables, most often, the join key will be the primary key of one table and a foreign key in the other.

To illustrate, we’ll perform queries against the following sample tables:

*Note: In a normalized database, the Manufacturer ID would be stored in the Models table. Here, I included the full description to better illustrate which manufacturers are associated with which models.

The first table contains automobile manufacturers; the second, some models that are built by the first several auto manufacturers. The common data between the two tables is the manufacturer, which is linked by manufacturer ID.

Now we’ll extract some data from the tables, using different join types in ANSI syntax.

Inner Join

An inner join is defined as a join in which unmatched rows from either table are not to be returned. In other words, the rows must match in both tables in order to be included in the result set.

SELECT 		t1.description AS 'Manufacturer', 
		t2.description AS 'Model'
FROM 		manufacturer 	t1
INNER JOIN	model		t2
ON		t1.id = t2.manufacturer_id
WHERE		t1.description = 'ACURA';

The "INNER" keyword is not required, but it is considered good practice to include it.

Typing the query above in the MySQL Command Line Client produces the following:

mysql> SELECT           t1.description AS 'Manufacturer',
    ->                  t2.description AS 'Model'
    -> FROM             manufacturer    t1
    -> INNER JOIN       model           t2
    -> ON               t1.id = t2.manufacturer_id
    -> WHERE            t1.description = 'ACURA';
+--------------+---------+
| Manufacturer | Model   |
+--------------+---------+
| ACURA        | INTEGRA |
| ACURA        | CL      |
| ACURA        | LEGEND  |
| ACURA        | RL      |
| ACURA        | NSX     |
| ACURA        | TL      |
| ACURA        | VIGOR   |
| ACURA        | EL      |
| ACURA        | NSX-T   |
+--------------+---------+
9 rows in set (0.00 sec)

Outer Join

Outer joins will return records in one table that aren’t matched in another. Outer joins can be further divided into the two types of left and right. In a left outer join, all records from the first (left-hand) table in a join that meet any conditions set in the WHERE clause are returned, whether or not there’s a match in the second (right-hand) table:

mysql> SELECT           t1.description AS 'Manufacturer',
    ->                  t2.description AS 'Model'
    -> FROM             manufacturer   t1
    -> LEFT JOIN        model          t2
    -> ON        		t1.id = t2.manufacturer_id
    -> WHERE		t1.description = 'New Car Co';

+--------------+----------------+
| Manufacturer | Model          |
+--------------+----------------+
| New Car Co   | (NULL)         |
+--------------+----------------+
19 rows in set (0.00 sec)

The ‘New Car Co’ is returned even though there are no associated models in the model table.

Similar to the left outer join, a right outer join returns all records from the second (right-hand) table in a join that meet any conditions set in the WHERE clause, whether or not there’s a match in the first (left-hand) table:

mysql> SELECT           t1.description AS 'Manufacturer',
    ->                  t2.description AS 'Model'
    -> FROM             manufacturer    t1
    -> RIGHT JOIN       model           t2
    -> ON               t1.id = t2.manufacturer_id
    -> WHERE		t2.description = 'Custom';
+--------------+--------+
| Manufacturer | Model  |
+--------------+--------+
| (NULL)       | Custom |
+--------------+--------+
1 row in set (0.00 sec)

The ‘Custom’ model is returned even though there is no associated manufacturer.

Cross-join

The cross-join, also referred to as a Cartesian product, returns all the rows in all the tables listed in the query.  Each row in the first table is paired with all the rows in the second table.  This happens when there is no relationship defined between the two tables. 

Note that, most of the time, we do not want a Cartesian join, and we end up with one because we failed to provide a filter on the join.  Result sets can get large quickly because the amount of data in the select is the number of rows in Table A multiplied by the number of rows in Table B. If you have more than two tables this multiplies at an exponential rate.

If we actually want a Cartesian join, then we should use the ANSI cross join to tell others reading the script that we actually wanted a Cartesian join. So why would we want one? One reason might be to produce all the combinations of 1, 2 and 3, which could be used as part of a password or ID generation process:

mysql> SELECT CONCAT(   CAST(t1.num AS CHAR),
    ->                  CAST(t2.num AS CHAR)) AS combinations
    -> FROM numbers t1, numbers t2;
+--------------+
| combinations |
+--------------+
| 11           |
| 21           |
| 31           |
| 12           |
| 22           |
| 32           |
| 13           |
| 23           |
| 33           |
+--------------+
9 rows in set (0.00 sec)

Now you’ve got every permutation of number combinations for two digits!

Knowing how to link tables is of great assistance in extracting data from normalized databases, but it may not always be enough. There will be times that no combination of joins will suffice to properly filter the data. In those cases, it may be necessary to use temporary tables. We’ll be looking at those in the next article.

» See All Articles by Columnist Rob Gravelle