MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tag (tag_id int)
;
insert into tag values (1), (2)
create table tag_l11n (tag_l11n_tag int, 
tag_l11n_language varchar(2),
tag_l11n_title varchar(20))
;
insert into tag_l11n values 
(1, 'it', 'Buongiorno'),
(1, 'en', 'Good Morning'),
(2, 'en', 'Goodbye')
SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    `tag_l11n_2`.`tag_l11n_title` as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_2 ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
WHERE 
    `tag_l11n_2`.`tag_l11n_language` = 'it' 
   OR
    `tag_l11n_2`.`tag_l11n_language` = 'en'
    AND NOT EXISTS (SELECT *
                    FROM tag_l11n t3 
                    WHERE t3.tag_l11n_tag = tag_3.tag_id 
                      AND t3.tag_l11n_language = 'it')ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;


tag_id_3
tag_l11n_title_2


1
Buongiorno

2
Goodbye


SELECT 
    `tag_3`.`tag_id` as tag_id_3,
    COALESCE(`tag_l11n_2`.`tag_l11n_title`, `tag_l11n_3`.`tag_l11n_title`) as tag_l11n_title_2
FROM 
    `tag` as tag_3 
LEFT JOIN 
    `tag_l11n` as tag_l11n_2 ON `tag_3`.`tag_id` = `tag_l11n_2`.`tag_l11n_tag` 
                            AND `tag_l11n_2`.`tag_l11n_language` = 'it' 
LEFT JOIN 
    `tag_l11n` as tag_l11n_3 ON `tag_3`.`tag_id` = `tag_l11n_3`.`tag_l11n_tag` 
                            AND `tag_l11n_3`.`tag_l11n_language` = 'en' 
ORDER BY 
    `tag_3`.`tag_id` ASC 
LIMIT 25;


tag_id_3
tag_l11n_title_2


1
Buongiorno

2
Goodbye