SQL Server Left Join With 'Or' Operator

Given how little of the query is being exposed; a very rough rule of thumb is to replace an Or with a Union to avoid table scanning.

Select..LEFT JOIN Child c ON c.ParentAId = a.ParentAId unionSelect..left Join Child c ON c.ParentBId = b.ParentBId

You should take care of using predicates inside On.

"It is very important to understand that, with outer joins, the ON and WHERE clauses play very different roles, and therefore, they aren’t interchangeable. The WHERE clause still plays a simple filtering role—namely, it keeps true cases and discards false and unknown cases. Use something like this and use predicates in where clause. However, the ON clause doesn’t play a simple filtering role; rather, it’s more a matching role. In other words, a row in the preserved side will be returned whether the ON predicate finds a match for it or not. So the ON predicate only determines which rows from the nonpreserved side get matched to rows from the preserved side—not whether to return the rows from the preserved side." **Exam 70-461: Querying Microsoft SQL Server 2012

Here is what I did in the end, which got the execution time down from 52 secs to 4 secs.

SELECT * FROM (    SELECT tpl.*, a.MidParentAId as 'MidParentId', 1 as 'IsMidParentA'     FROM TopLevelParent tpl     INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentIDUNION    SELECT tpl.*, b.MidParentBId as 'MidParentId', 0 as 'IsMidParentA'      FROM TopLevelParent tpl     INNER JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentIDUNION    SELECT tpl.*, 0 as 'MidParentId', 0 as 'IsMidParentA'      FROM TopLevelParent tpl     WHERE tpl.TopLevelParentID NOT IN (       SELECT pa.TopLevelParentID        FROM TopLevelParent tpl       INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentID    UNION       SELECT pa.TopLevelParentID        FROM TopLevelParent tpl       INNER JOIN MidParentB b ON h.TopLevelParentId = tpl.TopLevelParentID    )) tplLEFT JOIN MidParentA a ON a.TopLevelParentId = tpl.TopLevelParentIDLEFT JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentIDLEFT JOIN (        SELECT  [ChildId]                ,[MidParentAId] as 'MidParentId'                ,1 as 'IsMidParentA'        FROM Child c        WHERE c.MidParentAId IS NOT NULL   UNION        SELECT [ChildId]               ,[MidParentBId] as 'MidParentId'               ,0 as 'IsMidParentA'        FROM Child c        WHERE c.MidParentBId IS NOT NULL) AS cON c.MidParentId = tpl.MidParentId  AND c.IsMidParentA = tpl.IsMidParentA

This eliminates the table scanning that was happening, as I have matched the top level record to its midlevel parent up front if it exists, and stamped it on that record.

I have also done the same with the child record meaning I can then just join the child record to the top level record on the MidParentId, and I use the IsMidParentA bit flag to differentiate where there are two identical MidParentIds (ie an Id of 1 for IsMidParentA and IsMidParentB).

Thanks to all who took the time to answer.