472,099 Members | 2,273 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,099 software developers and data experts.

Join criteria and the Where Clause...

Lots of reading out there says you should only place “join criteria” in the join clause. That makes perfect sense. What about other search criteria? I’ve got other criteria, ie. cst_delete_flag = 0 that predecessors have put in the where clause and sometimes it’s programmed into the join clause. Ie.

Select cst_id, cst_name,
from co_customer
left join mb_membership on mbr_cst_key = cst_key and mbr_delete_flag = 0
where cst_delete_flag = 0
and mbr_flag = 1

The delete flag is what really concerns me. We have a NNN_delete_flag on every table in our database. We NEVER delete records. We just set the appropriate NNN_delete_flag = 1 and go about our business. The delete_flag is not part of how the two tables are joined, but for readability and just plain remembering, we've tried to standardize all the NNN_delete_flag=0 criteria being put in the join. It reads so much nicer when you're joining 6 or 8 tables. Is that going to come back to bite us later on?

Other articles have made it sound almost like any outer joins should have ALL criteria (join and otherwise) in the join clause area.

Does that make sense? Am I totally off the mark here?


Keith Taylor

DBA/Application Analyst
Aug 24 '07 #1
1 1929
Having the join criteria on the join operation has the main purpose of making it easier for you to spot it compared when it is in the where clause. However, putting all of the criteria (even the ones not related to that join) in that join criteria clause is not a good idea since it increases clutter for you.
Aug 27 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Matt | last post: by
9 posts views Thread by Alan Lane | last post: by
1 post views Thread by ehchn1 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.