I didn't know this!

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

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

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.

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

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.

when to use this???

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

Travis,

SELECT 'We have possible outliers'

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

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

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

SELECT 1 WHERE (test clause)

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

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.