Posted by Hugo Kornelis on 10/17/07 21:28

On Wed, 17 Oct 2007 06:44:57 -0700, Mukesh_Singh_Nick@yahoo.com wrote:

>I've come accross queries like this one previously but never got the
>time to carefully study them form some book.
>
>SELECT 1 WHERE 1 = 1
>
>
>1) What do queries like these mean? From common sense, I can deduce
>that it is some kind of a test for a boolean value, but the result is
>already deterministic in the above case (true).
>
>What use is such a query for?

Hi Mukesh_Singh_Nick,

A query exactly like the one above is rather pointless.

A query without FROM clause is sometimes used to return a single row,
based on values that are not from a table - for instance calculations,
variables, etc.

WHERE 1 = 1 is redundant - it means select rows from the source (in this
case the single row consisting of the constant value 1) only if 1 is
equal to 1 - which is of course always true. The only "good" reason for
using WHERE 1 = 1 is if queries are generated dynamically - if you start
with 1 = 1, you can use AND in front of all other tests; without it, you
have to choose WHERE for the first and AND for the rest. Note, though,
that dynamically generating SQL is not something a beginning SQL coder
should ever do - there are way too many risks involved!


>2) What dialect of SQL (ANSI/T-SQL) does MS-SQL Server use?

Transact-SQL, often shortened to T-SQL. Many language elements from
T-SQL are also defined in ANSI, but there are some features that T-SQL
has added in addition to the ANSI standard, and there are also some
features that are defined in the standard but not (yet???) implemented
in T-SQL.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]