Question :
I am studying query optimization. I have read that it might be preferred to apply relational selections before a logical join operation. Is there a scenario where joining first and then applying selections would be desirable?
I searched for an example of this, and found that people seemed to be saying that a query with conjunctions might benefit from this, but it is not clear to me why this should be. This is not a question about how a particular query optimizer in a particular commercial product performs, it is a question of logic.
An example would be appreciated.
EDIT:
Inner Joining allows joining first and then selection and it would be optimized approach too. Example:
SELECT * FROM employee,**(Inner Joining)** department
WHERE employee.DepartmentID=department.DepartmentID;
Answer :
Yes, there are cases where applying the filter after the join is preferable. But they are rare.
Consider this example:
SELECT ...
FROM a
INNER JOIN b
ON b.key = a.key
WHERE a.some_string LIKE '%foo%'
Now, assume that very few rows in a
has a matching row in b
. It is now faster to first join and remove the rows from a
that does not match and then apply the expensive string filter.