THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

I didn't know this!

Everyone else probably knew, but I didn't know that you could do the following:

SELECT 'abc'
WHERE <some where clause conditions>;

I probably didn't really miss anything, but was surprised that this worked! I mean, a SELECT statement that has a WHERE clause but without also having a FROM clause.

Published Friday, June 19, 2009 3:10 PM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

Yes, this is actually documented in BOL under From clause

The FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).

here is another one

select 1 + 1

where 2=2

the where clause basically just returns true or false and if it is true it does the select

June 19, 2009 2:26 PM
 

Travis Staker said:

I knew you could do it but I've never figured out why you'd want to do it.  Any ideas?

June 19, 2009 3:31 PM
 

Linchi Shea said:

I don't know either. That's why I'm not sure I actually missed anything by not knowing it.

But I guess you could replace the following:

IF (blah blah)

  SELECT @abc = ...

with

  SELECT @abc = ...

   WHERE blah blah -- if blah blah is a valid WHERE clause condition

Not sure why you'd want to do that though.

June 19, 2009 4:21 PM
 

andyleonard said:

I use this to generate dummy data in an OLE DB Source in SSIS. Cool way to populate the data flow pipeline without actually hitting a table.

:{> Andy

June 19, 2009 6:32 PM
 

Jeff said:

I've worked mostly with SQL Server and didn't realize that there are some database products where you couldn't do a select without a FROM clause.

SELECT @abc = ...

Thanks for pointing out that you can use the WHERE without having the from with a select like this...  Seems like an interesting trick to know about.

Thanks!

Jeff

PS.  For more information on databases that require a "dummy" table, check out http://troels.arvin.dk/db/rdbms/#other-dummy_table or read all about it in Learning SQL 2nd Edition by O'Reilly Press.

June 19, 2009 7:19 PM
 

jackson christian said:

when to use this???

June 20, 2009 1:49 AM
 

Bill Plander said:

You could use it as an inline "if" function....good for maybe messing with the junior DBA's heads

Declare @SomeValue as Int,@CompareValue as Int, @Result as Int

Select @SomeValue = 10,@CompareValue = 20

Select @Result = Coalesce((Select 1 where @SomeValue=@CompareValue),0)

Print @Result

-- same as

Select @Result = Case When @SomeValue = @CompareValue then 1 else 0 end

Print @Result

-- or

If @SomeValue=@CompareValue

Select @Result = 1

else

Select @Result = 0

Print @Result

June 20, 2009 4:36 AM
 

Alex Kuznetsov said:

Travis,

SELECT 'We have possible outliers'

WHERE EXISTS(SELECT 1 FROM dbo.SomeTable WHERE <some criteria>);

June 20, 2009 10:06 AM
 

clydedoggie said:

I've used it to place 'All' at the top of a list. For example:

Select -1 as ID, 'All' as Customer Where 1-1

Union All

Select ID, Customer From Customers

June 20, 2009 10:53 PM
 

Brian Tkatch said:

It is pretty neat. I use it when testing WHERE clauses:

SELECT 1 WHERE (test clause)

June 22, 2009 8:09 AM
 

Dylan said:

Probably my favorite way to duplicate table schema (I know this was about not having a from clause, but boolean where clauses are cool!)

SELECT *

INTO [newTable]

FROM [oldTable]

WHERE 1=2

June 23, 2009 11:06 AM
 

Param said:

Ok...

I think you want to know what is the difference between having Select clause with “from” and without “from” clause.

So when you want to return some data in database you write select statement, its always not necessary that you retrieve data from any database objects as explained earlier by few guys you can use select to assign some value in a variable or using some if conditions.  

But when  you want to retrieve data from table / view you have to write from clause.

Where clause is not dependent on from clause, let me split the query in three parts 1) select statement (with or without from) 2) where condition 3) other conditions like order by group by having etc.

Now as you can see statement 2 (Where clause) is not dependent on statement 1, you use statement 2 can be used to filter the records returned from statement 1.

June 24, 2009 12:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement