SQL - Multiple join conditions using OR?
- by Brandi
I have a query that is using multiple joins. The goal is to say "Out of table A, give me all the customer numbers in which you can match table A's EmailAddress with either email_to or email_from of table B. Ignore nulls, internal emails, etc.".
It seems like it would be better to use an or condition in the join than multiple joins since it is the same table. When I try to use AND/OR it does not give the behaviour I expect... AND finishes in a reasonable time, but yields no results (I know that there are matches, so it must be some flaw in my logic) and OR never finishes (I have to kill it).
Here is example code to illustrate the question:
--my original query
SELECT DISTINCT a.CustomerNo
FROM A a WITH (NOLOCK)
LEFT JOIN B e WITH (NOLOCK) ON a.EmailAddress = e.email_from
RIGHT JOIN B f WITH (NOLOCK) ON a.EmailAddress = f.email_to
WHERE a.EmailAddress NOT LIKE '%@mydomain.___'
AND a.EmailAddress IS NOT NULL
AND (e.email_from IS NOT NULL OR f.email_to IS NOT NULL)
Here is what I tried, (I am attempting logical equivalence):
SELECT DISTINCT a.CustomerNo
FROM A a WITH (NOLOCK)
LEFT JOIN B e WITH (NOLOCK)
ON a.EmailAddress = e.email_from OR a.EmailAddress = e.email_to
WHERE a.EmailAddress NOT LIKE '%@mydomain.___'
AND a.EmailAddress IS NOT NULL
AND (e.email_from IS NOT NULL OR e.email_to IS NOT NULL)
So my question is two-fold:
Why does having AND in the above query work in a few seconds and OR
goes for minutes and never completes?
What am I missing to make a logically equivalent statement that
has only one join?
