SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Additional Criteria in the JOIN Clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/16/2002 :  15:53:21  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Have you ever seen a SELECT statement joining two tables that had something like and Field = Value in the JOIN clause? Did you, like me, ask yourself, "Why did they put that in the JOIN instead of in the WHERE clause?" What difference does it make? Well recently I found out that I needed this functionality for a project at work. Here’s an explanation with samples to show the differences.

Article Link.

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 09/19/2002 :  07:54:52  Show Profile  Reply with Quote
Thanks AjarnMark,

never knew how to explain this, especially to o(O)ffice folks who use Crystal reports and Access with it's criteria GUI.
One time I was in a training class for a software package, we were working thru some examples and I was asking how I would code the AND in the JOIN within a proprietory interface (wizard). Blank stares and "Why would you want to do that" were the responses. At the time I couldn't explain myself.

Voted best SQL forum nickname...."Tutorial-D"
Go to Top of Page

ScottMitchell
Starting Member

USA
6 Posts

Posted - 09/20/2002 :  00:50:27  Show Profile  Visit ScottMitchell's Homepage  Reply with Quote
On a slight tangent, ever since using SQL server I've always used joins in this manner:

SELECT bleh
FROM Table1
INNER JOIN Table2 ON
Table1.ID = Table2.ID

or something like that. I was always told that this was more efficient than:

SELECT bleh
FROM Table1, Table2
WHERE Table1.ID = Table2.ID

However, I fail to see why. Is the reason because SQL is not smart enough to examine the SQL and say, "I have a join," and hence it does the cartesean product of Table1 and Table2 and then does the WHERE? It seems inefficient to do that. IIRC, given a parse tree like:

SELECTION on T1.x = T2.x
|
Cartesian Product
/ T1 T2

is equivalent to:

Inner Join on T1.x = T2.X
/ T1 T2

This, one would think, would be a simple query rewriter optimization. So, my question is, does it really matter? I would hope not, that SQL Server is smart enough to do the optimization itself.

Just curious.
Go to Top of Page

jimmers
Starting Member

12 Posts

Posted - 09/20/2002 :  01:23:37  Show Profile  Reply with Quote
Excellent article 'cause it’s short and clear.
Go to Top of Page

Peter Dutch
Posting Yak Master

Netherlands
127 Posts

Posted - 09/20/2002 :  06:00:06  Show Profile  Reply with Quote
Scott, you're right the Query Processor nowadays is 'clever' enough to first do the join and then apply the where clause.

However, some versions ago (please don't ask which) using the 'Microsoft' style had worse performance.

Check this for more info:

http://www.sql-server-performance.com/tuning_joins.asp

<edit>
added link
</edit>

Edited by - Peter Dutch on 09/20/2002 06:02:08
Go to Top of Page

simondeutsch
Aged Yak Warrior

USA
547 Posts

Posted - 10/31/2002 :  12:39:05  Show Profile  Reply with Quote
Good Article. I had this situation myself with patient insurance. I wanted to get all patients with a particular insurance, and their secondary insurance. If I didn't put an AND in the JOIN clause between the PatientInsurance and InsuranceCompany tables, the WHERE clause would eliminate all patients with no secondary insurance. At first I tried optimizing by removing all ANDs from the JOIN clauses and putting them in the WHERE, but then I realized it doesn't always work.

Sarah Berger MCSD
Go to Top of Page

joysus
Starting Member

2 Posts

Posted - 06/28/2005 :  15:10:39  Show Profile  Reply with Quote
but would it still return the wrong results, if we instead did this:
SELECT T.title_id, T.title, S.qty
FROM titles T
LEFT JOIN sales S on T.title_id = S.title_id
WHERE (S.stor_id = '7131' OR S.stor_id is NULL)
ORDER BY T.title

does it makes more sense to put search criteria into WHERE clause, in case there are more criterias that need to be specially filtered?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/28/2005 :  17:04:54  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by joysus

but would it still return the wrong results, if we instead did this:
SELECT T.title_id, T.title, S.qty
FROM titles T
LEFT JOIN sales S on T.title_id = S.title_id
WHERE (S.stor_id = '7131' OR S.stor_id is NULL)
ORDER BY T.title

does it makes more sense to put search criteria into WHERE clause, in case there are more criterias that need to be specially filtered?




Yes, it would return the wrong results doing it that way. All rows should be returned along with matches in the outer table; your criteria only returns rows that match 7131 or that have no matches at all; any other match is completely suppressed.

The WHERE clause you've suggested is a common error people make all the time.

- Jeff
Go to Top of Page

senthiljams
Starting Member

1 Posts

Posted - 11/28/2008 :  19:53:48  Show Profile  Reply with Quote
This is interesting.
http://www.revealnet.com/newsletter-v7/0306_B.htm

I have faced this issue before and your article has got me confused now more than ever.

Think you suggestion holds good only for inner joins. Am i right?

Good article anyway.
Go to Top of Page

sandhyakalsotra
Starting Member

India
22 Posts

Posted - 09/09/2010 :  16:20:36  Show Profile  Reply with Quote
You have solved my problem that I had been encountering. Actually, we have a table "billprocess" containing units consumed etc of a consumer and another table "revcoll" containing revenue received ( which is obviously not by all consumers) and whenever I joined two tables to get total units consumed and revenue received, i would come up with data of only those consumers who had deposited bills.
thanx a bunch

sandhya
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000