How to write optimized query for multiple prioritize conditional joins in SQL server?

Question

The scenario I'm after for is :

Result = Nothing
CollectionOfTables = Tbl1, Tbl2, Tbl3
While(True){
  CurrentTable = GetHighestPriorityTable(CollectionOfTables)
  If(CurrentTable) = Nothing Then Break Loop;
  RemoveCurrentTableFrom(CollectionOfTables)
  ForEach ID in CurrentTable as TempRow {
    If(Result.DoesntContainsId(ID)) Then Result.AddRow(TempRow)
  }
}

Assume I have following three tables.
IdNameTable1, Priority 1
1  John
2  Mary
3  Elsa

IdNameTable2, Priority 2
2  Steve
3  Max
4  Peter

IdNameTable3, Priority 3
4  Frank
5  Harry
6  Mona

Here is the final result I need.

IdNameResult
1  John
2  Mary
3  Elsa
4  Peter
5  Harry
6  Mona

A few tips to keep in mind.
Number of actual tables is 10.
Number of rows per table exceeds 1 Million.
It's not necessary to use join in query, but because of amount of data I'm working with the query must be optimized and used set-operations in SQL not a Cursor script.

What (sub-optimal) solution have you come up with so far? – cf_en Feb 20 '17 at 16:39 — cf_en, Feb 20 '17 at 16:39
@ChrisFlynn Actually I don't know SQL so nothing! – Mohsen Sarkar Feb 20 '17 at 16:41 — Mohsen Sarkar, Feb 20 '17 at 16:41

score 4 · Accepted Answer · answered Feb 20 '17 at 16:41

Here's a way to do it using UNION and ROW_NUMBER():

;With Cte As
(
    Select  Id, Name, 1 As Prio
    From    Table1
    Union All
    Select  Id, Name, 2 As Prio
    From    Table2
    Union All
    Select  Id, Name, 3 As Prio
    From    Table3
), Ranked As
(
    Select  Id, Name, Row_Number() Over (Partition By Id Order By Prio) As RN
    From    Cte
)
Select  Id, Name
From    Ranked
Where   RN = 1
Order By Id Asc;

How to write optimized query for multiple prioritize conditional joins in SQL server?

1 Answers1