Re: converting join in Oracle to mysql

[Roland Bouman]

This join syntax should work fine in MySQL. Have you tried? Any errors?


that said, this is actually the nicer way to define joins, and even oracle supports it as of Oracle 9 if I am not mistaken.

(First of all, general remark: try to write the tables and corresponding conditions in the same order. After the joins, add any normal conditions, such as your TRUNC thingie)

Consider this join:

FROM securities s
, bonds_historic b
, cq_bond bond
, cq_vw_cds_bond_map MAP
, cq_cds cds
WHERE s.security_id = b.security_id
AND b.security_id = bond.security_id_live
AND bond.bond_id = MAP.bond_id
AND MAP.cds_id = cds.cds_id

this is called a theta join - join conditions are just ordinary "where" conditions

Now see this:

FROM securities s
JOIN bonds_historic b
ON s.security_id = b.security_id
JOIN cq_bond bond
ON b.security_id = bond.security_id_live
JOIN cq_vw_cds_bond_map MAP
ON bond.bond_id = MAP.bond_id
JOIN cq_cds cds
ON MAP.cds_id = cds.cds_id

Much nicer, don't you think?