MINUS in MySQL

In this tutorial, we are going to see the MINUS operator and show you how to simulate the MySQL MINUS operator.

You should know that MySQL does not support the MINUS operator. This tutorial explains how to simulate the MINUS operator in MySQL using joins.
 

 

MINUS operator

MINUS operator is one of the operators defined in the SQL standard, namely UNION, INTERSECT and MINUS.
MINUS compares the results of two queries and returns separate rows (the first query that do not appear in the result of the second query).
 

 

Example:

In the example below, we have two tables, called “Clients” and “Orders”, which contain the following data:
 

 

 
The following query uses Join to simulate the MINUS operator, and returns ‘ClientID’ values in ‘Clients’ table but not in ‘Orders’ table.

SELECT ClientID 
FROM Clients 
LEFT JOIN Orders USING(ClientID) 
WHERE Orders.ClientID IS NULL;

Output:

+----------------+
|    ClientID    |
+----------------+
|            4   |
|            5   |
+----------------+

 
The following query returns the ‘ClientID’ values in “Orders” table but not in “Clients” table.

SELECT ClientID 
FROM Orders
LEFT JOIN Clients USING(ClientID) 
WHERE Clients.ClientID IS NULL;

Output:

+----------------+
|    ClientID    |
+----------------+
|            8   |
+----------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More