I have a query like this:

SELECT  ordIdent.Identifier, ord.OrderId FROM    OrderIdentifier ordIdent         JOIN [order] ord             ON ordIdent.OrderId = ord.OrderId WHERE   ordIdent.Identifier = '29584'         OR ord.ClientOrderId = '29584' 

The idea is that we are searching using the value ‘29584’ and we don’t really know if it is a ClientOrderId or an Identifier, so we want to search both.

This gives really really bad performance. It pulls millions of rows (125 thousand from one and 20 Million from the other)

But this query:

SELECT  ordIdent.Identifier, ord.OrderId FROM    OrderIdentifier ordIdent         JOIN [order] ord             ON ordIdent.OrderId = ord.OrderId WHERE   ord.ClientOrderId = '01193504'   UNION  SELECT  ordIdent.Identifier, ord.OrderId FROM    OrderIdentifier ordIdent         JOIN [order] ord             ON ordIdent.OrderId = ord.OrderId WHERE   ordIdent.Identifier = '01193504' 

has good performance. Normally I would just do this and call it a day.

But my actual query is very large, and I would rather not call it twice (though it is still better than using the “or”.) So before I just set it up to be called twice, I thought I would ask around and see if I have other options.

It seems I should be to do some kind of left join to make this work. But I can’t seem to wrap my head around it.

Is there a way to do this in one query that has a left join instead of an “OR”? (Or some other kind of option?)

UPDATE:
OK, I got thinking about it and I realized I could just factor out the OR into a CTE that uses the union and then use the CTE the actual query.

I will leave this open in case there is a better way to deal with it, but a CTE fixes my issue.