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)
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.
Comments
Post a Comment