Database Reference
In-Depth Information
As just explained, breaking data into multiple tables enables more efficient stor-
age, easier manipulation, and greater scalability. But these benefits come with
a price.
If data is stored in multiple tables, how can you retrieve that data with a single
SELECT
statement?
The answer is to use a join. Simply put, a join is a mechanism used to associ-
ate tables within a
SELECT
statement (and thus the name join). Using a special
syntax, multiple tables can be joined so a single set of output is returned, and
the join associates the correct rows in each table on-the-fly.
Note
Maintaining Referential Integrity It is important to understand that a join is not a
physical entity—in other words, it does not exist in the actual database tables. A join is
created by MariaDB as needed, and it persists for the duration of the query execution.
When using relational tables, it is important that only valid data is inserted into relation-
al columns. Going back to the example, if products were stored in the
products
table
with an invalid vendor ID (one not present in the
vendors
table), those products would
be inaccessible because they would not be related to any vendor.
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 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;
