By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE a (species VARCHAR(255) PRIMARY KEY, dutch VARCHAR(255));
CREATE TABLE b LIKE a;
CREATE TABLE c LIKE a;
INSERT INTO a VALUES
('species1', 'dutch1_'), -- present in both, dutch equal
('species2', 'dutch2a'), -- present in both, dutch differs
('species3', 'dutch3a'), -- present in both, dutch in a only
('species4', NULL ), -- present in both, dutch in b only
('species5', 'dutch5a'), -- present in a only
-- ('species6', 'dutch6a'), -- present in b only
('species7', NULL ); -- present in both, no dutch
INSERT INTO b VALUES
('species1', 'dutch1_'),
('species2', 'dutch2b'),
('species3', NULL ),
('species4', 'dutch4b'),
-- ('species5', 'dutch5b'),
('species6', 'dutch6b'),
('species7', NULL );
SELECT * FROM a;
SELECT * FROM b;
| species | dutch |
|---|---|
| species1 | dutch1_ |
| species2 | dutch2a |
| species3 | dutch3a |
| species4 | null |
| species5 | dutch5a |
| species7 | null |
| species | dutch |
|---|---|
| species1 | dutch1_ |
| species2 | dutch2b |
| species3 | null |
| species4 | dutch4b |
| species6 | dutch6b |
| species7 | null |
SELECT *
FROM ( SELECT species FROM a
UNION
SELECT species FROM b ) AS all_species
LEFT JOIN a USING (species)
LEFT JOIN b USING (species)
ORDER BY species;
| species | dutch | dutch |
|---|---|---|
| species1 | dutch1_ | dutch1_ |
| species2 | dutch2a | dutch2b |
| species3 | dutch3a | null |
| species4 | null | dutch4b |
| species5 | dutch5a | null |
| species6 | null | dutch6b |
| species7 | null | null |
INSERT INTO c (species, dutch)
SELECT species, COALESCE(a.dutch, b.dutch)
FROM ( SELECT species FROM a
UNION
SELECT species FROM b ) AS all_species
LEFT JOIN a USING (species)
LEFT JOIN b USING (species);
SELECT * FROM c;
| species | dutch |
|---|---|
| species1 | dutch1_ |
| species2 | dutch2a |
| species3 | dutch3a |
| species4 | dutch4b |
| species5 | dutch5a |
| species6 | dutch6b |
| species7 | null |