What else are the differences in writing queries

Question

Whats the difference between Query A and Query B. Both table_a and table_b are having 700k+ records. The obvious difference I can see is the speed (performance). Additionally, our Oracle consultants tend to write sql script using Query B.

Query A 
select * 
from table_a a 
inner join table_b b 
on a.id = b.id

Query B
select * 
from table_a a,table_b b 
where a.id = b.id

The difference is that the first is the modern, robust way of writing joins. The second is the old, outdated and fragile way of writing joins — , Sep 01 '16 at 09:25
...and I wouldn't expect a performance difference between the two since any reasonably powerful SQL-engine should produce them same execution-plan for both. — piet.t, Sep 01 '16 at 09:27
You might see a performance difference if you run each query once - data block caching could make the second one you run appear faster as it can get everything from memory and doesn't have to hit the disk again. If you run both queries multiple times the subsequent runs should be the same. Also look at the execution plans to see what they are both doing - it should be the same. — Alex Poole, Sep 01 '16 at 10:21
*The obvious difference I can see is the speed (performance)*: What gave you that (incorrect) impression? — sstan, Sep 01 '16 at 13:56

score 0 · Accepted Answer · edited Jul 29 '20 at 18:25

0

The second query is using more of a relational model(it is not recommended anymore.) concept whereas the first one is ANSI compliant and more readable.

Although this article is in SQL Server by Aaron but it gives a useful insight about the old practice: Bad habits to kick : using old-style JOINs

edited Jul 29 '20 at 18:25

Community

1
1

answered Sep 01 '16 at 09:25

Rahul Tripathi

168,305
31
280
331

The second is also ANSI compliant (albeit a very old ANSI standard) – Sep 01 '16 at 09:26

What else are the differences in writing queries

1 Answers1