SQL Server 2022

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

CREATE TABLE table1
    ([ID] int)
;
    
INSERT INTO table1
    ([ID])
VALUES
    (1),
    (2),
    (3)
;
3 rows affected

CREATE TABLE table2
    ([ID] int, [Name] varchar(1), [Language] varchar(1))
;
    
INSERT INTO table2
    ([ID], [Name], [Language])
VALUES
    (1, 'a', 'E'),
    (1, 'b', 'F'),
    (2, 'c', 'E'),
    (2, 'd', 'F')
;
4 rows affected

SELECT
t1.[ID],t2.[Name], t3.[Language] 
FROM table1 t1
  CROSS JOIN ( SELECT DISTINCT [Language] FROM table2) t3
LEFT JOIN table2 t2 ON t1.[ID] = t2.[ID] AND t2.[Language] = t3.[Language]
ORDEr BY t1.[ID],t2.[Name], t3.[Language]


ID
Name
Language


1
a
E

1
b
F

2
c
E

2
d
F

3
null
E

3
null
F

ID	Name	Language
1	a	E
1	b	F
2	c	E
2	d	F
3	null	E
3	null	F