How to simplify complex JOIN ON condition

Transact-SQL
1

I have a working but very ugly, redundant join in Transact-SQL. The ON condition is of the form

ON ((A = 1 AND B) OR (A = 0 AND C))

where A, B, and C are themselves complex conditions. In other words, if A is true, B applies, and if A is false, C applies. It seemed like IIF should work so that A can be evaluated only once, but IIF cannot resolve to boolean. Maybe the optimizer only evaluates A once but the code is very ugly. How can I simplify to eliminate the redundant A evaluation in the condition? Thanks for any help.

2

Please supply sample data in the form of a create table script with insert of sample data script, what you've tried and what result you would like.

3

The trick to problems like this is usually to move the calculation for "A" into a CROSS APPLY, then you can refer to the column without forcing re-evaluation of the expression (or being forced to type it out multiple times)

1 Like
4

CASE should evaluate it only once, although it may not help performance of the join, if that is your ultimate goal:

ON 1 = CASE A WHEN 1 THEN B/make B return 1 when true/ WHEN 0 THEN C/1 if true/ END

1 Like
5 
In detail, the solution I settled on took the form
	ON 1 = CASE A
		WHEN 1 THEN
			IIF (B, 1, 0)
		WHEN 0 THEN 
			IIF (C, 1, 0)
		END
Thank you.  It's been a learning experience, which is always good.
6

My nicely-indented code came out left-aligned. Sorry about that. How do you post while keeping indents?

7

image
image.png1869×923 38.7 KB

2 Likes