Menu
If you have an OR condition in the JOIN - and there is no possibility that the values in the OR statement overlap...then you can convert it to a UNION ALL. If the values overlap it would require a UNION which may not improve performance over the JOIN.
With the AND in the inner join you can specify it even more. Join the tables on the columns, where A1.
It's possible, though that you might want to filter one or both of the tables before joining them. For example, you only want to create matches between the tables under certain circumstances.
Doing an INNER join is not so bad, it is what databases are made for. The only time it is bad is when you are doing it on a table or column that is inadequately indexed.
This kind of JOIN is not optimizable to a HASH JOIN or a MERGE JOIN.
It can be expressed as a concatenation of two resultsets:
SELECT *
FROM maintable m
JOIN othertable o
ON o.parentId = m.id
UNION
SELECT *
FROM maintable m
JOIN othertable o
ON o.id = m.parentId
, each of them being an equijoin, however, SQL Server's optimizer is not smart enough to see it in the query you wrote (though they are logically equivalent).
I use following code for get different result from condition That worked for me.
Select A.column, B.column
FROM TABLE1 A
INNER JOIN
TABLE2 B
ON A.Id = (case when (your condition) then b.Id else (something) END)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With