TSQL Left Join with multiple right hand rows

Question

When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right?

I'm trying to use this to exploit an ordering on the right table.

so

Select ColA, ColB, ColC 
from T
Left Outer Join 
   (Select ColA, ColB, ColC 
   from T--CLARIFIED, this is a self join.
   Order by TopColumn Desc) AS OrderedT(ColA, ColB, ColC) 
   On T.ColA = OrderedT.ColA

I would expect to retrieve all the ColA's in Table, and all the first row in the set of ColA results for my left join based on my ordering.

Is there any guarantee made on this by the language or server?

Answer 1

Not that simple. The LEFT JOIN returns all matching right-hand rows. So the question on guarantee here is not really relevant. You'd have to do something with a subquery to get the single row you need, using TOP 1 in the subquery.

Answer 2

A LEFT JOIN returns all left hand rows satisfying any WHERE criteria regardless of whether there is a matching row on the right (on the join key(s)). Columns in the right table will be returned as NULL where there is no match on join key.

Answer 3

I believe you need this...

select T.ColA, T.ColB, T.ColC 
from T
inner join
   (select ColA, max(TopColumn) MaxTopColumn
   from T
   group by ColA) OrderedTable
   on T.ColA = OrderedTable.ColA and T.TopColumn = OrderedTable.MaxTopColumn

Fairly common query for versioned tables, requires an inner join to a max query.

The table name "Table" doesn't help matters, I've renamed it T.