Adding conditions in the ON clause of a JOIN

[jackal_on_work@yahoo.com]

Hi Faculties,
I have two queries which give me the same output.


-- Query 1
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_i d = cat.id
WHERE cat.id = 1;

-- Query 2
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_i d = cat.id AND cat.id = 1;


The first query uses the WHERE clause and the second one has all the
conditions in the ON clause. Is there anthing wrong with the second
approach in terms of performance? Please suggest.


Thanks in advance
Jackal

[Madhivanan]

Re: Adding conditions in the ON clause of a JOIN

On Feb 12, 12:10 pm, jackal_on_w...@ yahoo.com wrote:

Hi Faculties,
    I have two queries which give me the same output.
>
   -- Query 1
   SELECT prod.name, cat.name
   FROM products prod INNER JOIN categories cat
       ON prod.category_i d = cat.id
   WHERE cat.id = 1;
>
  -- Query 2
   SELECT prod.name, cat.name
   FROM products prod INNER JOIN categories cat
       ON prod.category_i d = cat.id AND cat.id = 1;
>
  The first query uses the WHERE clause and the second one has all the
conditions in the ON clause. Is there anthing wrong with the second
approach in terms of performance? Please suggest.
>
Thanks in advance
Jackal

No issues if you use INNER JOINs
You may get different results if you use OUTER JOINs

[Plamen Ratchev]

Re: Adding conditions in the ON clause of a JOIN

Nothing wrong, it is more a question of preference. There is no logical
difference between ON and WHERE for inner joins, and has no effect on
performance. I normally prefer to write queries like the first example to
separate join conditions from filters.

There is only one situation where this may matter. SQL Server supports the
proprietary GROUP BY ALL. In that case the rows removed by the WHERE filter
are added back, so moving the filter between ON and WHERE will make a
difference.

Of course, this is valid for inner joins only, for outer joins it does
matter where you place filters and you will get different results.

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

Re: Adding conditions in the ON clause of a JOIN

Correcting your data element names a bit (vague dangling "id" and
"name"; the absurd "category_i d" that changes names from table to
table), you can also write:

SELECT P.product_name, C.foobar_catego ry
FROM Products AS P, VagueCategories AS C
WHERE P.foobar_catego ry = C.foobar_catego ry
AND C. foobar_category = 1;

[Erland Sommarskog]

Re: Adding conditions in the ON clause of a JOIN

(jackal_on_work @yahoo.com) writes:

Hi Faculties,
I have two queries which give me the same output.
>
>
-- Query 1
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_i d = cat.id
WHERE cat.id = 1;
>
-- Query 2
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_i d = cat.id AND cat.id = 1;
>
>
The first query uses the WHERE clause and the second one has all the
conditions in the ON clause. Is there anthing wrong with the second
approach in terms of performance? Please suggest.

As long it's an inner join, it's only a matter of taste. I prefer to
put conditions on keys in the ON clause, and other conditions in the
WHERE clause. That is, if the condition is part of the join at all,
which it is not in this case.

If you have an outer join it's a completely different story, as you
could get different results. Common error:

SELECT ...
FROM a
LEFT JOIN b ON a.col1 = b.col1
WHERE b.type = 'X'

This query is effectively an inner join, because the WHERE clause
filters all rows where b.type are NULL. So here you need to put
b.type in the ON clause.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tony Rogerson]

Re: Adding conditions in the ON clause of a JOIN

SELECT P.product_name, C.foobar_catego ry

FROM Products AS P, VagueCategories AS C
WHERE P.foobar_catego ry = C.foobar_catego ry
AND C. foobar_category = 1;

In SQL Server nobody uses that format any more in fact you'll confuse most
developers and leave yourself open to a cartesian product problem which was
common before we start using the ANSI 92 introduced INNER JOIN syntax.

Best practice definied in Books Online is to use INNER JOIN instead of the
prehistoric ANSI 89 style.

--
Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

[Ramblings from the field from a SQL consultant]

http://sqlserverfaq.com

[UK SQL User Community]

[Peter Deacon]

Re: Adding conditions in the ON clause of a JOIN

"Tony Rogerson" <tonyrogerson@t orver.netwrote in
news:fp0jlr$m43 $1$8300dec7@new s.demon.co.uk:

>SELECT P.product_name, C.foobar_catego ry
> FROM Products AS P, VagueCategories AS C
>WHERE P.foobar_catego ry = C.foobar_catego ry
> AND C. foobar_category = 1;

>
In SQL Server nobody uses that format any more in fact you'll confuse
most developers and leave yourself open to a cartesian product problem
which was common before we start using the ANSI 92 introduced INNER
JOIN syntax.
>
Best practice definied in Books Online is to use INNER JOIN instead of
the prehistoric ANSI 89 style.
>

Personally I get burned *by far* more for forgetting the WHERE clause and
having the conditions mingle with the JOIN operators (rather than the
preferred syntax error) than we ever had cartesian product problems back in
the days when ANSI joins didn't exist.

Cart product production is typically obvious. Weird combinations that can
arise from committing the above mistake are almost always non-trivial and
non-obvious.