SQL Server Left Join With 'or' Operator

Question

I have four tables: TopLevelParent, two mid-level tables MidParentA and MidParentB, and a Child table that can have a parent MidParentA or MidParentB (one or the other midParent must be in place). Both mid-level tables have a parent table, TopLevelParent.

The top level table is as follows:

TopLevelId | Name -------------------------- 1 | name1 2 | name2 

MidParent tables are as follows:

 MidParentAId | TopLevelParentId | MidParentBId | TopLevelParentId | ------------------------------------ ------------------------------------ 1 | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 

The table "Children" looks like this:

 ChildId | MidParentAId | MidParentBId -------------------------------- 1 | 1 | NULL 2 | NULL | 2 

I used the following left join in a larger stored procedure that disconnects, and it looks like the OR statement in the last left join is the culprit:

 SELECT * FROM TopLevelParent tlp LEFT JOIN MidParentA a ON tlp.TopLevelPatientId = a.TopLevelPatientId LEFT JOIN MidParentB a ON tlp.TopLevelPatientId = b.TopLevelPatientId LEFT JOIN Child c ON c.ParentAId = a.ParentAId OR c.ParentBId = b.ParentBId 

Is there a more efficient way to make this connection?

Answer 1

Here is what I did at the end, which reduced lead time from 52 seconds to 4 seconds.

 SELECT * FROM ( SELECT tpl.*, a.MidParentAId as 'MidParentId', 1 as 'IsMidParentA' FROM TopLevelParent tpl INNER JOIN MidParentA a ON a.TopLevelParentId = tpl.TopLevelParentID UNION SELECT tpl.*, b.MidParentBId as 'MidParentId', 0 as 'IsMidParentA' FROM TopLevelParent tpl INNER JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID UNION 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 ) ) tpl LEFT JOIN MidParentA a ON a.TopLevelParentId = tpl.TopLevelParentID LEFT JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID LEFT 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 c ON c.MidParentId = tpl.MidParentId AND c.IsMidParentA = tpl.IsMidParentA 

This eliminates the table scan that occurred because I matched the top-level entry with its mid-level parent, if one exists, and printed it on that entry.

I also did the same with the child entry, which means that I can simply attach the child entry to the top level entry in MidParentId, and I use the IsMidParentA bit flag to distinguish where there are two identical MidParentIds (i.e. Id of 1 for IsMidParentA and IsMidParentB).

Thanks to everyone who took the time to respond.

Answer 2

Considering how little information about the request; a very crude rule of thumb is to replace Or or Union to avoid scanning the table.

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

Answer 3

You must make sure to use predicates inside On.

"It is very important to understand that ON and WHERE clauses play very different roles with external joins, and therefore they are not interchangeable. The WHERE clause still plays a simple filtering role, namely: it stores true cases and discards false and unknown cases. Use that something like this and use the predicates in where where. However, the ON clause does not play a simple filtering role, but rather its more appropriate role. In other words, the line in the saved side will be returned whether the ON predicate found for it matches Niemi or not. Thus, the ON predicate determines which rows from the unsupported side will be matched rows from the stored hand and not return rows from the stored hand. " ** Exam 70-461: Microsoft SQL Server 2012 Query

Answer 4

another way to write it:

LEFT JOIN Child c ON c.ParentAId = COALESCE(a.ParentAId, b.ParentBId) >

Edit

One possible approach is to first query MidParentA, then MidParentB, and then UNION results:

 SELECT tlp.*, a.MidParentAId, null MidParentBId, c.ChildId FROM TopLevelParent tlp LEFT JOIN MidParentA a ON tlp.TopLevelPatientId = a.TopLevelPatientId LEFT JOIN Child c ON c.MidParentAId = a.MidParentAId UNION SELECT tlp.*, null MidParentAId, b.MidParentBId, c.ChildId FROM TopLevelParent tlp LEFT JOIN MidParentB b ON tlp.TopLevelPatientId = b.TopLevelPatientId LEFT JOIN Child c ON c.MidParentBId = b.MidParentBId 

SQLFiddle Demo