SQL Join on Multiple condition

Question

I have two tables tabA & tabB

joining these two tables using the below code:

 Select *
 from tabA X
 join tabB Y
 on (X.id1 = Y.id1
 Or X.id2 = Y.id2
 Or X.id3 = Y.id3)

Now, my requirement is like: I want if the 1st join condition is true, then the rest two join conditions on id2 & id3 will be discarded. Similarly if the 2nd join condition on id2 is true, then the rest of the join conditions on id1 & id3 will be discarded and so on.

Can you please tell how can I achieve this??

SQL should stop matching when it finds the first TRUE in the OR chain, so I think the sentence about discarding doesn't actually matter. — sevensevens, Jul 29 '22 at 17:29
No,. Actually it will try to match all the 3 conditions, since it is in OR chain — Johnson, Jul 29 '22 at 17:37

sevensevens · Answer 1 · 2022-07-29T17:51:17.987

I would try UNION

SELECT * FROM TabA JOIN TabY ON(X.id1 = Y.id1)
UNION
SELECT * FROM TabA JOIN TabY ON(X.id2 = Y.id2)
UNION
SELECT * FROM TabA JOIN TabY ON(X.id3 = Y.id3)

Now, my requirement is like: I want if the 1st join condition is true, then the rest two join conditions on id2 & id3 will be discarded. Similarly if the 2nd join condition on id2 is true, then the rest of the join conditions on id1 & id3 will be discarded and so on.

I don't think this condition matters. How would the result set be different? If any of the conditions are true, the row will be included once because of the way SQL creates the result set. UNION will make SQL get the result set for the 3 queries then combine them and remove things with the same primary key.

The other reason I don't think it matters is depending on the optimizer in your SQL engine, it'll "short circuit" the OR chain as soon as one matches, which would fulfill your requirement. This is dependent on both the SQL flavor and the exact query and optimizer.

You may also be able to use your ON(... OR ... OR ...) with a SELECT DISTINCT

Actually, in my case, when two conditions are becoming true ex. On id1 & on id2; it is producing duplicate tuples,---which I don't want. I want the join to take place on any 1 particular condition; not more than 1 — Johnson, Jul 29 '22 at 17:49
@mainak09 - Using union should mean that there is a unique primary key for each row — sevensevens, Jul 29 '22 at 17:50

score 1 · Answer 2 · answered Jul 29 '22 at 18:01

1

Join tabA with LEFT joins to 3 copies of tabB each joined only if the previous copies failed and in the WHERE clause filter out any rows that did not match at all:

SELECT *
FROM tabA a
LEFT JOIN tabB b1 ON b1.id1 = a.id1
LEFT JOIN tabB b2 ON b2.id2 = a.id2 AND b1.id1 IS NULL
LEFT JOIN tabB b3 ON b3.id3 = a.id3 AND b1.id1 IS NULL AND b2.id2 IS NULL
WHERE COALESCE(b1.id1, b2.id2, b3.id3) IS NOT NULL;

answered Jul 29 '22 at 18:01

forpas

160,666
10
38
76

Thanks, Good approach, but didn't understand the where part??? – Johnson Jul 29 '22 at 20:07
@mainak09 without the WHERE clause the result would be all the rows of tabA because I'm using LEFT joins. With the WHERE clause the result is only the rows where there is a match. – forpas Jul 29 '22 at 20:44

score 1 · Answer 3 · answered Jul 29 '22 at 18:35

With first_match as (select *
                       from tabA X
                       join tabB Y
                         on (X.id1 = Y.id1)),
    second_match as (select * from 
                       from tabA X
                       join tabB Y
                         on (X.id2 = Y.id2)
                      where not exists (select 1 
                                          from first_match f  
                                          where f.id1=y.id1)), 
      third_match as (select * from 
                        from tabA X
                        join tabB Y
                          on (X.id3 = Y.id3)
                       where not exists (select 1  
                                           from first_match f  
                                          where f.id1=y.id1)
                         AND not exists (select 1 from second_match s  where s.id2=y.id2))
select * from first_match
union all
select * from second_match
union all
select * from third_match;

First match will return all rows that matches the condition. Second will return matches but will skip the records that has been already retrieved by first. Similarly, third will return rows that has a match and has been skipped by first and second.

score 0 · Answer 4 · answered Jul 29 '22 at 17:28

You can score the matches and pick the one with the best score. For example:

select *
from (
  select x.*, y.*,
    row_number() over(partition by x.id1
      order by case when x.id1 = y.id1 then 3
                    when x.id2 = y.id2 then 2
                    else 1 end 
      desc
    ) as rn
  from tabA x
  join tabB y on x.id1 = y.id1
              or x.id2 = y.id2
              or x.id3 = y.id3
) x
where rn = 1

score 0 · Answer 5 · answered Jul 29 '22 at 17:32

     Select *
,JoinSource = case 
      when X.id1 = Y.id1 then 'id1' 
when X.id2 = Y.id2 then 'id2'
when X.id3 = Y.id3 then 'id3'
end 
 from tabA X
 cross join tabB Y
where
  case 
      when X.id1 = Y.id1 then 1 
when X.id2 = Y.id2 then 1
when X.id3 = Y.id3 then 1
end = 1

Remeber this code can lead a tablescan. You can consider using 3 queries with UNION (try first, if not match, try second).

SQL Join on Multiple condition

5 Answers5