Trying to perform MINUS operation in MySQL

Question

I am trying to perform a MINUS operation in MySql.I have three tables:

1. one with service details
2. one table with states that a service is offered in
3. another table (based on zipcode and state) shows where this service is not offered.

I am able to get the output for those two select queries separately. But I need a combined statement that gives the output as 'SELECT query_1 - SELECT query_2'.

Service_Details Table Service_Code(PK) Service Name

Servicing_States Table Service_Code(FK) State Country PK(Service_Code,State,Country)

Exception Table Service_Code(FK) Zipcode State PK(Service_Code,Zipcode,State)

Answer 1

MySql does not recognise MINUS and INTERSECT, these are Oracle based operations. In MySql a user can use NOT IN as MINUS (other solutions are also there, but I liked it lot). Example:

select a.id 
from table1 as a 
where <condition> 
AND a.id NOT IN (select b.id 
                 from table2 as b 
                 where <condition>);

Answer 2

MySQL Does not supports MINUS or EXCEPT,You can use NOT EXISTS, NULL or NOT IN.

Answer 3

Here's my two cents... a complex query just made it work, originally expressed with Minus and translated for MySql

With MINUS:

select distinct oi.`productOfferingId`,f.name 
from t_m_prod_action_oitem_fld f
     join t_m_prod_action_oitem oi 
    on f.fld2prod_action_oitem = oi.oid;
minus
select
  distinct r.name,f.name
from t_m_prod_action_oitem_fld f
     join t_m_prod_action_oitem oi 
    on f.fld2prod_action_oitem = oi.oid
     join t_m_rfs r 
    on r.name = oi.productOfferingId
     join t_m_attr a 
    on a.attr2rfs = r.oid and f.name = a.name;

With NOT EXISTS

select distinct oi.`productOfferingId`,f.name 
from t_m_prod_action_oitem_fld f
     join t_m_prod_action_oitem oi 
    on f.fld2prod_action_oitem = oi.oid
where not exists (
select
  r.name,f.name
from t_m_rfs r 
     join t_m_attr a 
    on a.attr2rfs = r.oid   
where r.name = oi.productOfferingId and f.name = a.name