473,228 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,228 software developers and data experts.

Adding conditions in the ON clause of a JOIN

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_id = cat.id
WHERE cat.id = 1;

-- Query 2
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_id = 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
Feb 12 '08 #1
6 3079
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_id = cat.id
* *WHERE cat.id = 1;

* -- Query 2
* *SELECT prod.name, cat.name
* *FROM products prod INNER JOIN categories cat
* * * *ON prod.category_id = 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
Feb 12 '08 #2
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

Feb 12 '08 #3
Correcting your data element names a bit (vague dangling "id" and
"name"; the absurd "category_id" that changes names from table to
table), you can also write:

SELECT P.product_name, C.foobar_category
FROM Products AS P, VagueCategories AS C
WHERE P.foobar_category = C.foobar_category
AND C. foobar_category = 1;
Feb 12 '08 #4
(ja************@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_id = cat.id
WHERE cat.id = 1;

-- Query 2
SELECT prod.name, cat.name
FROM products prod INNER JOIN categories cat
ON prod.category_id = 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 12 '08 #5
SELECT P.product_name, C.foobar_category
FROM Products AS P, VagueCategories AS C
WHERE P.foobar_category = C.foobar_category
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]

Feb 14 '08 #6
"Tony Rogerson" <to**********@torver.netwrote in
news:fp*******************@news.demon.co.uk:
>SELECT P.product_name, C.foobar_category
FROM Products AS P, VagueCategories AS C
WHERE P.foobar_category = C.foobar_category
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.
Feb 23 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: 6thirty | last post by:
Hi, I've created a stocktaking database using Access XP. This is indexed by two fields - part number and shelf location. I am currently inputting all the data via a form. When I have entered a...
4
by: John Siracusa | last post by:
I recently had a problem where a bunch of postgres backends were taking up huge amounts of CPU time. I found a bunch of log messages like this: NOTICE: adding missing FROM-clause entry for...
1
by: Martijn van Oosterhout | last post by:
Today I got the error: ERROR: FULL JOIN is only supported with mergejoinable join conditions Which is really annoying since a full join is exactly what I wanted. I guess the alternative is to...
3
by: C G | last post by:
Dear All, I have a simple join query SELECT c1 FROM t1 INNER JOIN t2 ON t2.c2 = t1.c2 WHERE t3.c3= t2.c3; Which gives the expected result but I get the message NOTICE: adding missing...
3
by: lagj | last post by:
Using MySQL 5.0.15 I am no expert so maybe I am deeply misundertanding how this should work. I have a relatively complex quey with joins and a subquery (it is probably far from optimized, but...
5
by: consonanza | last post by:
I am working on a report filter form. It has 2 combo boxes (cmboSelectSubject and cmboSelectCategory) to select criteria. Selecting an entry in combo 1 restricts the options available in combo 2....
69
by: kabradley | last post by:
Alrighty Guys and Gals, I have another question that I hope you all can help me with. I have a report that uses a cross-tab query as its record source. This cross-tab query is getting all of its...
1
by: mtnbikur | last post by:
Lots of reading out there says you should only place “join criteria” in the join clause. That makes perfect sense. What about other search criteria? I’ve got other criteria, ie. cst_delete_flag =...
12
by: =?ISO-8859-1?Q?Ren=E9?= | last post by:
Hi, is there a rule of thumb what is better/faster/more performant in SQL Server 2005? a) SELECT * FROM A INNER JOIN B ON B.ID = A.ID AND B.Cond1 = 1 AND B.Cond2 = 2 b) SELECT * FROM A INNER...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.