Equivalent for (X IS NULL OR X IS NOT NULL)

[drew1234567890]

Hi All,
I'm hoping that there is an SQL keyword (e.g. &quot;<KEYWORD>&quot such that the following statements are all equivalent:

    SELECT * FROM Table
    SELECT * FROM Table WHERE (Field IS NULL OR Field IS NOT NULL)
    SELECT * FROM Table WHERE Field = <KEYWORD>
    SELECT * FROM Table WHERE Field IN (<KEYWORD>)

Anyone have any ideas on this?
TIA,
Drew

 

[swampBoogie]

Your hopes are in vain.

 

[carp]

The first two are already equivilent.

You can make the third equivilent as follows:
SELECT * FROM Table WHERE Field = Field; (I know, that's not a keyword). Of course, if you have NULL values in field, this will not work.

The fourth one can be done as
SELECT * FROM Table WHERE Field IN (SELECT field FROM table); (STILL not a keyword, and still won't work if a NULL slips into Field).

Out of curiosity, since these all equate to
SELECT * FROM Table;
why not just use
SELECT * FROM Table; ?

 

[drew1234567890]

Hi Carp,
Thanks for your response. Very helpful! I should have thought of those options.
The answer to your question is that my app needs to be able to replace a parameter with a value, and that value could include not caring what is in the field. So, in the following

    SELECT * FROM Table WHERE Field = @P1

I need to be able to replace the @P1 with a string value that the user provides. If the user types &quot;Blah&quot;, then I need the query to say

    SELECT * FROM Table WHERE Field = "Blah"

However, if the user does not provide a value, what I really need is for the query to be

    SELECT * FROM Table

with no where clause. But this is a pain to implement with a simple search/replace function. It becomes even more of a pain when you consider the other form:

    SELECT * FROM Table WHERE Field IN (@P1)

In this case, @P1 could be '1' or '1,2,3' or nothing at all.

But with the info you provided, I can do this:

CSharp Code:

string command = "SELECT * FROM Table WHERE Field = @P1"
// User enters "Blah"
command.Replace("@P1", "Blah") 
// User enters nothing at all
command.Replace("@P1", "Field OR Field IS NULL")

For the IN keyword, it's a little messier, but still workable:

CSharp Code:

string command = "SELECT * FROM Table WHERE Field IN @P1"
// User enters "Blah"
command.Replace("@P1", "Blah") 
// User enters nothing at all
command.Replace("@P1", "(SELECT Field FROM Table) OR Field IS NULL")

So this will work fine. Thanks again!
Drew