SELECT and WHERE without FROM

Question

SELECT and WHERE without FROM

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

SanDroid SSChampion Points: 10068 More actions · Answer 1

Andrew Watson-478275 (6/23/2011)
You don't even need the WHERE clause. I've just done an SSIS step where I needed a single row returned, but didn't care about the contents, so I used
SELECT 1
and it worked fine.

I think the concept the question was testing is;

"Do you need a From clause to use a where clause?"

I hope we all understand that a where clause is not needed for a SELECT statement.

i.e. SELECT @ParameterName = @@Version

SELECT @ParameterName

:smooooth:

sknox SSChampion Points: 12388 More actions · Answer 2

Carlo Romagnano (6/23/2011)
If the condition of the WHERE clause validates to true then the result set is returned. If it does not, the result set is empty.
This is true only if you do not use COUNT(*):
select count(*) cnt
where 1 =0
One row is returned despite the WHERE condition is false.

This is true of aggregate functions, not just COUNT(*) -- the where condition filters the input to the aggregate functions, not the output from them.

Try

select sum(5)

where 1 = 0

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

straight up, fundamental question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 4

Terrific question! Thanks.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Ken SSCoach Points: 17191 More actions · Answer 5

Nice question. I have used the "select '123' " or "select 5" syntax before but didn't think to ever try a where clause with it.

john.arnott SSChampion Points: 11882 More actions · Answer 6

sknox (6/23/2011)
.... the where condition filters the input to the aggregate functions, not the output from them.
...

True. And of course this is because the Where clause filters input to the Select, regardless of it using an aggregate function. That's why one may not use an alias in the Where clause.

This code wouldn't work because MyData is not a real columnName in table MyVeryLongTableName.

Select tbl_a.RidiculouslyComplicatedColumnName as MyData

from MyVeryLongTableName tbl_a

join AnotherVeryLongTableName tbl_b on tbl_b.key = tbl_a.key

where MyData = 'SearchString'

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 7

AmolNaik

SSCarpal Tunnel

Points: 4767

June 23, 2011 at 12:03 pm

#1343295

Easy enough!

Amol Naik

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 8

Carlo Romagnano (6/23/2011)
If the condition of the WHERE clause validates to true then the result set is returned. If it does not, the result set is empty.
This is true only if you do not use COUNT(*):
select count(*) cnt
where 1 =0
One row is returned despite the WHERE condition is false.

Good One didn't know that it will still return even if where condition is false.

Not to forget that basic idea of question is select still does return result even if it's missing from clause.

Keep it up...

Cheers

Toreador SSChampion Points: 11360 More actions · Answer 9

I wonder which of the following (if either) is more efficient:

select @a = 1 where @b-2 = 0

or

if @b-2 = 0 set @a = 1

Carlo Romagnano SSC-Insane Points: 22605 More actions · Answer 10

Toreador (6/24/2011)
I wonder which of the following (if either) is more efficient:
select @a = 1 where @b-2 = 0
or
if @b-2 = 0 set @a = 1

"if" runs better

declare @d datetime

,@a int

,@b int

,@i int

set @d = getdate()

set @i = 0

while @i < 1000000

begin

if @b-2 = 0 set @a = 1

set @i = @i + 1

end

print datediff(millisecond,@d,getdate())

set @d = getdate()

set @i = 0

while @i < 1000000

begin

select @a = 1 where @b-2 = 0

set @i = @i + 1

end

print datediff(millisecond,@d,getdate())

Carlo Romagnano SSC-Insane Points: 22605 More actions · Answer 11

Toreador (6/24/2011)
I wonder which of the following (if either) is more efficient:
select @a = 1 where @b-2 = 0
or
if @b-2 = 0 set @a = 1

The best one seems to be "if" + "select" if @b-2 = 0 (see last test):

declare @d datetime

,@a int

,@b int

,@i int

set @b-2 = 0

set @d = getdate()

set @i = 0

while @i < 1000000

begin

if @b-2 = 0 set @a = 1

set @i = @i + 1

end

print datediff(millisecond,@d,getdate())

set @d = getdate()

set @i = 0

while @i < 1000000

begin

select @a = 1 where @b-2 = 0

set @i = @i + 1

end

print datediff(millisecond,@d,getdate())

set @d = getdate()

set @i = 0

while @i < 1000000

begin

if @b-2 = 0 select @a = 1

set @i = @i + 1

end

print datediff(millisecond,@d,getdate())

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 12

Easy with good fundamental details...

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 13

I have used this before to test something... but I can't remember what it was!!!

Except I used

WHERE 1=0

My brain is all RAM and no HD... now where did that swapfile go to?...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

zymos SSCommitted Points: 1960 More actions · Answer 14

Good question that was straight forward to figure out.

Thanks.