August 15, 2016 at 8:16 am
1.
SELECT Jcdtl.somdoc_no, Jcdtl.mdoc_no, Jcsze.en_id, Jcdtl.pd_id, Jcsze.sze_id,Jcsze.clr_id,Jcprs.prs_id,jcsze.pd_qty
FROM Jcdtl
JOIN Jcsze ON Jcdtl.mdoc_no=Jcsze.mdoc_no
JOIN Jcprs ON Jcdtl.mdoc_no=Jcprs.mdoc_no
JOIN Jbprs ON Jcprs.prs_id=Jbprs.prs_id
JOIN Sodtl ON Jcdtl.somdoc_no=Sodtl.mdoc_no
JOIN Byrdtl ON Sodtl.hd_id=Byrdtl.mdoc_no
JOIN Achead ON Byrdtl.hd_id=Achead.hd_id
JOIN Product ON Jcdtl.pd_id=Product.pd_id
JOIN Soclr ON Soclr.en_id=Jcsze.clragen_id
WHERE Jbprs.show_wip='Y'
2.
SELECT Jcdtl.somdoc_no, Jcdtl.mdoc_no, Jcsze.en_id, Jcdtl.pd_id, Jcsze.sze_id,Jcsze.clr_id,Jcprs.prs_id,jcsze.pd_qty
FROM Jcdtl
JOIN Jcsze ON Jcdtl.mdoc_no=Jcsze.mdoc_no
JOIN Jcprs ON Jcdtl.mdoc_no=Jcprs.mdoc_no
JOIN Jbprs ON Jcprs.prs_id=Jbprs.prs_id AND Jbprs.show_wip='Y'
JOIN Sodtl ON Jcdtl.somdoc_no=Sodtl.mdoc_no
JOIN Byrdtl ON Sodtl.hd_id=Byrdtl.mdoc_no
JOIN Achead ON Byrdtl.hd_id=Achead.hd_id
JOIN Product ON Jcdtl.pd_id=Product.pd_id
JOIN Soclr ON Soclr.en_id=Jcsze.clragen_id
Please suggest, which one is perfect, is their any issue of performance if i write query like two?
August 15, 2016 at 8:36 am
There's no difference. But don't take my word for it - compare the execution plans and see for yourself.
Now, if the join were an outer (instead of inner) join, then it would indeed make a difference, not just to performance, but to the results as well.
John
August 15, 2016 at 9:08 am
John Mitchell-245523 (8/15/2016)
There's no difference. But don't take my word for it - compare the execution plans and see for yourself.Now, if the join were an outer (instead of inner) join, then it would indeed make a difference, not just to performance, but to the results as well.
John
There IS a difference, it's a semantic difference rather than a functional difference. Join criteria and filter criteria serve two different purposes in relational theory. The second query conflates those two purposes, showing a lack of understanding of the relational theory behind SQL. If I had to choose between applicants who submitted those two queries, I would always choose the first one, even though the two queries are functionally equivalent.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2016 at 9:45 am
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
August 15, 2016 at 12:25 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply