ROW_NUMBER and LEFT JOIN

Hi,

I don't have a problem, but I have run into an SQL 2005 "behavior" that I can't explain. So, I thought I'd ask here.

My question is how come that the below script will return 1 row if DISTINCT keyword is present and 3 rows if it's not? I would have guessed that it should return 3 rows for both versions of the batch.

Declare @.Movies TABLE(ID int,Namenvarchar(100))

Declare @.Comments TABLE(ID int, MovieID int, Comment nvarchar(100))

INSERTINTO @.Movies VALUES(1,'Pulp Fiction')

INSERTINTO @.Comments VALUES(1, 1,'Good movie.')

INSERTINTO @.Comments VALUES(2, 1,'Sucked!')

INSERTINTO @.Comments VALUES(3, 1,'Terrific.')

SELECT

DISTINCT

ROW_NUMBER()OVER(ORDERBY M.ID DESC)AS RowNum,

M.ID

FROM

@.Movies AS M

LEFTJOIN

@.Comments AS C

ON

C.MovieID = M.ID

GO

In the Execution Plan I can see that, if the keyword DISTINCT is present, SQL won't "touch" the Comments table, and if DISTINCT is present it will deal with the Comments table, so that gives me a clue, but I'd still like to hear an offical explanation if there is one.

Thanks.

Since you haven't included any columns from the @.Comments table, the "distinct" will cause the same number of rows to be returned with or without the left join to that table, so the query does not require access to @.Comments.

Ron Rice