This forum is currently locked. You can't register or post questions at this time. (read more)

JOIN or WHERE clause

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

JOIN or WHERE clause

Postby ponic » Thu May 31, 2012 5:24 pm

Hi,

Are there any advantages of using JOIN instead of where clause and does that improves speed or performance of sql queries?

Would like to have some input into this aspect.

Thanks and regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: JOIN or WHERE clause

Postby Tim... » Thu May 31, 2012 5:43 pm

Hi.

Kinda difficult to know what you are asking. Can you give me an example of two statements that display what you are talking about.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: JOIN or WHERE clause

Postby ponic » Fri Jun 01, 2012 7:14 am

Tim... wrote:Hi.

Kinda difficult to know what you are asking. Can you give me an example of two statements that display what you are talking about.

Cheers

Tim...


I am asking this for general information, I have always used where clause in sql. So wondering if use JOIN instead would be of any use.
Besides JOIN is ANSI standard I guess.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: JOIN or WHERE clause

Postby Tim... » Fri Jun 01, 2012 7:24 am

Hi.

Well I use joins for joining tables and where clause for filtering. Really need a couple of simple example sql statements to understand what you are asking. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: JOIN or WHERE clause

Postby ponic » Sat Jun 02, 2012 5:38 am

Thanks Tim for the information.

I have given first example as

Example 1

Code: Select all

SELECT    product_id, product_no,product_extension
      FROM    products
     WHERE    product_no IN (  SELECT     DISTINCT product_no
                                                    FROM     products
                                              GROUP BY     product_no
                                                 HAVING     COUNT ( * ) > 1)


or it is better to write as

Code: Select all

SELECT    a.product_id, a.product_no,a.product_extension
      FROM    products a inner join (select distinct product_no from products
      group by
      product_no
                                                 HAVING     COUNT ( * ) > 1) b on a.product_no = b.product_no


I believe

Code: Select all

WHERE IN
takes long time in a sql statement so it is better to write using

Code: Select all

JOIN

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: JOIN or WHERE clause

Postby Tim... » Sat Jun 02, 2012 8:50 am

Hi.

I'm gonna ignore the fact the filters are pointless in these examples, because I think you know that, but just wanted to give me an idea of the style of SQL.

My first attempt in this case would be this.

Code: Select all

SELECT a.product_id, a.product_no, a.product_extension
FROM   products a
WHERE  EXISTS (SELECT 1
               FROM   products b
               WHERE  b.product_no = a.product_no);


The EXISTS is correlated to the outer query and terminates as soon as a matching record is found, so in some situations it is more efficient that IN.

Having said that, you have to remember a few things:

- Relational databases are all about set processing, so for large number of rows, standard joins are probably the most efficient way of performing any action.
- On smaller numbers of rows, you may get benefit from using IN or EXISTS.
- If there were a "best" way to do something, the other ways would probably not exist. The reality is they all perform differently under different conditions, so you have to try them all and pick the best one for the specific quantity and type of data in your system.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron