Join criteria and the Where Clause...

[mtnbikur]

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?


Thanks!


Keith Taylor

DBA/Application Analyst

[bakpao]

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.