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.

Pratima

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'

systematica

ASKER

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

Pratima

Why first row is wrong ?

it fullfills requirements
c.ptroword >= -1
AND c.ptorigsa <> 'S'

Pratima

if you change the condition
c.ptroword > -1

then will not come that row

systematica

ASKER

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?

Pratima

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

systematica

ASKER

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?

Pratima

In inner join your 2nd table is query
which has where conditions right ?

systematica

ASKER

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

Pratima

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

systematica

ASKER

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

yuching

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

systematica

ASKER

Thank you both, you helped me to understand the problem. There was in fact something I overlooked.