SQLite 3.27

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

CREATE TABLE t1 (id INT, name CHAR(5), lang CHAR(2), city CHAR(3), date DATE);
INSERT INTO t1 VALUES
( 1, 'vasya', 'ru', 'msk', '1970-01-01'),
( 2, 'petya', 'ru', 'spb', '1970-01-01');
CREATE TABLE t2 (id INT, name_test CHAR(5), email VARCHAR(16), tel INT);
INSERT INTO t2 VALUES
( 1, 'vasya',    'ru@localhost' ,1111),
( 2, 'petya',    'ru@localhost1',2222),
( 3, 'vasya',    NULL           ,3333),
( 4, 'petya',    ''             ,4444);
SELECT CASE WHEN t2.email > '' THEN t1.id ELSE '' END id, 
       CASE WHEN t2.email > '' THEN t1.name ELSE '' END name, 
       CASE WHEN t2.email > '' THEN t1.lang ELSE '' END lang, 
       CASE WHEN t2.email > '' THEN t1.city ELSE '' END city, 
       CASE WHEN t2.email > '' THEN t1.date ELSE '' END date, 
       COALESCE(t2.email, '') email, 
       t2.tel
FROM t1
JOIN t2 ON t1.name = t2.name_test
ORDER BY t1.id, t2.tel;


id
name
lang
city
date
email
tel


1
vasya
ru
msk
1970-01-01
ru@localhost
1111







3333

2
petya
ru
spb
1970-01-01
ru@localhost1
2222







4444

id	name	lang	city	date	email	tel
1	vasya	ru	msk	1970-01-01	ru@localhost	1111
						3333
2	petya	ru	spb	1970-01-01	ru@localhost1	2222
						4444