SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT and WHERE without FROM


SELECT and WHERE without FROM

Author
Message
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3059
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
AmolNaik
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 1234
Easy enough!

Amol Naik
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 237
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
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2819 Visits: 8084
I wonder which of the following (if either) is more efficient:

select @a = 1 where @b = 0



or

if @b = 0 set @a = 1


Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5079 Visits: 3327
Toreador (6/24/2011)
I wonder which of the following (if either) is more efficient:

select @a = 1 where @b = 0



or

if @b = 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 = 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 = 0
set @i = @i + 1
end

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




I run on tuttopodismo
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5079 Visits: 3327
Toreador (6/24/2011)
I wonder which of the following (if either) is more efficient:

select @a = 1 where @b = 0



or

if @b = 0 set @a = 1



The best one seems to be "if" + "select" if @b = 0 (see last test):
declare @d datetime
,@a int
,@b int
,@i int

set @b = 0

set @d = getdate()
set @i = 0
while @i < 1000000
begin
if @b = 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 = 0
set @i = @i + 1
end

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

set @d = getdate()
set @i = 0
while @i < 1000000
begin
if @b = 0 select @a = 1
set @i = @i + 1
end

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




I run on tuttopodismo
Danny Ocean
Danny Ocean
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1662 Visits: 1549
Easy with good fundamental details...

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
Peter Trast
Peter Trast
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 655
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
zymos
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 259
Good question that was straight forward to figure out.

Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search