MySql NOT IN() Function

The previous lesson of MySql Lessons Series was about MySql Having Clause and in this lesson we will learn to use MySql NOT IN() Function.



Let us say we have a table purchase given in the image below:



The above purchase table contains the titles, codes and quantities of books. To select all the books from purchase table, except those having quantities 34 and 45, we can use MySql NOT IN Function. An example query of MySql NOT IN Function is given below:

SELECT * FROM `purchase` WHERE `quantity` NOT IN (34,45)

The above query will show all records from purchase table except those having quantities 34 and 45 (i.e Chemistry and Physics Book).

Similarly we can apply the MySql NOT IN Function on the code column like this:

SELECT * FROM `purchase` WHERE `code` NOT IN ('acd007','afg678')

Now let us consider another situation where we have two tables purchase and sale. Purchase table is the one given above and sale table is shown in the image below:



Now let us assume we want to show all the data from purchase table which is not present in sales table i.e we want to show all the purchased books that are not sold yet. MySql query for this scenario is given below:

SELECT * FROM `purchase` WHERE `code` NOT IN (SELECT `code` FROM `sale`)

The above query will show all the books from purchase table that are not present in sale table i.e Physics Book and Biology Book. In the above example we have used a sub query inside our main query to fetch records from two different tables and compare the records fetched.

Stay tuned for more lessons...