MySQL NOT IN

MySQL NOT IN

In this section we will read about how to use the NOT IN function in MySQL with a simple example.

MySQL NOT IN() function specifies that the list doesn't contain the any value that present in the arguments of this function i.e. the resultant list will not include the row which parameter has been passed to NOT IN() parameter. We will illustrate the NOT IN() functionality using an example.

Example

Here I am giving a simple example which will demonstrate you about how to use MySQL not in() function. In this example we will create a database table in MySQL using sql create query named purchase then we will insert some bulk record using the sql insert query. Then we will use the not in() function with sql select query and display those result excluding the value which parameter is passed into the not in() function.

We have created a table using the following sql :

CREATE TABLE `purchase` (                  
            `purchaseId` int(20) NOT NULL,           
            `purchaseAmt` int(15) DEFAULT NULL,      
            `productId` int(15) DEFAULT NULL,        
            `id` int(15) DEFAULT NULL,               
            `Username` varchar(20) DEFAULT NULL,     
            `productName` varchar(20) DEFAULT NULL,  
            PRIMARY KEY (`purchaseId`)               
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then inserted some value to the table using the following SQL :

insert into `purchase`(`purchaseId`,`purchaseAmt`,`productId`,`id`,`Username`,`productName`) values ( '1001','5000','101','7','deepak','clothes')
insert into `purchase`(`purchaseId`,`purchaseAmt`,`productId`,`id`,`Username`,`productName`) values ( '1002','6000','102','8','ankit','games')
insert into `purchase`(`purchaseId`,`purchaseAmt`,`productId`,`id`,`Username`,`productName`) values ( '1003','7000','103','9','asdf','books')

After executing the insert query then the table will be as follows :

When you will execute the following code that contains the NOT IN() function as follows :

SELECT * FROM purchase WHERE purchaseAmt NOT IN (5000);

Then the output will be as follows :