4 ms·

That’s expressible in Oracle syntax as: SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+)

That’s expressible in Oracle syntax as: 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.

mattmanser 15y ago
I find the JOIN syntax to be far more maintainable too, the WHERE clauses become really hard to read amongst the dross of boilerplate JOIN clauses from the salient WHERE clauses. Things also get confusing as soon as you throw an OR in the WHERE clause, too many unnecessary brackets. 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.
CWuestefeld 15y ago
That’s expressible in Oracle syntax 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?
- dangoldin 15y ago
  I'm no expert in SQL (Especially the Oracle flavor) but why would the following not work? SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123

mattmanser 15y ago

I find the JOIN syntax to be far more maintainable too, the WHERE clauses become really hard to read amongst the dross of boilerplate JOIN clauses from the salient WHERE clauses. Things also get confusing as soon as you throw an OR in the WHERE clause, too many unnecessary brackets. 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.

CWuestefeld 15y ago

That’s expressible in Oracle syntax 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?

dangoldin 15y ago
I'm no expert in SQL (Especially the Oracle flavor) but why would the following not work? SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123