Thread: WHERE AND JOIN STATEMENTS

> What is the advantage (or advantages) of using JOIN instead of WHERE in a
> SELECT statement?
> Is JOIN faster than WHERE?
>
> I have a SELECT statement that involves 7 tables and I am using WHERE to
> relate them and get the result I need but I want to know if there is a
> performance issue if I change to JOIN.
>
> Respectfully,
> Jorge Maldonado
>

When it comes to speed they are both the same (talking about INNER JOIN here).
You can check the query plan and see it's the same for both.
About the advantages of using JOIN. This is my opinion but for many tables
it's easyer to read the query with JOIN then WHERE clause.
A small example:

SELECT *
FROM a
        INNER JOIN
        b
        ON (
          a.id = b.aid
        )
WHERE a.id = 'value'

SELECT *
FROM a, b
WHERE a.id = b.aid
AND a.id = 'value'

Now with many tables it's a bit easier to see the relations while using JOIN
instead of WHERE and using WHERE only for conditions.

--
Regards,
Jure

Jure Kobal <j.kobal@gmx.com> wrote:

>> What is the advantage (or advantages) of using JOIN instead of WHERE in a
>> SELECT statement?
>> Is JOIN faster than WHERE?

>> I have a SELECT statement that involves 7 tables and I am using WHERE to
>> relate them and get the result I need but I want to know if there is a
>> performance issue if I change to JOIN.

> When it comes to speed they are both the same (talking about INNER JOIN here).
> You can check the query plan and see it's the same for both.
> About the advantages of using JOIN. This is my opinion but for many tables
> it's easyer to read the query with JOIN then WHERE clause.
> A small example:

> SELECT *
> FROM a
>         INNER JOIN
>         b
>         ON (
>           a.id = b.aid
>         )
> WHERE a.id = 'value'

> SELECT *
> FROM a, b
> WHERE a.id = b.aid
> AND a.id = 'value'

> Now with many tables it's a bit easier to see the relations while using JOIN
> instead of WHERE and using WHERE only for conditions.

Another disadvantage of the non-JOIN syntax is that you need
to be more careful if you add more conditions with OR.

Tim

On 2010-03-05, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> --000e0cdf986cab962e048111beb4
> Content-Type: text/plain; charset=ISO-8859-1
>
> What is the advantage (or advantages) of using JOIN instead of WHERE in a
> SELECT statement?
> Is JOIN faster than WHERE?

Join is IMO easier to read, also asier to change if you want left
join instead etc.