Left Join Table with Exclusive OR

Question

Is there a way to join 2 tables together on one and only one of the possible conditions? Joining on condition "a" or "b" could duplicate rows, but I'm looking to only join once. I came up with a potential solution, but I'm wondering if there is a more slick way to do it.

For example:

SELECT *
FROM TableA a
LEFT JOIN TableB b
    ON a.col1 = b.col1
    OR (a.col1 != b.col1 AND a.col2 = b.col2)

This would join the tables on col1 OR col2 BUT NOT BOTH. Is there a cleaner way of doing this?

if it works for you (returns expected results) then I'd stick with it because when you read this code a year from now, you'll have a better chance of understanding it. Remember, the optimizer can rewrite your query a different way to get the same results. So if "slick" is what it looks like and isn't performance driven, I'm not sure there is a lot of room for improvement. — S3S, May 08 '18 at 21:07
Possible duplicate of [T-SQL XOR Operator](https://stackoverflow.com/questions/5411619/t-sql-xor-operator) — Austin Salonen, May 08 '18 at 21:11
My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 — philipxy, May 10 '18 at 01:45
This is not clear. It is also strangely phrased--per some odd mental model. You have 1 join. What do do "on one and only one of the possible conditions", "could duplicate rows" & "looking to only join once" mean? Your test means `a.col1 = b.col1 OR a.col2 = b.col2`--simpler. What does "on col1 OR col2 BUT NOT BOTH" mean? You mean, exactly 1 equality? That's `a.col1 = b.col1 AND a.col2 <> b.col2 OR a.col1 <> b.col1 AND a.col2 = b.col2`. If you want to learn about coding for performance, read everything in the manual re indexes & optimization. And there are books free online in pdf. Etc. — philipxy, May 10 '18 at 02:03

paparazzo · Answer 1 · 2018-05-08T21:42:34.347

0

Not more efficient but I think more clear

SELECT *
FROM TableA a
LEFT JOIN TableB b
  ON     (a.col1 = b.col1 or  a.col2 = b.col2)
 AND NOT (a.col1 = b.col1 and a.col2 = b.col2)

edited May 08 '18 at 21:42

answered May 08 '18 at 21:10

paparazzo

44,497
23
105
176

2

`AND !(expression)` isn't valid SQL Server Syntax. It would need to be `AND NOT(expression)`. – Thom A May 08 '18 at 21:23

score 0 · Answer 2 · answered May 09 '18 at 02:07

Your method works. If you only want one (or a handful) of columns from b, I would suggest:

SELECT a.*, COALESCE(b.col3, b2.col3)
FROM TableA a LEFT JOIN
     TableB b
     ON a.col1 = b.col1 LEFT JOIN
     TableB b2
     ON a.col1 <> b2.col1 AND a.col2 = b2.col2;

Removing the OR from the JOIN conditions allows the optimizer to generate a better execution plan.

Left Join Table with Exclusive OR

2 Answers2