Home » SQL & PL/SQL » SQL & PL/SQL » Can we replace any sub query using join
Can we replace any sub query using join [message #661520] Wed, 22 March 2017 08:28 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I need to know can we replace any sub query using join
Re: Can we replace any sub query using join [message #661522 is a reply to message #661520] Wed, 22 March 2017 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes. That doesn't mean it's always the right approach though
Re: Can we replace any sub query using join [message #661777 is a reply to message #661522] Sat, 01 April 2017 02:45 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

you can replace subquery with join you might see improvement/degradation of performance depending on the number of rows
in each of the tables where the join is involved.

Ex

SELECT A.ID FROM TABLEA
WHERE A.ID IN(SELECT ID FROM TABLEB)

Say in the above table if your TABLEB has large number of rows and you can join with TABLEA instead of subquery however your performance of query will degrade using joins.

SELECT A.ID FROM
TABLEA,TABLEB
WHERE TABLEA.ID=TABLEB.ID

Hence compare the cost before and after implementing.

garan
Re: Can we replace any sub query using join [message #661778 is a reply to message #661777] Sat, 01 April 2017 02:59 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Say in the above table if your TABLEB has large number of rows and you can join with TABLEA instead of subquery however your performance of query will degrade using joins.
This is not correct.
If your statistics are up to date, Oracle will transparently convert your query to the other one if it thinks it is better.
And it will convert IN to EXISTS (or the opposite) for the same reason; this depends on row numbers, indexes...
It will even remove the IN/EXISTS if a foreign key constraint exists between the 2 tables.

Previous Topic: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb)
Next Topic: Converting comma separated value in different rows
Goto Forum:
  


Current Time: Sat Feb 24 16:35:25 CST 2018

Total time taken to generate the page: 0.02671 seconds