Fallback value in a JOIN condition if no row is found

Question

Foreword: I am afraid that I'm asking to do something that can't be achieved. But I know that, if there is a way to do it, here someone will know :)

Suppose I have a MySQL database (or a MariaDB one, if compatibility can be retained). I need to select from TableA and join TableB based on a certain column having a certain value X; it is possible that no suitable row exists in TableB, which means I shall use a LEFT OUTER JOIN. However, in case no row is found (no row of TableB has the value X in that column), then I wish to use a row from TableB with value Y. In other words, Y should act as a fallback condition for the JOIN.

Here is my concrete example. Suppose I handle data localization by splitting an entity (say, a product) between two tables, one with the localizable data and the other with the fixed data. When I want to get the product info in French, I wish to fallback to English if no information is available in French. However, if I do

// basic stub of the query for demonstration purposes
SELECT * FROM Products p
LEFT OUTER JOIN ProductsLocalization pLoc
      ON pLoc.ProductID = p.ID AND pLoc.Culture = 'fr-FR'

I will get NULL when no French info is available. Instead, only for those rows, I wish to have the result of the query

// basic stub of the query for demonstration purposes
SELECT * FROM Products p
LEFT OUTER JOIN ProductsLocalization pLoc
      ON pLoc.ProductID = p.ID AND pLoc.Culture = 'en-US'

But the latter query is not acceptable, of course.

I know I could use an OR to join based on the Culture being 'fr-FR' or 'en-US', but this would potentially double the number of selected rows, and it would require me to post-process the resulting dataset. I wish to know if there is a way to avoid this post-processing.

Non-duplicate disclaimer: my question is similar to many others, but there's a crucial difference. I need to use a fallback value for the JOIN condition itself, not as the result of the query. My default doesn't end up in the dataset, but the dataset is computed based on that default.

Answer 1

You can join twice and use COALESCE() to get the value you want:

SELECT p.product_desc, 
    COALESCE(pLoc_fr.value, pLoc_en.value) 
FROM Products p
    LEFT OUTER JOIN ProductsLocalization pLoc_us ON 
       pLoc_us.ProductID = p.ID 
       AND pLoc_us.Culture = 'en-US'
    LEFT OUTER JOIN ProductsLocalization pLoc_fr ON 
       pLoc_fr.ProductID = p.ID 
       AND pLoc_fr.Culture = 'fr-FR';

Also, if you can imagine a way of combining data sets, you can probably achieve using plain-ol SQL. Some edge cases require dynamically generating a SQL statement, but those aren't terribly common. In this case, you could also use two correlated subqueries inside of a COALESCE or a CASE or an IF statement inside the SELECT. There's almost always a few ways to skin the cat.