SQL: Can I negate a condition in a where clause?

问题:

I want to check if a boolean is true, then decide in the WHERE clause what condition to use.

Say the boolean variable is @checkbool:

SELECT *
FROM TableA A
WHERE
    --if @checkbool is true, run this
    A.Id = 123

    --if @checkbool is false, run this
    A.Id <> 123

Is there a way to negate a condition? Like in C++ you can do if !(condition).

If not, what is the best way to solve this problem?

Thank you!

回答1:

SQL's equivalent of ! in C is NOT. However, in your case you want something else: you need to build a condition that decides between the two choices based on the value of @checkbool, like this:

SELECT *
FROM TableA A
WHERE (    (@checkbool) AND (A.Id =  123))
   OR ((NOT @checkbool) AND (A.Id <> 123))

回答2:

Here is one solution:

IF @Checkbool = 1
     SELECT * FROM Table A WHERE A.Id = 123
ELSE
     SELECT * FROM Table A WHERE A.Id <> 123

Here is another using just the WHERE Clause:

SELECT * 
FROM Table A 
WHERE
     (@Checkbool = 1 AND A.Id = 123)
     OR
     (@Checkbool = 0 AND A.Id <> 123)

Everything you put in the where clause needs to be in the form of an expression. Thus, the solution in this case is to write the condition in the form of an expression.

Hope this helps. :)

回答3:

select *
from TableA A
where
    (@checkbool = 1 and A.Id = 123) or
    (@checkbool = 0 and A.Id <> 123)

回答4:

If checkbool is a coumn, then something like this will do.(Not in proper SQL syntax)

WHERE (A.ID=123 AND A.checkbool=TRUE) OR (A.ID!=123 AND A.checkbool=TRUE)

If checkbool is not a cloumn, replace A.checkbool with value of checkbool.

here is the correct SQL

WHERE ((checkbool) AND (A.Id =  123))OR ((NOT checkbool) AND (A.Id <> 123))

回答5:

You can use IN clausule or even the != operator, like:

A.Id NOT IN (123,x,y,z);

or

A.Id != 123;