SELECT *
FROM Products.Category AS c
, Products.Product AS p
WHERE c.CategoryId = p.CategoryId (+)
AND p.ProductId IS NULL
so I think your point is mistaken. I do agree that the explicit JOIN syntax is clearer.Also, changing schema tends to be easier too as you can often just delete the JOIN line if you've been coding consistently instead of having to pick through the WHERE clause on more complex queries.
In the end though I guess a lot of it comes down to what you're used to, though I have been exposed to both and would definitely be in the JOIN camp and help utterly destroy you evil ,s if we had to have a worldwide battle to decide the fate of SQL kind.
Let's modify the query slightly, to retrieve all categories are not represented by any products from a particular manufacturer. The standard syntax would look like this:
SELECT *
FROM Products.Category AS c
LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId AND p.ManufacturerID = 123
WHERE p.ProductId IS NULL
How will you express this in your notation? In particular, what differentiates the "p.ManufacturerID = 123" of the join criteria from the "p.ProductId IS NULL" of the filter criteria?SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123
It's very common to want to LEFT JOIN two tables, and then see specifically which ones on the left had no corresponding record on the right. But the old way cannot specify the JOIN criteria separately from the filter criteria, so we can't express the query.
For example, in my current database, I might do the following to see what categories are not represented by any products:
By specifying the JOIN criteria in the ON, I can then use the WHERE to winnow down to the left-only rows. But if that were expressed in the old notation, it would be mistaken for JOIN criteria, and thus all categories would appear to have no products.