JOIN or WHERE clause

[ponic]

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...]

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...

[ponic]

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...]

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...

[ponic]

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...]

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...