EXPLAIN EXTENDED

From Stack Overflow:

I have table item and another table language which contains names for the items in different languages:

item	language	data

How do I select a French name for an item if it exists, or a fallback English name if there is no French one?

This is quite a common database design (most often used for localization), so I'd like to cover this question for different databases.

This design is not limited to languages and localization: it may be used for any data with default value that can be overriden. Prices, discounts, options — many kinds of things.

Today we will see how do it in Oracle.

We basically have three options here:

1. Use COALESCE on two SELECT list subqueries
2. Use COALESCE on the results of two LEFT JOINS
3. Use the combination of methods above: a LEFT JOIN for French names and a subquery for English ones

Let's create sample tables and see what is more efficient.
Read the rest of this entry »