Notifications
Mark all as read
Q&A

Are there best practices for sticking conditions in WHERE clauses vs the JOIN statement?

+8
−0

Lets say I have two tables, A and B and I need to join a subset of them.

Is there best practices of sticking the conditions in the WHERE clause like this,

SELECT * 
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000

versus sticking the condition in the JOIN like this,

SELECT * 
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000

For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?

Why should this post be closed?

0 comments

2 answers

+10
−0

SQL is a declarative language, and the form of the query does not dictate the form of the query plan that actually retrieves the data. So these two queries might be not only the same speed, but actually map to the exact same query plan to be executed.

So if speed and results don't give an advantage to one form, the best practice is to go with the one that is easier to read. In this case, I'd go with the first one.

In the second query, the JOIN handles both joining and filtering the data. By using a separate WHERE clause, you separate the actions of joining and filtering into different clauses.

If a second filter needs to be added later, it's a little less intuitive to add it to the JOIN. It's possible a later developer might create a WHERE clause, and then you'd have the JOIN filtering and joining, and the WHERE filtering as well.

But if you're already using a WHERE for filtering, it's simple and intuitive to add another condition to the WHERE.

0 comments

+4
−0

@BruceAlderman gave a good answer with different aspects that covers the most. I'm not very good at SQL, so my answer is more general.

When I have to choose between two different things that are equivalent in performance and functionality and readability is the only thing that's left. Then I try to describe the result of the operation in plain English (or any other spoken language) and then pick whatever code that most accurately describes the intention of the operation.

I like when programming languages has features that makes it easier to express your intention. One of my favorites there is the keyword unless in Ruby. AFIK it's completely equivalent to if not. But in many cases it sounds way more natural. if not <condition> tend to give the message "if this condition is not met" in a very dry manner. unless <condition> tend to give the message "Always do this. Well, unless this very unlikely event has happened."

0 comments

Sign up to answer this question »