JOIN only if column is NULL

Question

I've two tables as follow:

tag table (only tags in english):

ID    title
-------------
1     tag_1
2     tag_2
3     tag_3

tag_translation table:

ID    title      locale   tag_id (foreign key)
-----------------------------------------------
1     tag_1_fr   FR        1
2     tag_1_de   DE        1
3     tag_2_es   ES        3

How to do a SQL query returning all tags in french, and if no tag found in french, fallback to english?

Example of result (select all tags in french, fallback to english):

ID    title
---------------
1     tag_1_fr
2     tag_2
3     tag_3

Answer 1

SELECT T.ID
       ,COALESCE(TT.TITLE, T.TITLE) AS TITLE
FROM tag T
LEFT JOIN tag_translation TT
    ON T.ID = TT.tag_id
    AND TT.locale = 'FR';

This assumes that tag_id and locale are unique in tag_translation.