OraFAQ Forum: SQL & PL/SQL » ANSI vs Oracle Join Syntax

Home » SQL & PL/SQL » SQL & PL/SQL » ANSI vs Oracle Join Syntax

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ANSI vs Oracle Join Syntax [message #684475]

Sat, 12 June 2021 09:43

dancko
Messages: 108
Registered: June 2013
Location: italy

Senior Member

Hi to all

I see recommendation to use ANSI join instead of Oracle conventional one.
But the advantages are only better readability and avoid potential unwanted cartesian product?
In particularly, what I want to know is, will there be any difference in performance?
What is better in performance, ANSI syntax or Oracle one?

Thanks in advance.

[Updated on: Sat, 12 June 2021 09:47]

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684476 is a reply to message #684475]

Sat, 12 June 2021 10:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can answer this question for yourself by writing the SQL both ways & then produce & compare the EXECUTION PLAN for both.

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684477 is a reply to message #684475]

Sat, 12 June 2021 10:27

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

Take this example of a full outer exclusive join (remember that from your relational algebra classes?) in the HR demo schema:

Code: [Select all] [Show/ hide]

orclz>
orclz> set autot trace exp
orclz>
orclz> select coalesce(e.last_name,d.department_name) from employees e full join departments d
  2  on (e.department_id=d.department_id)
  3  where e.department_id is null or d.department_id is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   122 |  6954 |     6   (0)| 00:00:01 |
|*  1 |  VIEW                 | VW_FOJ_0    |   122 |  6954 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|             |   122 |  3294 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  1177 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NULL OR "D"."DEPARTMENT_ID" IS NULL)
   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

orclz>
orclz> select e.last_name from employees e
  2  where not exists (select 'x' from departments d where e.department_id=d.department_id)
  3  union all
  4  select d.department_name from departments d
  5  where not exists (select 'x' from employees e where e.department_id=d.department_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 1717839905

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |    18 |   334 |     6   (0)| 00:00:01 |
|   1 |  UNION-ALL          |                   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL | EMPLOYEES         |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |   NESTED LOOPS ANTI |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("E"."DEPARTMENT_ID" IS NULL)
   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

orclz>

The optimizer is coming with something very different. Which would be quicker? I dunno! You would have to test, on your data. One thing to note - the estimate for the join cardinality is much better with the Oracle trad syntax.

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684478 is a reply to message #684477]

Sat, 12 June 2021 11:10

dancko
Messages: 108
Registered: June 2013
Location: italy

Senior Member

Thanks for the response. It's seems like that there is not an univocal answer but it's depends on data and in which context the query is executed.

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684479 is a reply to message #684478]

Sun, 13 June 2021 01:24

John Watson
Messages: 8989
Registered: January 2010
Location: Global Village

Senior Member

Quote:

it's depends on data and in which context the query is executed.

Do you think so? It would be interesting to see your tests. I have always found ANSI syntax to equal to or better than the old style. This is another solution to the full exclusive outer join,

Code: [Select all] [Show/ hide]

select e.last_name from departments d,employees e where e.department_id=d.department_id(+) and d.department_id is null
union all
select d.department_name from employees e,departments d where e.department_id(+)=d.department_id and e.department_id is null
/

but I would be surprised if it performed as well as the ANSI version. When tuning SQL, it is important to try all the equivalent SQLs you can think of to see if there is some way you can help the optimizer.

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684482 is a reply to message #684479]

Sun, 13 June 2021 11:40

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Based on my own experience:

some Oracle tools (like Reports Builder, e.g. 10g) don't understand JOINs; they force you to use the old, comma-separated list of tables in the FROM clause (for example, in Format Triggers)
Oracle's "old" outer join operator, (+), lets you outer join one table to max one another table. OUTER JOIN, on the other hand, lets you outer join it to as many tables as needed, in a simple manner

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684483 is a reply to message #684482]

Sun, 13 June 2021 13:37

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Quote:

Oracle's "old" outer join operator, (+), lets you outer join one table to max one another table.

You have to be more specific and not confuse people. What you said was true to old, long desupported versions:

Code: [Select all] [Show/ hide]

SQL> SELECT  BANNER
  2    FROM  V$VERSION
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> SELECT  *
  2    FROM  DUAL T1,
  3          DUAL T2,
  4          DUAL T3,
  5          DUAL T4
  6    WHERE T1.DUMMY(+) = T2.DUMMY
  7      AND T1.DUMMY(+) = T3.DUMMY
  8      AND T1.DUMMY(+) = T4.DUMMY
  9  /
    AND T1.DUMMY(+) = T3.DUMMY
                    *
ERROR at line 7:
ORA-01417: a table may be outer joined to at most one other table

SQL> SELECT  BANNER
  2    FROM  V$VERSION
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> SELECT  *
  2    FROM  DUAL T1,
  3          DUAL T2,
  4          DUAL T3,
  5          DUAL T4
  6    WHERE T1.DUMMY(+) = T2.DUMMY
  7      AND T1.DUMMY(+) = T3.DUMMY
  8      AND T1.DUMMY(+) = T4.DUMMY
  9  /

D D D D
- - - -
X X X X

SQL>

SY.

Report message to a moderator

Re: ANSI vs Oracle Join Syntax [message #684497 is a reply to message #684483]

Tue, 15 June 2021 12:04

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Solomon

What you said was true to old, long desupported versions

Right; thank you for pointing it out.

Though, people (me included) still use that old, long desupported version. I'm pretty much sure that we're not the only ones. Time to upgrade? Certainly, but that's not my call. Do we have newer databases? Yes, 12c and 19c, but something is still on 11g.

Report message to a moderator

Previous Topic:	dbms_scheduler.create_job() throws error ORA-27362
Next Topic:	Help improve SQL Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 09 02:09:57 CST 2025