I’m re-reading the Visual QuickStart Guide to SQL and working out some examples. I don’t use a whole lot of joins on my websites and I’ve never given the topic much thought. In fact, if you asked me, I would have said that I never use joins, because none of my code uses the word.
It turns out that a lot of the queries on my websites use INNER JOINs, but without using the INNER JOIN keyword. In this example I have a table of products and a table of categories. Each product belongs to one category and the category key is in the product table. In this case the category table has unique values for for `name`.
I frequently use something like this:
SELECT product.name, product_category.name
FROM product, product_category
WHERE product.category_id = product_category.id
Which is equivalent to this using JOIN syntax.
SELECT product.name, category.name
FROM product
INNER JOIN product_category AS category
ON product.category_id = category.id
Here’s an example where I combine three tables. The product table has information about the product. It has a unique id for each product. The details table has selling information about the product. This table has a one-to-one mapping to the product table using `product_id`. The category table is the same table as in the example above. It is a one-to-many mapping. Note that the order in which the joins are made doesn’t matter.
SELECT product.name, details.itemNum, details.price, category.name, details.taxable, details.shipping_charge
FROM product, product_details AS details, product_category AS category
WHERE product.id = details.product_id
AND product.category_id = category.id
AND details.isForSale = 'true'
ORDER BY product.name
Which is equivalent to this using JOIN syntax.
SELECT product.name, details.itemNum, details.price, category.name, details.taxable, details.shipping_charge
FROM product
INNER JOIN product_details AS details
ON product.id = product_id
INNER JOIN product_category AS category
ON product.category_id = category.id
WHERE details.isForSale = 'true'
ORDER BY product.name