Converting Oracle SQL join query into MySQL query

Question

Given the following join in Oracle Pl/SQL:

select a.field1, b.field1, b.field2
from table_a a, table_b b
where a.field2 = b.field3;

When executed this code in MySQL, it takes a really long time to do the task while in Pl/SQL it takes no time. The tables are the same in the two environments. No fields are indexed.

Is there a difference between the joins in the two dialects? What would be the correct translation of this in standard SQL?

it's not truly JOIN, this approach is more slower as JOIN you can use select a.field1, b.field1, b.field2 from table_a a inner join table_b b where a.field2 = b.field3 — Simon Dorociak
– Simon Dorociak, Commented Jun 3, 2012 at 18:42
So you mean that the 'join...on' would give the same result? — user1433877
– user1433877, Commented Jun 3, 2012 at 18:51

usr · Accepted Answer · 2012-06-03 18:51:39Z

1

MySQL does not cope with having no indexes. Oracle can do a hash join and still be fast. Add indexes on the join columns.

answered Jun 3, 2012 at 18:51

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FSP · Accepted Answer · 2012-06-03 18:55:25Z

0

You need indexes in MySql to avoid the problem that you are facing.

answered Jun 3, 2012 at 18:55

FSP

4,8572 gold badges21 silver badges19 bronze badges

Collectives™ on Stack Overflow

Converting Oracle SQL join query into MySQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related