Problem with INNER JOIN using two conditions

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with INNER JOIN using two conditions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-14 : 13:32:16

Mark writes "I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.

---------------------------

SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)

---------------------------

This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?

Another possible solution is if I were to use "WHERE/IN" like this:

---------------------------

SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)

---------------------------

This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.

I'm using SQL Server 2000 on Windows XP Pro."

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-14 : 14:31:03

I suspect the problem is the large amount of data... give it time... but the query may not be returning the data you need.

Here's a variation to try:


SELECT * 
  FROM jobs J
  INNER JOIN contacts C
    ON (C.contact_id = J.owner OR C.contact_id = J.assignee)
    AND C.deleted = 0 -- I've put this here for example.  I prefer the WHERE that follows
  WHERE C.deleted = 0

Another concern is that in posting a simplified query, you may have eliminated the element in the query that is causing the problem.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-14 : 15:04:49

Jeff's "SQL Rules to live by" #12: Never, ever JOIN with an OR in your join expression.

Instead, use two LEFT OUTER JOINS to two alias's of the same table, one on each condition, and then use a CASE to determine which alias to return data from.

i.e.,


SELECT jobs.*, CASE WHEN C1.Contact_id is Null THEN ... ELSE .. END as ContactInfo 
FROM jobs
LEFT OUTER JOIN
  Contacts C1 ON 
    C1.Deleted = 0 AND C1.contact_id = Jobs.Owner
LEFT OUTER JOIN
  Contacts C2 ON
    C2.Deleted = 0 AND C2.Contact_ID = jobs.assignee

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-15 : 08:25:13

quote:
Originally posted by jsmith8858

Jeff's "SQL Rules to live by" #12: Never, ever JOIN with an OR in your join expression.

Does an OR kill performance, (why?), create unwanted JOINs, or what?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-15 : 11:19:46

Performance, clarity, and results are the 3 reasons.

It almost never returns what you think it should. If both conditions in the OR are true, you get two rows back instead of one so your results are duplicated. If your requirements are to check a table for one condition, and if there's no match, use a different condition, than that is two LEFT OUTER JOINS, not an "OR" in a single join.

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-15 : 12:00:28

quote:
Originally posted by jsmith8858

If both conditions in the OR are true, you get two rows back instead of one so your results are duplicated.

I'd like to be the first to acknowledge that separate JOINs may be a better solution for many JOIN conditions.

However, if both conditions in the OR are true, it returns one row, not two !

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-15 : 21:24:04

No ....

The data:

t1: a,b,c
t2: b, x

The Goal: return each row from t1, plus the match (if any) from t2. if no rows match, return row x from t2.

what does

select t1.id, t2.id
from t1
inner join t2
on t1.id=t2.id or t2.id='x'

return?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-16 : 04:13:17

quote:
Originally posted by jsmith8858
what does

select t1.id, t2.id
from t1
inner join t2
on t1.id=t2.id or t2.id='x'

return?

Hmm, this line of questioning sounds distinctly Socratic.

I'll play along!

It returns


t1.id   t2.id
-----   -----
b       b
a       x
b       x
c       x

of course. Now why would that surprise anyone?

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-16 : 08:23:46

The JOIN mentioned at the start of this thread was: return row with T1.ID when any row matches T2.ID OR T2.ID2 (paraphrased)

I'd argue something like: Dr. Cross Join has posted a different JOIN condition. Sure, it has an OR in it, but, under any circumstances, it's more difficult: return row with T1.ID when any row T2.ID matches, if none found, then match T2.ID = 'x'?

INNER JOIN T1.ID = T2.ID OR (T2.ID = 'X' AND T1.ID NOT IN (SELECT T2.ID FROM T2))

This pretzel of a JOIN would be readable if it were two LEFT OUTER JOINS as the Dr. recommended.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-16 : 09:02:32

Oh, so you're saying that "The Goal: return each row from t1, plus the match (if any) from t2. if no rows match, return row x from t2." meant that the query was supposed to return what your query did, not what the one Jeff wrote did.
Yes, that does make more sense: I just assumed that it was badly phrased.

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-16 : 09:23:34

quote:
Originally posted by Arnold Fribble

Oh, so you're saying that "The Goal: return each row from t1, plus the match (if any) from t2. if no rows match, return row x from t2."

Well, Dr. Cross Join said that was the goal!

The secondary discussion in this thread is whether using an OR condition in a JOIN is a bad thing (I posted a solution using an OR earlier)... Dr. Cross Join's "Rules to Live By", No. 12 states: two LEFT OUTER JOINS is always better than an OR (to avoid duplicate rows, performance, clarity).

In the case of the original problem posted in this thread, there doesn't seem to be any threat of duplicate rows when using an OR in the JOIN.

On the flip side, I've had JOIN conditions return duplicate rows for reasons similar to the concern in rule 12, but not in this case.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-16 : 16:15:37

quote:

In the case of the original problem posted in this thread, there doesn't seem to be any threat of duplicate rows when using an OR in the JOIN.

On the flip side, I've had JOIN conditions return duplicate rows for reasons similar to the concern in rule 12, but not in this case.

You'd have to see the data. What if the owner and the assignee both have a matching contact? Then you get 2 rows returned, instead of one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources