12 ms·
This kind of notation (a comma-separated list of tables) used to be the standard. We've moved beyond it for a very good reason: there's a very common class of q
by CWuestefeld 15y ago
This kind of notation (a comma-separated list of tables) used to be the standard. We've moved beyond it for a very good reason: there's a very common class of queries that this notation cannot express.
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:
SELECT *
FROM Products.Category AS c
LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId
WHERE p.ProductId IS NULL
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.
- robinhouston 15y agoThat’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 agoI 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 agoThat’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 agoI'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