... and once you've switched to INNER JOIN, the next question is "when is it appropriate to add to the JOIN clause vs WHERE clause?"
as in
join orders returns on returns.original_order_id = orders.order_id
WHERE <-------- Should order type be in the WHERE clause?
returns.order_type = 'RETURN'
vs
join orders returns on returns.original_order_id = orders.order_id
AND <-------- Should order type be in the JOIN clause?
returns.order_type = 'RETURN'
One argument was that JOINs are specifically for tickling indexes and only for structural joins between tables, not for describing the table itself, so it should go in the WHERE clause. ("ALL context should be in the where clause.")
One argument was that sometimes a definition is limited by context, so strategically it makes sense to add to the JOIN clause. ("the `returns` alias MUST be limited to return orders, so it should be in the JOIN"; you'd have to adjust your indexes to account for the "order_type".)
Reading the title, I thought this was the actual question at hand.
I try to keep JOIN logic to only on the keys. I like to think this makes reading the query more linear: now I have all of these tables, what data gets retained/removed?
I see it as WHERE clause joining is the "can't mess it up" way to do it, and JOIN clause joining is "better in some situations".
Imagine you're using SQL fragments. Using JOIN clauses make your code extremely clean:
<sql id="getRecentReturns">
<fragment id="ReturnOrderFragment">
where returns.order_date between sysdate - 30 and sysdate <------- Nice, clean code. No chance bugs from forgetting to add `returns.order_type = "Return"` or `employee.customer_type = "Employee"` or whatever.
</sql>
That said - if the guy making the table didn't make an index that includes your attribute (order_type and customer_type above), don't use it."
as in
vs One argument was that JOINs are specifically for tickling indexes and only for structural joins between tables, not for describing the table itself, so it should go in the WHERE clause. ("ALL context should be in the where clause.")One argument was that sometimes a definition is limited by context, so strategically it makes sense to add to the JOIN clause. ("the `returns` alias MUST be limited to return orders, so it should be in the JOIN"; you'd have to adjust your indexes to account for the "order_type".)