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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Comments

[Joel Coehoorn]

The sql-server tag means MS SQL Server. There are also separate tags for 'oracle', 'mysql', 'ms-access', and other database flavors. Again: check the prompts when posting the question. Any tag with a number less than 10 after it is almost certainly wrong.

[Spence]

But I can't use Top 1 as an aggregate. This is solely to solve the problem of fetching the top row FOR EACH ColA which I am trying to avoid a cursor to do.

[Mitch Wheat]

@David M: "The LEFT JOIN returns all matching right-hand rows" - isn't that the wrong way around?

[David M]

No. A LEFT JOIN returns all rows from the left, and all matching rows from the right.

[Spence]

A left outer join will return one row for every row in the table on the left, with the joined columns set to null or a value. Regardless of the number of rows int he right hand side, it will only return a row for every row on the left. AFAIK.

[David M]

Sorry, not correct. If there are multiple matching rows on the right, multiple rows will be returned.