John Mitchell-245523 (8/15/2016)
I'm going to have to disagree there, I'm afraid. Suppose the query had originally been written with an outer join, with the filter (quite correctly) in the join predicate, but then changed to an inner join. Would it really be so wrong to leave everything else as it is? I certainly don't think it would show a lack of understanding. I do agree with your preference to keep anything that references only one side of the inner join in the WHERE clause, but I wouldn't disqualify someone from a competition for thinking differently. If I suspected they didn't understand relational theory properly, I'd ask a few more questions before doing showing them the door!John
Yes, yes it would. We have standards for a reason, and those standards are based on relational theory. All other things being equal, there is no reason to deviate from those standards. None.
With the outer join version there is a difference in functionality, and that difference determines where the criterion should appear. With the inner join, there is no difference in functionality, so the query should adhere to the standards.
Of course, I'm being a bit hardline here to make a point. There are several criteria that should be used to evaluate two different pieces of code, and functionality is only one of those pieces.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA