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:

1. Why does having AND in the above query work in a few seconds and OR goes for minutes and never completes?
2. What am I missing to make a logically equivalent statement that has only one join?