At 8:13 AM -0400 9/1/09, Dan Cech wrote:
Michael Southwell wrote:
It's always seemed easier and clearer to me to use WHERE instead of JOIN
(where the table IDs are indeed named id, and the foreign keys are
labeled with the table name):
SELECT product_name, vendor_name
FROM product, vendor
WHERE vendor.id = product.vendor_id
The problem with this approach is that you end up with the join
conditions and the 'real' selection criteria mashed together.
SELECT product_name, vendor_name, category_name
FROM product, vendor, category
WHERE vendor.id = product.vendor_id AND price > 50 AND category.id =
product.category_id AND color = 'red'
vs
SELECT product_name, vendor_name, category_name
FROM product
JOIN vendor ON vendor.id = product.vendor_id
JOIN category ON category.id = product.category_id
WHERE price > 50 AND color = 'red'
I mixed up the order of the WHERE elements in the first query for the
purposes of this example, but I have seen stuff like that in production
code.
Dan
Dan:
I need to understand joins much better than I do now because the
first query I fully understand while the second I don't.
Cheers,
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
