SQLServerCentral / Discuss Content Posted by Patrick LeBlanc / Article Discussions / Article Discussions by Author / SELECT and WHERE without FROM / Latest Posts

RE: SELECT and WHERE without FROM

zymos — Fri, 02 Mar 2012 15:07:48 GMT

Good question that was straight forward to figure out.Thanks.

RE: SELECT and WHERE without FROM

Peter Trast — Mon, 27 Jun 2011 15:41:05 GMT

I have used this before to test something... but I can't remember what it was!!!Except I usedWHERE 1=0My brain is all RAM and no HD... now where did that swapfile go to?...

RE: SELECT and WHERE without FROM

Danny Ocean — Mon, 27 Jun 2011 01:17:16 GMT

Easy with good fundamental details...

RE: SELECT and WHERE without FROM

Carlo Romagnano — Fri, 24 Jun 2011 09:52:46 GMT

[quote][b]Toreador (6/24/2011)[/b][hr]I wonder which of the following (if either) is more efficient:[code="sql"]select @a = 1 where @b = 0[/code]or[code="sql"]if @b = 0 set @a = 1[/code][/quote]The best one seems to be "if" + "select" if @b = 0 (see last test):[code="sql"]declare @d datetime,@a int ,@b int,@i intset @b = 0set @d = getdate()set @i = 0while @i < 1000000begin if @b = 0 set @a = 1set @i = @i + 1end print datediff(millisecond,@d,getdate())set @d = getdate()set @i = 0while @i < 1000000begin select @a = 1 where @b = 0set @i = @i + 1end print datediff(millisecond,@d,getdate())set @d = getdate()set @i = 0while @i < 1000000begin if @b = 0 select @a = 1 set @i = @i + 1end print datediff(millisecond,@d,getdate())[/code]

RE: SELECT and WHERE without FROM

Carlo Romagnano — Fri, 24 Jun 2011 09:46:52 GMT

[quote][b]Toreador (6/24/2011)[/b][hr]I wonder which of the following (if either) is more efficient:[code="sql"]select @a = 1 where @b = 0[/code]or[code="sql"]if @b = 0 set @a = 1[/code][/quote]"if" runs better[code="sql"]declare @d datetime,@a int ,@b int,@i intset @d = getdate()set @i = 0while @i < 1000000begin if @b = 0 set @a = 1set @i = @i + 1end print datediff(millisecond,@d,getdate())set @d = getdate()set @i = 0while @i < 1000000begin select @a = 1 where @b = 0set @i = @i + 1end print datediff(millisecond,@d,getdate())[/code]

RE: SELECT and WHERE without FROM

Toreador — Fri, 24 Jun 2011 08:55:17 GMT

I wonder which of the following (if either) is more efficient:[code="sql"]select @a = 1 where @b = 0[/code]or[code="sql"]if @b = 0 set @a = 1[/code]

RE: SELECT and WHERE without FROM

Dhruvesh Shah — Thu, 23 Jun 2011 19:08:48 GMT

[quote][b]Carlo Romagnano (6/23/2011)[/b][hr][quote]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. [/quote]This is true only if you do not use COUNT(*):[code="sql"]select count(*) cntwhere 1 =0[/code]One row is returned despite the WHERE condition is false.[/quote]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

RE: SELECT and WHERE without FROM

AmolNaik — Thu, 23 Jun 2011 12:03:15 GMT

Easy enough!

RE: SELECT and WHERE without FROM

john.arnott — Thu, 23 Jun 2011 11:21:03 GMT

[quote][b]sknox (6/23/2011)[/b][hr].... the where condition filters the input to the aggregate functions, not the output from them....[/quote]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. [code="sql"]Select tbl_a.RidiculouslyComplicatedColumnName as MyData from MyVeryLongTableName tbl_a join AnotherVeryLongTableName tbl_b on tbl_b.key = tbl_a.keywhere MyData = 'SearchString'[/code]

RE: SELECT and WHERE without FROM

KWymore — Thu, 23 Jun 2011 10:44:32 GMT

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.

RE: SELECT and WHERE without FROM

mtillman-921105 — Thu, 23 Jun 2011 09:40:42 GMT

Terrific question! Thanks.

RE: SELECT and WHERE without FROM

SQLRNNR — Thu, 23 Jun 2011 09:13:18 GMT

straight up, fundamental question.

RE: SELECT and WHERE without FROM

sknox — Thu, 23 Jun 2011 09:00:40 GMT

[quote][b]Carlo Romagnano (6/23/2011)[/b][hr][quote]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. [/quote]This is true only if you do not use COUNT(*):[code="sql"]select count(*) cntwhere 1 =0[/code]One row is returned despite the WHERE condition is false.[/quote]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[code]select sum(5)where 1 = 0[/code]

RE: SELECT and WHERE without FROM

SanDroid — Thu, 23 Jun 2011 08:22:58 GMT

[quote][b]Andrew Watson-478275 (6/23/2011)[/b][hr]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[code="sql"]SELECT 1[/code]and it worked fine.[/quote]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:

RE: SELECT and WHERE without FROM

phonetictalk — Thu, 23 Jun 2011 08:15:55 GMT

I'm enjoying the T-SQL questions this week!

RE: SELECT and WHERE without FROM

SanDroid — Thu, 23 Jun 2011 08:07:58 GMT

Nice question. It makes me smile to see "where 1 = 1" or "while 1 = 1" in code.

RE: SELECT and WHERE without FROM

jkraemer — Thu, 23 Jun 2011 08:03:50 GMT

I'd have got it right had I not accidentally clicked Parse instead of Execute! :doze:

RE: SELECT and WHERE without FROM

Andrew Watson-478275 — Thu, 23 Jun 2011 03:17:10 GMT

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[code="sql"]SELECT 1[/code]and it worked fine.

RE: SELECT and WHERE without FROM

Koen Verbeeck — Thu, 23 Jun 2011 03:14:16 GMT

Nice question, thanks.

RE: SELECT and WHERE without FROM

Hardy21 — Thu, 23 Jun 2011 02:58:08 GMT

Nice question

RE: SELECT and WHERE without FROM

Stewart "Arturius" Campbell — Thu, 23 Jun 2011 02:28:43 GMT

Having worked with MSSQL, Oracle, Db2, Sybase and Informix databases, the only ones this command will work on is MSSQL and Sybase.All the others require a FROM clause with a SELECT

RE: SELECT and WHERE without FROM

paul s-306273 — Thu, 23 Jun 2011 02:04:27 GMT

[quote][b]Geoff Langdon (6/23/2011)[/b][hr]Interestingly, this gives ORA-00923 error - FROM keyword not found where expected in Oracle.(Hence I got it wrong!)[/quote]Geoff how about: 1 select '123' as col1 2 from dual 3* where 1 =1orhousin@BUSO> /COL---123I was expecting this question to have almost 100% correct answers...

RE: SELECT and WHERE without FROM

Geoff Langdon — Thu, 23 Jun 2011 01:59:24 GMT

Interestingly, this gives ORA-00923 error - FROM keyword not found where expected in Oracle.(Hence I got it wrong!)

RE: SELECT and WHERE without FROM

Carlo Romagnano — Thu, 23 Jun 2011 01:27:13 GMT

[quote]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. [/quote]This is true only if you do not use COUNT(*):[code="sql"]select count(*) cntwhere 1 =0[/code]One row is returned despite the WHERE condition is false.

RE: SELECT and WHERE without FROM

paul.knibbs — Thu, 23 Jun 2011 01:06:33 GMT

I looked at the question, thought, "Surely this is obvious"--went and ran it in SSMS just to be sure I wasn't missing something. It was only when I came here I realised it was talking about the missing FROM clause! :-)

RE: SELECT and WHERE without FROM

sayyad.azhar — Thu, 23 Jun 2011 00:51:39 GMT

Very Good basic question !

RE: SELECT and WHERE without FROM

mohammed moinudheen — Wed, 22 Jun 2011 22:41:14 GMT

Good question. I got to know something.

RE: SELECT and WHERE without FROM

bitbucket-25253 — Wed, 22 Jun 2011 20:23:17 GMT

Nice question on fundamentals .. thanks

SELECT and WHERE without FROM

Patrick_LeBlanc — Wed, 22 Jun 2011 20:22:35 GMT

Comments posted to this topic are about the item [B]SELECT and WHERE without FROM[/B]