MySQL replace minus with left join

Question

I am working on migrating from Oracle to MySQL and currently stuck on converting query with MINUS operation. I know, I have to use left join, but somehow my query is not giving the exact rows as it is in Oracle. The query is very simple with with select on views "V_*".

On Oracle:

select s.section, c.title from course c, v_staff_active s
  minus
select section,title from v_rep_attended_by_section

On Mysql, I converted it like below:

select * from
( select s.section, c.title from course c, v_staff_active s) x
left join
( select section,title from v_rep_attended_by_section) y on x.section = y.section
where y.section is null;

But not getting the exact result or number of records. Can you please help me out as I am very much new to MySQL.

Thanks in advance.

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 20 '18 at 13:53
Minus doesn't work that way, and you'd have to join on _all_ columns in the two queries to do something. Also, your first query appears to have a cross join in it, which may not be what you want. I agree with the @strawberry comment given above. — Tim Biegeleisen, Aug 20 '18 at 13:55
Thank you Tim and Strawberry, As I said I am not too good in mysql and I changed my query to mysql using below example in link https://stackoverflow.com/questions/21009748/how-to-replace-a-complex-sql-minus-query-with-left-outer-join-equivalent — VikiT, Aug 20 '18 at 13:58
@vikit DISTINCT is not a function, so I'd be wary of taking too much from that answer — Strawberry, Aug 20 '18 at 14:01
@VikiT Use proper `JOIN` It's been around for over 20 years. — Eric, Aug 20 '18 at 19:14

score 1 · Accepted Answer · answered Aug 20 '18 at 18:48

1

Your query should look like:

select s.section, c.title 
  from course c
  left join v_staff_active s on c.section = s.section
                            and c.title = s.title
  where s.section is null and s.title is null;

answered Aug 20 '18 at 18:48

The Impaler

45,731
9
39
76

MySQL replace minus with left join

1 Answers1