So I've been a programmer for awhile now. I never worked for a huge development firm, but I've always been the best SQL developer in every job I had lol. Doesn't mean I know everything.
While working on one of our modules, I stumbled on a query that does a "INNER MERGE JOIN". I've never seen that before. At first I thought it was a little bit like the "LEFT OUTER JOIN" vs "LEFT JOIN", which are the same thing. The "LEFT JOIN" is an OUTER JOIN, the "OUTER" part being simply implied.
I tried removing the MERGE from the INNER MERGE JOIN, and it gave me the same results, but sorted differently(there is no ORDER BY on the main query), which tells me there is definitely something that's being done differently.
So I read a little bit and found the difference between INNER NESTED LOOP JOIN, INNER MERGE JOIN and INNER HASH JOIN. I'm starting to get part of it, even tho it's still a bit nebulous to me. What would a standard INNER JOIN do? When should I do what? If I understand right, the NESTED LOOP should be used if one of the two tables or queries joined contains VERY few rows (< 10). The Merge join should be used on mid-size tables/queries that have similar number of records and the HASH JOIN should be used for two big tables having a very different number of records.
Is the "INNER JOIN" simply a short for one of those 3 types of joins just like the LEFT JOIN being a short for a LEFT OUTER JOIN? if so, which one is it? If not, shouldn't we avoid normal "INNER JOIN"s and always try to use the most appropriate type of join depending on the situations?
Thanks a lot, I'm always trying to improve, but sadly, we do not use SQL that much here, even tho my boss tries to give me most of the SQL stuff we have to do, everybody else can't wrap their head around for some reasons, and usually end up asking me for help, but I'm still getting a bit rusty and having some trouble getting the difference between those!