MYSQL NOT IN and NOT EXISTS Comparison Operators


MYSQL NOT IN and NOT EXISTS Comparison Operators

With mySQL, the NOT IN() and NOT EXISTS operators are do the opposite of their counterparts IN() EXISTS() operators. When managing databases, there may come a time when you need to build queries that will check the data in one database table and see if there is a matching value in another column of a different database table.

One example that comes to mind is to find the people that made a purchase for an item. This data could have ended up in a table like purchases_before_paypal. Then, upon purchase, you could record the user ids of the people who actually made a payment with a Paypal account.

So, if you wanted to do a check to see who did not actually pay you could use the NOT IN() or NOT EXISTS() operators to see those who made it the end of the cart without having made the Paypal transaction.

Another example is to have a table that is used to collect data from registered users and another table to check logins. This way, you can calculate who has registered, but has not logged in.

MYSQL NOT IN()

The statement below will grab rows where the id from the tablename1 table where the id from the tablename1 column is not in the userid column from the table called tablename2 table.

 SELECT id FROM tablename1 WHERE tablename1.id NOT IN (SELECT userid FROM tablename2); 

MYSQL NOT EXISTS()

The statement below will grab rows where the id from the tablename1 table where the id from the tablename1 column does not exist not in the userid column from the table called tablename2 table.

 SELECT DISTINCT id FROM tablename1 WHERE NOT EXISTS (SELECT userid FROM tablename2 WHERE tablename2.userid = tablename1.id);