Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SELECT and WHERE without FROM Expand / Collapse
Author
Message
Posted Thursday, June 23, 2011 11:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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'

Post #1130682
Posted Thursday, June 23, 2011 12:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Easy enough!

Amol Naik
Post #1130724
Posted Thursday, June 23, 2011 7:08 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, 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
Post #1130896
Posted Friday, June 24, 2011 8:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 1,715, Visits: 6,265
I wonder which of the following (if either) is more efficient:

select @a = 1 where @b = 0

or

if @b = 0 set @a = 1

Post #1131230
Posted Friday, June 24, 2011 9:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 2,469, Visits: 2,346
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())


Post #1131271
Posted Friday, June 24, 2011 9:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 2,469, Visits: 2,346
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())


Post #1131273
Posted Monday, June 27, 2011 1:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Easy with good fundamental details...



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1131907
Posted Monday, June 27, 2011 3:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
Post #1132523
Posted Friday, March 2, 2012 3:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 10:52 PM
Points: 483, Visits: 244
Good question that was straight forward to figure out.

Thanks.
Post #1261060
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse