Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

MISSING ORA-00918 IN JOIN can be very dangerous

utente_7029320Feb 14 2024

I note a problem with alias in join.

Example:

CREATE TABLE TAB_A AS (SELECT ROWNUM COD ,'AAAAA' DESCR FROM DBA_OBJECTS WHERE ROWNUM < 11);

CREATE TABLE TAB_B AS (SELECT ROWNUM COD ,'BBBBB' DESCR FROM DBA_OBJECTS WHERE ROWNUM < 11);

CREATE TABLE TAB_C AS (SELECT ROWNUM COD ,'CCCCC' DESCR FROM DBA_OBJECTS WHERE ROWNUM < 11);

SELECT A.COD, B.DESCR

FROM TAB_A A

INNER JOIN TAB_B B ON A.COD=B.COD

INNER JOIN TAB_C B ON A.COD=B.COD;

Please note: i used same alias “B” for tab_b and tab_c and i selected b.descr

If you execute this query i will wait for an error ORA-00918 (because b.descr can be from tab_b and tab_c) but it do not happen and the join works well.

Moreover the result of b.descr is ‘BBBBB’ (from tab_b the first inner join i wrote) but if i change the order of writing inner join in the query:

SELECT A.COD, B.DESCR

FROM TAB_A A

INNER JOIN TAB_C B ON A.COD=B.COD

INNER JOIN TAB_B B ON A.COD=B.COD;

the result of b.descr is ‘CCCCCC’ (from tab_c the first inner join NOW)

I think this is dangerous because i have no warning about a possible mistake in a complex join and i can return an arbitrary value from my query depending on the order i listed my join.

Tested on 19c EE

Please what do you think about it?

thanks

Comments
Post Details
Added on Feb 14 2024
8 comments
346 views