How to do a FULL OUTER JOIN in MySQL and perform GROUP BY a 'column'

Question

Here are my DMLs and DLLs:

CREATE TABLE `table1` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value1` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table2` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value2` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table3` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table4` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value4` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `table1`
(`id`, `email`, `value1`)
VALUES
(1, 'email1@test.com', 1.1),
(2, 'email2@test.com', 1.2);

INSERT INTO `table2`
(`id`, `email`, `value2`)
VALUES
(2, 'email2@test.com', 2.2);

INSERT INTO `table3`
(`id`, `email`, `value3`)
VALUES
(1, 'email1@test.com', 3.1),
(2, 'email2@test.com', 3.2);

INSERT INTO `table4`
(`id`, `email`, `value4`)
VALUES
(1, 'email1@test.com', 4.1),
(2, 'email2@test.com', 4.2);

This is what I am currently doing to emulate FULL OUTER JOIN:

SELECT * FROM table1 as t1 
LEFT JOIN table2 AS t2 ON t1.id = t2.id
LEFT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
LEFT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t1.id IS NULL
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
RIGHT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t2.id IS NULL
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
RIGHT JOIN table3 AS t3 ON t2.id = t3.id
RIGHT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t3.id IS NULL;

The result is like:

id	email	value1	id	email	value2	id	email	value3	id	email	value4
1	email1@test.com	1.1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	email2@test.com	1.2	2	email2@test.com	2.2	2	email2@test.com	3.2	2	email2@test.com	4.2
NULL	NULL	NULL	NULL	NULL	NULL	1	email1@test.com	3.1	1	email1@test.com	4.1

Desired result:

id	email	value1	id	email	value2	id	email	value3	id	email	value4
1	email1@test.com	1.1	NULL	NULL	NULL	1	email1@test.com	3.1	1	email1@test.com	4.1
2	email2@test.com	1.2	2	email2@test.com	2.2	2	email2@test.com	3.2	2	email2@test.com	4.2

My questions:

1. As you can see from the result, id = 1 gets split into 2 rows. Why? How can I get all ids to be in a single row with their respective values. Basically have all row be of unique ids and email. If they have values in each table then show it else show NULL.
2. I would like to not have the id and email repeat and have it only once at the beginning. How can I achieve this.

Answer 1

SELECT table1.*,
       table2.*,
       table3.*,
       table4.*
FROM (
  SELECT id FROM table1
  UNION ALL
  SELECT id FROM table2
  UNION ALL
  SELECT id FROM table3
  UNION
  SELECT id FROM table4  
  ) AS table0
LEFT JOIN table1 USING (id)
LEFT JOIN table2 USING (id)
LEFT JOIN table3 USING (id)
LEFT JOIN table4 USING (id)

id	email	value1	id	email	value2	id	email	value3	id	email	value4
1	email1@test.com	1.1	null	null	null	1	email1@test.com	3.1	1	email1@test.com	4.1
2	email2@test.com	1.2	2	email2@test.com	2.2	2	email2@test.com	3.2	2	email2@test.com	4.2

fiddle