Article: Additional Criteria in the JOIN Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Additional Criteria in the JOIN Clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-16 : 15:53:21

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

571 Posts

Posted - 2002-09-19 : 07:54:52

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"

ScottMitchell

6 Posts

Posted - 2002-09-20 : 00:50:27

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.

jimmers
Starting Member

12 Posts

Posted - 2002-09-20 : 01:23:37

Excellent article 'cause it’s short and clear.

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-09-20 : 06:00:06

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

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-10-31 : 12:39:05

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

joysus
Starting Member

2 Posts

Posted - 2005-06-28 : 15:10:39

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?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-28 : 17:04:54

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

senthiljams
Starting Member

1 Post

Posted - 2008-11-28 : 19:53:48

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.

sandhyakalsotra
Starting Member

22 Posts

Posted - 2010-09-09 : 16:20:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources