MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tags ( id INT, value VARCHAR(8) )
SELECT 1 id, 'Hello' value UNION ALL
SELECT 2,    'World'       UNION ALL
SELECT 3,    'Dog';
SELECT * FROM tags;


id
value


1
Hello

2
World

3
Dog


CREATE TABLE languages ( id INT, name VARCHAR(16) )
SELECT 1 id, 'English' name UNION ALL
SELECT 2,    'Portuguese'   UNION ALL
SELECT 3,    'French';
SELECT * FROM languages;


id
name


1
English

2
Portuguese

3
French


CREATE TABLE translations( id INT, tag INT, language INT, translation VARCHAR(8) );
INSERT INTO translations VALUES
(1 ,   1  ,   1     ,     "hello"),
(2 ,   1  ,   2     ,     "olá"),
(3 ,   1  ,   3     ,     "bonjour"),
(4 ,   2  ,   1     ,     "world"),
(5 ,   3  ,   1     ,     "dog");
SELECT * FROM translations;


id
tag
language
translation


1
1
1
hello

2
1
2
olá

3
1
3
bonjour

4
2
1
world

5
3
1
dog


SELECT *
FROM tags
CROSS JOIN languages
LEFT JOIN translations ON translations.tag = tags.id
                      AND translations.language = languages.id
ORDER BY tags.id, languages.id;


id
value
id
name
id
tag
language
translation


1
Hello
1
English
1
1
1
hello

1
Hello
2
Portuguese
2
1
2
olá

1
Hello
3
French
3
1
3
bonjour

2
World
1
English
4
2
1
world

2
World
2
Portuguese
null
null
null
null

2
World
3
French
null
null
null
null

3
Dog
1
English
5
3
1
dog

3
Dog
2
Portuguese
null
null
null
null

3
Dog
3
French
null
null
null
null

id	value	id	name	id	tag	language	translation
1	Hello	1	English	1	1	1	hello
1	Hello	2	Portuguese	2	1	2	olá
1	Hello	3	French	3	1	3	bonjour
2	World	1	English	4	2	1	world
2	World	2	Portuguese	null	null	null	null
2	World	3	French	null	null	null	null
3	Dog	1	English	5	3	1	dog
3	Dog	2	Portuguese	null	null	null	null
3	Dog	3	French	null	null	null	null