Is there an operator in MySQL to implement multiple NOT conditions like WHERE id != 5 AND id != 10 AND id != 15?

MySQLMySQLi Database

Yes, for this MySQL comes with a NOT IN.

The syntax is as follows

SELECT *FROM yourTableName WHERE yourColumnName NOT IN(1,2,7);

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table User_informations
   - > (
   - > UserId int,
   - > UserName varchar(20)
   - > );
Query OK, 0 rows affected (0.47 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into User_informations values(12,'Maxwell');
Query OK, 1 row affected (0.17 sec)
mysql> insert into User_informations values(7,'David');
Query OK, 1 row affected (0.10 sec)
mysql> insert into User_informations values(1,'Ramit');
Query OK, 1 row affected (0.36 sec)
mysql> insert into User_informations values(10,'Bob');
Query OK, 1 row affected (0.19 sec)
mysql> insert into User_informations values(2,'Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into User_informations values(14,'Sam');
Query OK, 1 row affected (0.23 sec)
mysql> insert into User_informations values(6,'Mike');
Query OK, 1 row affected (0.12 sec)
mysql> insert into User_informations values(4,'Robert');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from User_informations;

The following is the output

+--------+----------+
| UserId | UserName |
+--------+----------+
|     12 | Maxwell  |
|      7 | David    |
|      1 | Ramit    |
|     10 | Bob      |
|      2 | Carol    |
|     14 | Sam      |
|      6 | Mike     |
|      4 | Robert   |
+--------+----------+
8 rows in set (0.00 sec)

The following is the implementation of what you asked using NOT IN().

The query is as follows

mysql> select *from User_informations where UserId NOT IN(1,2,7);

The following is the output

+--------+----------+
| UserId | UserName |
+--------+----------+
|     12 | Maxwell  |
|     10 | Bob      |
|     14 | Sam      |
|      6 | Mike     |
|      4 | Robert   |
+--------+----------+
5 rows in set (0.00 sec)
raja
Published on 19-Mar-2019 11:23:34
Advertisements