[mysql] SQL WHERE condition is not equal to?

Is it possible to negate a where clause?

e.g.

DELETE * FROM table WHERE id != 2;

This question is related to mysql sql where-clause negate

The answer is


Best solution is to use

DELETE FROM table WHERE id NOT IN ( 2 )

WHERE id <> 2 should work fine...Is that what you are after?


delete from table where id <> 2



edit: to correct syntax for MySQL


I was just solving this problem. If you use <> or is not in on a variable, that is null, it will result in false. So instead of <> 1, you must check it like this:

 AND (isdelete is NULL or isdelete = 0)

Your question was already answered by the other posters, I'd just like to point out that

 delete from table where id <> 2

(or variants thereof, not id = 2 etc) will not delete rows where id is NULL.

If you also want to delete rows with id = NULL:

delete from table where id <> 2 or id is NULL

Yes. If memory serves me, that should work. Our you could use:

DELETE FROM table WHERE id <> 2

Look back to formal logic and algebra. An expression like

A & B & (D | E)

may be negated in a couple of ways:

  • The obvious way:

    !( A & B & ( D | E ) )
    
  • The above can also be restated, you just need to remember some properties of logical expressions:

    • !( A & B ) is the equivalent of (!A | !B).
    • !( A | B ) is the equivalent of (!A & !B).
    • !( !A ) is the equivalent of (A).

    Distribute the NOT (!) across the entire expression to which it applies, inverting operators and eliminating double negatives as you go along:

        !A | !B | ( !D & !E )
    

So, in general, any where clause may be negated according to the above rules. The negation of this

select *
from foo
where      test-1
  and      test-2
  and (    test-3
        OR test-4
      )

is

select *
from foo
where NOT(          test-1
           and      test-2
           and (    test-3
                 OR test-4
               )
         )

or

select *
from foo
where        not test-1
  OR         not test-2
  OR   (     not test-3
         and not test-4
       )

Which is better? That's a very context-sensitive question. Only you can decide that.

Be aware, though, that the use of NOT can affect what the optimizer can or can't do. You might get a less than optimal query plan.


You could do the following:

DELETE * FROM table WHERE NOT(id = 2);

Use <> to negate the where clause.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to where-clause

Laravel where on relationship object MySQL Select last 7 days SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel Conditional WHERE clause in SQL Server Conditional WHERE clause with CASE statement in Oracle How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? MySQL Multiple Where Clause How to use If Statement in Where Clause in SQL? MySQL direct INSERT INTO with WHERE clause MySql Inner Join with WHERE clause

Examples related to negate

Negate if condition in bash script How to negate a method reference predicate python how to "negate" value : if true return false, if false return true SQL WHERE condition is not equal to? How do I negate a test with regular expressions in a bash script?