Monday, August 12, 2013

Some good SQL tips

Two very nice articles on SQL tips for Java developers, or anyone who uses SQL, really: 

10 Common Mistakes Java Developers Make When Writing SQL

10 More Common Mistakes Java Developers Make When Writing SQL 

In Part 2, one tip is always to use ANSI joins. I tend to agree, but I just want to note that I have run into situations where the "old style" was required by Oracle. Maybe someone can correct me if this doesn't apply anymore, but back about 7 years ago when I really dug into materialized views, I found that query rewrite required the older syntax. Always thought that was a bit odd.


Lukas Eder said...

It's true that Oracle's (+) symbol is slightly more powerful than ANSI's OUTER JOIN syntaxes. This is specifically true for composite foreign keys, when you write something like:

T1.A = T2.A AND
T1.B = (+) T2.B

i.e. when you don't "fully outer join" on all key columns. For whatever reason you'd do that... :-)

MWrynn said...

Thanks for your comment, Lukas!