Database Reference
In-Depth Information
▼
Analysis
This example displays the items in order number
20005
. Order items are
stored in the
orderitems
table. Each product is stored by its product ID,
which refers to a product in the
products
table. The products are linked to
the appropriate vendor in the
vendors
table by the vendor ID, which is stored
with each product record. The
FROM
clause here lists the three tables, and the
WHERE
clause defines both of those join conditions. An additional
WHERE
con-
dition is then used to filter just the items for order
20005
.
Caution
Performance Considerations MariaDB processes joins at runtime, relating each table
as specified. This process can become resource intensive, so be careful not to join
tables unnecessarily. The more tables you join, the more performance degrades.
Now would be a good time to revisit the following example from Chapter 14,
“Working with Subqueries.” As you will recall, this
SELECT
statement returns
a list of customers who ordered product
TNT2
:
▼
Input
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
As mentioned in Chapter 14, subqueries might not always be the most efficient
way to perform complex
SELECT
operations, and so as promised, here is the
same query using joins:
▼
Input
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
