Link to home
Start Free TrialLog in
Avatar of systematica
systematicaFlag for Italy

asked on

Oracle 11 query strange INNER JOIN behavior

Greetings.
I've got a problem with this query:
SELECT * 
FROM   sincrpar_tite c 
       INNER JOIN (SELECT ptserial, 
                          ptnumpar, 
                          ptdatsca, 
                          pttipcon, 
                          ptcodcon, 
                          ptcodval, 
                          ptmodpag, 
                          pt_segno, 
                          pttotimp, 
                          ptflcrsa 
                   FROM   sincrpar_tite 
                   WHERE  e.ptroword >=- 1 
                          AND ptorigsa <> 'S') e 
         ON c.ptnumpar = e.ptnumpar 
            AND c.ptdatsca = e.ptdatsca 
            AND c.pttipcon = e.pttipcon 
            AND c.ptcodcon = e.ptcodcon 
            AND c.ptcodval = e.ptcodval 
            AND c.ptmodpag = e.ptmodpag 
WHERE  c.ptroword >=- 1 
       AND ptorigsa <> 'S' 
       AND c.ptserial = '0000014198' 

Open in new window


It returns 3 rows of which the first one is completely wrong. The wrong row is different from the others in all the JOIN conditions although it fulfills the "WHERE" filters.

I think it's a problem with INNER JOIN, can you help me?
I'm using Oracle 11.
Avatar of Pratima
Pratima
Flag of India image

try this

 SELECT c.*
FROM   sincrpar_tite c
       INNER JOIN (SELECT ptserial,
                          ptnumpar,
                          ptdatsca,
                          pttipcon,
                          ptcodcon,
                          ptcodval,
                          ptmodpag,
                          pt_segno,
                          pttotimp,
                          ptflcrsa
                   FROM   sincrpar_tite x
                   WHERE  x.ptroword >=- 1
                          AND x.ptorigsa <> 'S') e
         ON c.ptnumpar = e.ptnumpar
            AND c.ptdatsca = e.ptdatsca
            AND c.pttipcon = e.pttipcon
            AND c.ptcodcon = e.ptcodcon
            AND c.ptcodval = e.ptcodval
            AND c.ptmodpag = e.ptmodpag
WHERE  c.ptroword >=- 1
       AND c.ptorigsa <> 'S'
Avatar of systematica

ASKER

Same results.
As you can see from the attached image the first row is obviously wrong
result.JPG

Why first row is wrong ?

it fullfills requirements
c.ptroword >= -1
       AND c.ptorigsa <> 'S'
if you change the condition
 c.ptroword > -1

then will not come that row
Well indeed, but if I "INNER" join the tables on

 c.ptdatsca = e.ptdatsca AND
pttipcon = e.pttipcon AND
c.ptcodcon = e.ptcodcon

like I do you'll see that these 3 values are different. I didn't expect this on an INNER JOIN, I would have expected this result from a left or full join.

Am i mistaken?
not sure but I am not getting question correctly

in inner join you have the condtions
 WHERE  x.ptroword >=- 1
                          AND x.ptorigsa <> 'S')

same are in Where cluase .....

offcourse in innerjoin you will get all values are same in both innser join
Where conditions apply to the first table (c) and where conditions in parenthesis apply to the second select table (e).

If I don't filter for the fields in the where clause it would get me 300+ rows, the filters are needed.

Maybe I'm mistaken, but in SQL the JOIN filters are calculated before the WHERE clause, so if I have 300+ rows from a JOIN clause I will filter them with the WHERE.

Does Oracle works differently? Isn't there the same difference as in SQL between an INNER join and a FULL one?
In inner join your 2nd table is query
which has where conditions right ?
Let me clarify.

First table (c):
SELECT * FROM   sincrpar_tite c WHERE  c.ptroword >=- 1AND c.ptorigsa <> 'S'

Second table (e):
SELECT ptserial, ptnumpar, ptdatsca, pttipcon, ptcodcon, ptcodval, ptmodpag, pt_segno, pttotimp, ptflcrsa FROM   sincrpar_tite WHERE  e.ptroword >=- 1 AND ptorigsa <> 'S'

The INNER JOIN clause joins these two.
ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Besides that (it's part of a bigger trigger) it should be the most external where to define the actual filters for the query). You gave me an idea, however. I will try to change the inner filter and run some tests.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you both, you helped me to understand the problem. There was in fact something I overlooked.