SQL JOINS

What is a Join?
The SELECT queries shown thus far in this training guide retrieve information from a single table at a time.
However, not all questions can be answered using just one table. When it's necessary to draw on
information that is stored in multiple tables, use a join condition—an operation that produces a result by
combining (joining) information in one table with information in another.
The contents of two original tables are shown below. These tables can be joined together using a SELECT
query which retrieves data from more than one table at a time.

table1 table2

+----+----+

| i1 | c1 |

+----+----+

| 1 | a |

| 2 | b |

| 3 | c |

+----+----+

3 rows in set (#.## sec)

+----+----+

| i2 | c2 |

+----+----+

| 2 | c |

| 3 | b |

| 4 | a |

+----+----+

3 rows in set (#.## sec)

mysql> SELECT * FROM table1 JOIN table2;

+----+----+----+----+

| i1 | c1 | i2 | c2 |

+----+----+----+----+

| 1 | a | 2 | c |

| 2 | b | 2 | c |

| 3 | c | 2 | c |

| 1 | a | 3 | b |

| 2 | b | 3 | b |

| 3 | c | 3 | b |

| 1 | a | 4 | a |

| 2 | b | 4 | a |

| 3 | c | 4 | a |

+----+----+----+----+

9 rows in set (#.## sec)

The above example returns a list of all data in both tables combined. This is known as a cross join. This
example employs the use of the JOIN keyword. However, there are multiple methods available for joining
tables which will be discussed within this chapter.
A 'Cross Join' is so called because each row of each table is crossed with each row in every other table to
produce all possible combinations. The result is known as a 'Cartesian Product'. A Cartesian product is a
mathematical (set theory) concept which basically means combining all elements from the first entity with
all elements of the second entity.
Joining tables this way has the potential to produce a very large number of rows because the possible row
count is the product of the number of rows in each table. A cross join between three tables contain 100,
200 and 300 rows, respectively, could return 100X200X300 = 6 million rows. That's a lot of rows, even
though the individual tables are small. In cases like this, normally a WHERE clause is useful for reducing
the result to a more manageable size. A join condition is critical to produce meaningful table joins.