Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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; 
share|improve this question
1  
What do you mean with "select first and then join"? –  ypercube Apr 3 at 13:22
1  
Sample queries would help clarify your thoughts. Also add which DBMS you are asking about. Optimization is quite different across DBMS. –  ypercube Apr 3 at 13:30
    
Query logic does not have much to do with query optimization, I don't think. You make sure your query is semantically correct, then the optimizer tries to make it fast while remaining correct. –  mustaccio Apr 3 at 14:29

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.