What are these queries used for?

[Mukesh_Singh_Nick@yahoo.com]

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?

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

[Steve215]

Re: What are these queries used for?

On Oct 17, 9:44 am, Mukesh_Singh_N. ..@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?
>
2) What dialect of SQL (ANSI/T-SQL) does MS-SQL Server use?

The where clause 'WHERE 1=1' simply returns all rows since 1=1 is
always true.

[Hugo Kornelis]

Re: What are these queries used for?

On Wed, 17 Oct 2007 06:44:57 -0700, Mukesh_Singh_Ni ck@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_Ni ck,

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

[Arto V Viitanen]

Re: What are these queries used for?

Mukesh_Singh_Ni ck@yahoo.com kirjoitti:

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
>
>

I think I have seen similarly queries on some general graphical query
tool. It uses the statement to check that

1) the database driver you named is working
2) the database name you gave is working
3) the username you gave is working

--
Arto Viitanen