MariaDB Crash Course - Ben Forta [43]
To prevent this from occurring, MariaDB can be instructed to only allow valid values (ones present in the vendors table) in the vendor ID column in the products table. This is known as maintaining referential integrity and is achieved by specifying the primary and foreign keys as part of the table definitions (as explained in Chapter 21, “Creating and Manipulating Tables”).
For an example of this, see the create.sql script used to create the crashcourse database tables. The ALTER TABLE statements at the end of the file are defining constraints to enforce referential integrity.
* * *
Creating a Join
Creating a join is simple. You must specify all the tables to be included and how they are related to each other. Look at the following example:
Input
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
Output
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| 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 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
Analysis
Take a look at the preceding code. The SELECT statement starts in the same way as all the statements you’ve looked at thus far, by specifying the columns to be retrieved. The big difference here is that two of the specified columns (prod_name and prod_price) are in one table, whereas the other (vend_name) is in another table.
Now look at the FROM clause. Unlike all the prior SELECT statements, this one has two tables listed in the FROM clause, vendors and products. These are the names of the two tables that are being joined in this SELECT statement. The tables are correctly joined with a WHERE clause that instructs MariaDB to match vend_id in the vendors table with vend_id in the products table.
Notice that the columns are specified as vendors.vend_id and products.vend_id. This fully qualified column name is required here because if you just specified vend_id, MariaDB cannot tell which vend_id columns you are referring to (as there are two of them, one in each table).
* * *
Caution: Fully Qualifying Column Names
You must use the fully qualified column name (table and column separated by a period) whenever there is possible ambiguity about to which column you are referring. MariaDB returns an error message if you refer to an ambiguous column name without fully qualifying it with a table name.
* * *
The Importance of the WHERE Clause
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, regardless 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