CODEWITHSUNDEEP
mysqlsqloracle-databasejoinleft-join
Vinayak Gupta
Vinayak Gupta

Reputation: 13

Oracle to MySQL Query Conversion

I was working on Oracle to MySQL query conversion when I encountered the following snippet that I'm completely unable to understand:

select *
from a, b 
where a.liab_id = b.liability_no(+) 
and NVL (a.cust_id, b.customer_no(+)) = b.customer_no(+);

Table a columns: cust_id, liab_id, details

Table b columns: customer_no, liability_no,range

I'd be really grateful if someone can explain the query or convert it to the respective MySQL query.

Upvotes: 1

Views: 194

Answers (2)

MT0
MT0

Reputation: 167972

To convert the legacy Oracle comma join to the ANSI join syntax, you want:

SELECT *
FROM   a
       LEFT OUTER JOIN b
       ON (   a.liab_id = b.liability_no
          AND COALESCE( a.cust_id, b.customer_no ) = b.customer_no
          )

or

SELECT *
FROM   a
       LEFT OUTER JOIN b
       ON (   a.liab_id = b.liability_no
          AND ( a.cust_id = b.customer_no OR a.cust_id IS NULL )
          )

Oracle 18c db<>fiddle here

MySQL 8 db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

In both Oracle and MySQL, you should use explicit JOIN syntax. That would be:

select *
from a left join
     b 
    on a.liab_id = b.liability_no and
       a.cust_id = b.customer_no;

Upvotes: 0

Related Questions