Joining on two columns in one join

Do you all practice this ever? For instance (Join Table1 as t on t.attribute1 = t1.attribute1 and t.attribute2 = t3.attribute2) The other tables would of been qualified prior in the query as another join. There are a lot of moving part such as date range tables and location tables. These two essentially snap together to give you some context.

Find more posts tagged with

Comments

NotHackingYou

I will only join on more than one column if it is required to make the join work. Otherwise, add any additional filtering characteristics in the WHERE clause.

N2IT

Yeah if there is a Crossjoin or more commonly used a outer join I keep everything in the where clause, however inner joins I like to build my conditions in the joins so I know where they are executing. I do a lot of sub queries in max get date which is very useful for my environment.

NotHackingYou

I would encourage you instead to use descriptive aliases and keep only the minimum required to perform the join in the join condition. Then add your filters in the WHERE clause. As long as you have good table aliases, you will not be confused when you need to later edit your WHERE clause. However, if you are using JOIN for WHERE and JOIN, it will later be confusing - was that a match or a filter?

N2IT

Usually a filter.

NotHackingYou

The question was more rhetorical. Later on down the road, yourself or another developer will look at your query and wonder - was this part of the join condition really a join condition or just a filter? It will be confusing and difficult to troubleshoot versus if you had put a filter where it belongs, in the WHERE clause. You of course can do whatever makes sense to you but I always recommend common convention wherever possible.

I have not analyzed enough queries with filters in the JOIN versus the WHERE to determine if there are typically measurable performance impacts.

N2IT

Thanks for chiming in Carl. Hey this is real world and I do whatever I can to get the job done. I appreciate you sharing your knowledge with me, it helps a lot. I find it interesting experimenting with different SQL commands and techniques. I am finally starting to learn about CTE's (not much yet though).