Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Adelmo P.

Asked: July 05, 2016 - 8:54 pm UTC

Last updated: July 06, 2016 - 3:32 am UTC

Version: Oracle 11

Viewed 1000+ times

You Asked

What is the best practice use "Inner Join" o "Where" Example

Example A
select
DISTINCT(ET.DESCRIPTION)
FROM
EVENTTYPE ET INNER JOIN EVENTDCO E ON E.EVENTTYPEID = ET.EVENTTYPEID
INNER JOIN CONTEXTOPERATION CTX ON E.OPERATIONPK = CTX.ID

WHERE
E.OPERATIONPK = (
SELECT MAX(CTX.ID) FROM CONTEXTOPERATION CTX WHERE CTX.STATUS = 2 AND CTX.ID <> 16780769
AND CTX.ITEM = 32415097)


Example b


select
DISTINCT(ET.DESCRIPTION)
FROM
EVENTTYPE ET,
EVENTDCO E,
CONTEXTOPERATION CTX
WHERE
E.OPERATIONPK = CTX.ID
AND E.EVENTTYPEID = ET.EVENTTYPEID
AND E.OPERATIONPK = (
SELECT MAX(CTX.ID) FROM CONTEXTOPERATION CTX WHERE CTX.STATUS = 2 AND CTX.ID <> 16780769
AND CTX.ITEM = 32415097)



What is most optimal queries???

and Connor said...

In the case you've provided, its most likely they'll be treated identically by the optimizer. But be aware, that "WHERE" and "ON" and not *exactly* the same.

Here's a blog post I wrote about it a while back

https://blogs.oracle.com/sql/entry/last_week_on_asktom



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.