As a software developer, you must have the experience to deal with all sorts of join queries. And I believe everyone has once or still now struggle with all these different terms about inner, outer, left, right, and etc. As I have spent roughly two hours these morning, to summarize some of the points that I am easily to forgotten and got wrong, it's a good chance to write it done in case later I need to test my memory.
1) What is the difference of join...on... grammar and little (+) sign?
Oracle used to only support (+) in old days, which is also kind of created by Oracle. Later, as the ANSI formalized the standard for join grammar, Oracle adopted both for join queries. As a result, it won't be a surprise if you see both two styles in your project which comes from different developers.
2) Any difference between inner join and join?
No.
3) Why do we need outer join?
Generally, inner join will return result sets that have a match in both Table A and B. However in reality sometimes, we also need results to be returned even the matching is null. That's the place where outer join shows its ability. There are plenty of tutorials available to discuss about the outer join. What I want to mentioning here is the null value will only applied to the join's destination table, but not the starting table.
4) Any more differences?
Yes. Actually there are two more I want to emphasize:
1. ANSI style supports outer join, which you can google what does it mean. But traditional Oracle doesn't directly support it. ( By saying it directly, I mean there always exists workarounds, and various workarounds)
2. One of the most important feature to differentiate the two is the ANSI style separate the join condition and query filter criteria, which is much more nice tidy and clean. (Is it, at least I think so). Meanwhile, you will also be able to avoid a lot of caveats that may come with you when you go for the traditional style. See Common errors seen when using OUTER-JOIN.
No comments:
Post a Comment