Do you ever leave your and/or clauses directly in the join?

Lately I have been trying to keep my code cleaner and tightening things up a bit more. Instead of creating huge where clauses I have been putting my and / or operands directly in the join, (if the clause is associated with the join. For instance a address join, I will do and / or = '67%" for an address or for example, instead of where and then your criteria. I find my queries run faster as well, since most of mine are 4 joins at least and some go to attribute tables and can be over 25 joins. Do you practice this as well? Just wondering.

Find more posts tagged with

Comments

Zorodzai

I do that too. I honestly don't know why I chose to do it that way but it works so just stuck to it. Kinda like you I was thrown a bit in the deep end when I started learning SQL so, for a long time, I would learn/google how to do something without really understanding the underlying process taking place. Itzik's T-SQL Fundamentals 2008 book really helped me a lot in my understanding what my queries are doing in the db....... you are really working hard on your SQL - keep it up

N2IT

Yeah as long as the column is captured in that join it's okay to add your clauses there. If you are unsure I would go with the where clause. That will introduce all joins with the statement or vice versa.

NotHackingYou

I go to the where clause unless it's required for the join. IMO it's cleaner for me to read.

RobertKaucher

There are important differences in how INNER and OUTER joins deal with the placement of filter predicates.

You need to be careful doing this and you probably shouldn't do it in any instance (except in rare cases where the JOIN would require it) due to the potential issues it can cause. Stylistically I find it "objectionable" because of the subtle problems that are hard to troubleshoot. So I urge you to NOT do this unless it is required for some reason by the query.

Predicate-Based Query Filters | SQL Server content from SQL Server Pro
Equality Filters: ON vs. WHERE » Dave Turpin, SQL Server Practitioner
Understanding Query Filters and Clauses | T-SQL content from SQL Server Pro

Zorodzai

@Robertkaucher - thanks for the links, now I know why I shouldn't do it

UniqueAgEnT

Yes, as Robert said it the filter predicate placement is very important in Inner vs Outer. I have seen many queries where the Outer join is being promoted to an inner join when the filter is in the where clause instead of in the join. I use the where clause for my filters unless it needs to be placed in the outer join.

amcnow

With SQL Server, filter placement with inner joins (join clause vs where clause) does not alter functionality or performance. This may be a result of SQL Server being able to safely determine how the filter predicate is to work with the inner join. An ill-advised filter predicate within an outer join will indeed (like UniqueAgEnT already said) convert your outer join into an inner join.

It's standard practice to place all filter predicates within the where clause unless needed for the outer join. Oddly enough, I'm guilty of breaking this golden rule form time to time and have to correct myself.