Note that in the SELECT clause, the column names are prefixed by the table name. This is a
requirement if the columns exist in more tables that participate in the table join, such as product_id
in our case. For the other column, prefixing its name with the table name is optional, although
it??™s a good practice to avoid confusion.
The query that returns only the products that belong to category 5 is
SELECT product.product_id, product.name
FROM product_category
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 127
INNER JOIN product
ON product.product_id = product_category.product_id
WHERE product_category.category_id = 5;
The results follow:
product_id name
------------------------------------------------------------------------------------
65 Afghan Flower
66 Albania Flower
67 Austria Flower
68 Bulgarian Flower
A final thing worth discussing here is the use of aliases. Aliases aren??™t necessarily related to
table joins, but they become especially useful (and sometimes necessary) when joining tables,
and they assign different (usually) shorter names for the tables involved. Aliases are necessary
when joining a table with itself, in which case, you need to assign different aliases for its different
instances to differentiate them. The following query returns the same products as the query
before, but it uses aliases:
SELECT p.
Pages:
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222