diffent between "AND.. and ...and " and"and (and.. and)"

Question

I have two statement sql for oracle 11gr2

sql1:

Select * from tableA inpt, tableB ptbs
where inpt.z='A'
AND (inpt.pid    = ptbs.pid
     AND  inpt.instcd = ptbs.instcd);

sql2:

Select * from tableA inpt, tableB ptbs
where inpt.z='A'
AND inpt.pid    = ptbs.pid
     AND  inpt.instcd = ptbs.instcd

Sql1 is faster than sql2?

The writing ... and ... and ... and ... Slowly write and (... and ... and (... and ... and ...))?

Similar to "or"?

there should be no difference between your 2 statements. try running each multiple times first.. you might have a cache issue. otherwise try comparing explain plans. — ShoeLace, Oct 13 '17 at 03:34
They're logically the same. Check the explain plan and you should find there is no difference. — Jeffrey Kemp, Oct 13 '17 at 04:52
I run and found differnce between sql1 and sql2. I see Oracle have diffence process sql1 and sql2. — Vinh Hoang, Oct 13 '17 at 06:59
The SQL parser knows [basic logic](https://en.wikipedia.org/wiki/Logical_conjunction) and does not in general change execution plan as a result of redundant bracketing. If you have an example where it does, please share it. — William Robertson, Oct 13 '17 at 10:48

score 1 · Answer 1 · answered Oct 13 '17 at 08:33

There is no difference here.

 where inpt.z='A'
    AND (inpt.pid    = ptbs.pid
         AND  inpt.instcd = ptbs.instcd);

or

where inpt.z='A'
AND inpt.pid    = ptbs.pid
     AND  inpt.instcd = ptbs.instcd

or

where inpt.pid    = ptbs.pid
     AND  inpt.instcd = ptbs.instcd and inpt.z='A'

All is the same.

score 0 · Answer 2 · answered Oct 13 '17 at 03:16

0

It should probably just be written like:

SELECT *
FROM tableA inpt
INNER JOIN tableB ptbs
ON inpt.pid = ptbs.pid
WHERE inpt.instcd = ptbs.instcd
AND inpt.z = 'A'

This might be worth a review: INNER JOIN ON vs WHERE clause

answered Oct 13 '17 at 03:16

kjmerf

2 Answers2