MYSQL: MINUS (Set Difference) Operator

Often we need MINUS operator as defined by the SQL definitions i.e you have two sets P and Q and you want P-Q which gives all the elements in P that are not in Q. But unfortunately, MYSQL does not have any operator for such an operation (yeah, I had the same reaction when I heard this!!).

So here is a way how you can implement it in MYSQL. For simplicity let us consider we have two tables P and Q (we can also have views, joins, etc instead of the tables here) which have atleast one column in common (say id, which usually will be the primary key) and we want to evaluate P – Q which removes all those rows from P whose id is present in Q.

Here is the SQL statement for it. The statement is quite self explanatory, but if you still have doubt then leave a comment.

Select * from P left join Q on P.id = Q.id where Q.id is NULL;

Thats is what I could think of. If you are using some better and efficient method, then please leave a comment.

4 thoughts on “MYSQL: MINUS (Set Difference) Operator

    1. Navid

      Yes, it works. I performed operation on single table, where for an entry there are many counts for a field and there are multiple such entries. So I manipulated the difference between the field between two different entries. Thanks for the query.

      Reply
  1. Suchitra Dash Jain

    Using NOT IN will affect the performance. Do we have any other way where we can increase the performance and avail the functionality of MINUS operator?

    Reply

Leave a Reply