How to Perform a Minus Operation in MySQL Using NOT IN?

Performing Minus Operations in MySQL

In MySQL, performing minus operations is not directly supported like in Oracle database. However, there are alternative ways to achieve similar results using NOT IN or other techniques.

Understanding the Problem

You have three tables:

• Service_Details: Contains service details.
• Servicing_States: Lists states where a service is offered.
• Exception: Indicates ZIP codes and states where a service is not offered.

You aim to combine these tables and display the result as:

SELECT query_1 - SELECT query_2

Solution Using NOT IN

MySQL provides the NOT IN operator, which can be used to emulate minus operations. The query below leverages NOT IN to achieve the desired result:

SELECT
  service_details.Service_Code
FROM
  service_details
WHERE
  service_details.Service_Code IN (
    SELECT
      servicing_states.Service_Code
    FROM
      servicing_states
    WHERE
      servicing_states.State = 'California'
  )
  AND service_details.Service_Code NOT IN (
    SELECT
      exception.Service_Code
    FROM
      exception
    WHERE
      exception.State = 'California'
  );

This query selects the Service_Code from the Service_Details table that exists in the Servicing_States table for California but is not present in the Exception table for California. This approach mimics the effect of a minus operation.

The above is the detailed content of How to Perform a Minus Operation in MySQL Using NOT IN?. For more information, please follow other related articles on the PHP Chinese website!