With SQL, what is the ranking of efficiency for each of the types of join

JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN?

I'm guessing the size of the datasets on each side of the join may make LEFT vs RIGHT a hard call, but how do the others compare.

Also am I correct in assumin开发者_JAVA百科g JOIN & INNER JOIN are one and the same? If not, how does this fit into the order/ranking.

Yes, JOIN and INNER JOIN are the same. In general the ranking is JOIN is fastest, followed closely by LEFT JOIN which is equivalent to RIGHT JOIN, and then followed very far in the distance by FULL JOIN.

But this ranking is so variable that it can be largely ignored. Your actual performance is highly dependent upon the size of the datasets, availability of proper indexes, and exact query plan chosen. One LEFT JOIN may be fast and the next INNER JOIN might be glacially slow.

That notwithstanding, I would advise avoiding FULL JOIN unless you absolutely need it. (At least in Oracle, which is where I've had bad experiences with it.)

INNER is an optional word when INNER JOIN is desired => so they are one and the same. This is the same as the word OUTER being optional in LEFT/RIGHT/FULL OUTER JOIN

In terms of efficiency, it completely depends on what else is happening. If it is a LEFT JOIN with a IS NOT NULL test on the right side (anti-semi join) then it is very efficient and works like an EXISTS clause.

Absent other factors, and considering only

SELECT .. FROM A X-JOIN B ON <condition>

1. If results need to be preserved from A, B or Both, then efficiency is not a factor. You need a LEFT/RIGHT/FULL join because it provides the correct results

2. If you need results that match on both sides, and not all data is available from either side, then same as the above, you need an INNER JOIN.

3. Only if the join is bound to find rows on both sides, then LEFT/RIGHT/FULL join becomes an option. In most cases, the INNER JOIN will be faster because it gives the optimizer the option to start from the smaller table (or better indexed) and hash match to the larger table.

_{"in most cases" in Point #3 because different RDBMS may optimize queries differently.}

Ranking them for efficiency would be pointless, as they return different results. If you need a left join, an inner join won't do the job.

Efficiency in a join has more to with the size of the tables, the indexing, and how the rest of the query is written than whether it is an INNER, OUTER, CROSS or FUll JOIN. A CROSS JOIN on two small tables might be fast but a INNER join on two large tables with a WHERE clause that is not sargable would not be.