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.
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;
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.