Re: NEWBEE: 'WHERE true' question - Mailing list pgsql-novice

I have done this on occasion.  I do it for the reason Murrah Boswell
suggested, namely "so the WHERE field is not blank."  Murrah asked for an
explanation, so here goes:
Every ANDed and ORed part of a WHERE clause evaluates to a true or false
value.  A query returns rows for which the entire WHERE clause evaluates to
true.  For example, if you have the following table tbl_names:
ID | Name
-------------
1 | Joe
2 | Mary
3 | Joe

and a the following SQL statement:
SELECT * FROM tbl_names WHERE Name = 'Joe' AND ID = 3;

for row (1) the WHERE clause evaluates to True AND False (=False)
so the row is not returned

for row (2) the WHERE clause evaluates to False AND False (=False)
so the row is not returned

for row (3) the WHERE clause evaluates to True AND True (=True)
so the row is returned

You can take any SQL WHERE clause, enclose it in parentheses, tack "AND
True" on to the end of it, and it will return exactly what it did before.
If you are building a dynamic SQL statement that may have no real WHERE
conditions, you can use this as an easy way to ensure that your statement
will always be syntactically correct.

David Boerwinkle

-----Original Message-----
From: GH <grasshacker@over-yonder.net>
Cc: PGSQL Novice List <pgsql-novice@postgresql.org>
Date: Wednesday, November 15, 2000 9:39 PM
Subject: Re: [NOVICE] NEWBEE: 'WHERE true' question


>On Wed, Nov 15, 2000 at 09:16:37PM -0700, some SMTP stream spewed forth:
>> Hello,
>>
>*snip*
>> A snippet from this code is as follows:
>>
>> ...
>>   $where = 'true';
>>   if ($form->{number}) {
>>     $callback .= "&number=$form->{number}";
>>     $where .= " AND number ~* '$form->{number}'";
>>   }
>>   if ($form->{description})

>>     $callback .= "&description=$form->{description}";
>>     $where .= " AND description ~* '$form->{description}'";
>>   }
>>
>>   $query = qq|SELECT id, number, description, onhand, unit, sellprice
>>                 FROM parts
>>                 WHERE $where
>>                 ORDER BY $form->{sort}
>>              |;
>> ...
>>
>> Now if the two 'if' conditionals are false, the query becomes:
>>
>> SELECT id, number, description, onhand, unit, sellprice
>>                 FROM parts
>>                 WHERE true
>>                 ORDER BY number
>>
>>
>> What does it mean here for the WHERE to be true?  What is being tested
>> for true?  Is this just a 'place holder', if you will, for the WHERE
>> field in case the two 'if' conditionals are false and so the WHERE field
>> is not blank?
>>
>
>Er, I hope this helps.
>< http://www.postgresql.org/docs/user/sql-select.htm#SQL-WHERE >
>
>It looks like the 'true' is just in there to ensure that all rows are
>returned.
>I can't think of why someone wolud do that..
>
>
>G'luck and
>cheers
>gh
>
>> Thanks,
>> Murrah Boswell
>>
>