Select Criteria in a SQL Query 1

[gussifinknottle]

I have seen a query which looks like:


SELECT *
FROM Table1
WHERE 1 = 1
AND <other criteria>




Why is 1 = 1 used?

 

[TomSark]

I've seen this trick before, but like this:

-- some no rows condition
SELECT *
FROM Table
WHERE 1 = 0 -- always return 0 rows

This trick above is to ensure that the column names will get passed back to a client.

As far as the 1 = 1, and then an AND following it, makes no sense. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[FGS]

Here is a wordy explanation from Pervasive that may explain why you have seen that construct before - it has to do with one of the options in setting up an ODBC data source:

Scalable SQL-style null handling vs. ODBC-style null handling

Scalable SQL determines null values by examining the data for a given row and column, looking for a sentinel value in each byte of the column. For example, the default value for a column of type NUMERIC is the space character.
For most data types, there is a valid sentinel value outside the scalar range of the type. However, for a few types, such as INT(2) and INT(4), this is not the case. The default sentinel value for integer types is zero, and an integer with a zero in all bytes has the legal scalar value zero.
Some ODBC-enabled applications construct WHERE clauses that test a column value with an IS NOT NULL construct, resulting in the exclusion of rows where every byte of the test columns value is the sentinel value. This can result in the inadvertent exclusion of rows from a result set.
The ODBC Interfaces report as non-nullable any data type that does not have a possible null value outside its scalar range. Nonetheless, some ODBC-enabled applications still construct tests of the type described in the preceding paragraph.

To allow these applications to operate correctly, the ODBC Interface modifies the SQL statement substituting WHERE 0 = 1 for WHERE MY_INT_COLUMN IS NULL and WHERE 1 = 1 for WHERE MY_INT_COLUMN IS NOT NULL. This behavior is applied whenever a test for null status is applied to a column which the ODBC Interface declares as not nullable.
Some programmers may want to expose the native null handling of Scalable SQL, which requires that the ODBC Interfaces null handling behavior be disabled. To accomplish this, set the Scalable SQL Nulls check box in the Setup or SQLDriverConnect dialog. When passing a connect string to SQLDriverConnect or SQLBrowseConnect, pass the attribute value pair NULLENABLED=yes.

Fred

 

[ducksoup]

Also, in the case of a client, it returns all the rows as the default.

sql = 'SELECT * FROM Employees WHERE 1 = 1 '
if (response('getNewEmployees') <> '')
sql = sql & ' AND WHERE timeEmployed < 6'

Then if getNewEmployees is not selected, we get all the employees. __________________________________
You don't see with your eyes, you perceive with your mind.