Database Reference
In-Depth Information
▼
Output
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 5 |
| E Fudd | IL | 5 |
| Mouse House | OH | 5 |
| Wascals | IN | 5 |
| Yosemite Place | AZ | 5 |
+----------------+------------+--------+
▼
Analysis
Obviously the returned results are incorrect (compare them to the previous
results), but why did this happen? There are two
cust_id
columns, one in
customers
and one in
orders
, and those two columns need to be compared
to correctly match orders with their appropriate customers. Without fully quali-
fying the column names, MariaDB assumes you are comparing the
cust_id
in
the
orders
table to itself. And
SELECT COUNT(*) FROM orders WHERE cust_id = cust_id;
always returns the total number of orders in the
orders
table (because
MariaDB checks to see that every order's
cust_id
matches itself, which it
always does, of course).
▼
Analysis
Although subqueries are useful in constructing this type of
SELECT
statement,
care must be taken to properly qualify ambiguous column names.
Note
Always More Than One Solution As explained earlier in this chapter, although the sam-
ple code shown here works, it is often not the most efficient way to perform this type of
data retrieval. You revisit this example in a later chapter.
Tip
Build Queries with Subqueries Incrementally Testing and debugging queries with sub-
queries can be tricky, particularly as these statements grow in complexity. The safest
way to build (and test) queries with subqueries is to do so incrementally, in much the
same way as MariaDB processes them. Build and test the innermost query first. Then
build and test the outer query with hard-coded data, and only after you have verified that
it is working embed the subquery. Then test it again. And keep repeating these steps
as for each additional query. This takes just a little longer to construct your queries, but
doing so saves you a lot of time later (when you try to figure out why queries are not
working) and significantly increases the likelihood of them working the first time.
