Database Reference
In-Depth Information
It might seem strange to use a
WHERE
clause to set the join relationship, but
actually, there is a good reason for this. Remember, when tables are joined
in a
SELECT
statement, that relationship is constructed on-the-fly. Nothing
in the database table definitions can instruct MariaDB how to join the tables.
You have to do that yourself. When you join two tables, what you are actually
doing is pairing every row in the first table with every row in the second table.
The
WHERE
clause acts as a filter to only include rows that match the specified
filter condition—the join condition, in this case. Without the
WHERE
clause,
every row in the first table is paired with every row in the second table, regard-
less of whether they logically go together.
New Term
Cartesian product The results returned by a table relationship without a join condition.
The number of rows retrieved is the number of rows in the first table multiplied by the
number of rows in the second table.
To understand this, look at the following
SELECT
statement and output:
▼
Input
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;
▼
Output
+----------------+----------------+------------+
| vend_name | prod_name | prod_price |
+----------------+----------------+------------+
| ACME | .5 ton anvil | 5.99 |
| ACME | 1 ton anvil | 9.99 |
| ACME | 2 ton anvil | 14.99 |
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Fuses | 3.42 |
| ACME | JetPack 1000 | 35.00 |
| ACME | JetPack 2000 | 55.00 |
| ACME | Oil can | 8.99 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
