john.arnott (11/8/2010)
Hmmm. When I run this code to build the table, "Select 1 from #test" returns two rows, each with a "1". It's an implied join, isn't it?
Create Table #Test (a int, b int, C as a+b )go
insert into #test values (3,4)
insert into #test values (1,7)
go
select 1 from #test
go
drop table #test
Nope, no join at all. The logic of the result can be foundd by following the query evaluation steps in their logical order:
1. FROM clause - refers to a single table, so intermediate result set is a copy of that table; two rows, with values {(3,4,7),(1,7,8)}.
2. WHERE clause - not present; intermediate result set unchanged.
3. GROUP BY clause - not present; intermediate result set unchanged.
4. HAVING clause - not present; intermediate result set unchanged.
5. SELECT clause - for each row in intermediate result set, built row in final result set. The intermediate result set has two rows, so the final result set will have two rows as well. The SELECT list includes one column, so the final result set will have one column. And since that single column is defined as the constant value 1, the final result set will consist of two rows, and one column; the colunm is unnamed, typed as integer, and filled with the constant value 1 in both rows.
It's basically the same as when you use SELECT a, b, c, 1 FROM #test - except that you now leave out the a, b, and c columns - and when you have no column left that refers to the table, the results suddenly look a lot weirder than usual.