Union of two relations¶

SQL exercise · Part I

Problem¶

Two relations record the mother and the father of each child. Combine them into a single relation of (parent, child) pairs, ordered by child and, for the same child, by parent.

Schema and test data¶

schema.sqltest_data.sql

create table maternita(
        madre           character(20) not null ,
        figlio  character(20) not null primary key
        );

create table paternita(
        padre           character(20) not null ,
        figlio  character(20) not null primary key
        );

-- Test data (not part of the exercise): a child known only through the mother.
INSERT INTO maternita VALUES ('Anna', 'Luca'), ('Anna', 'Sara'), ('Elena', 'Marco');
INSERT INTO paternita VALUES ('Paolo', 'Luca'), ('Paolo', 'Sara'), ('Franco', 'Marco');
INSERT INTO maternita VALUES ('Giulia', 'Teo');

Query¶

SELECT madre AS genitore, figlio
FROM maternita

UNION

SELECT padre AS genitore, figlio
FROM paternita

ORDER BY figlio, genitore

genitore	figlio
Anna	Luca
Paolo	Luca
Elena	Marco
Franco	Marco
Anna	Sara
Paolo	Sara
Giulia	Teo

Takeaways¶

UNION needs the same number of columns with compatible types; the alias genitore gives the combined column one name.
ORDER BY applies to the whole union, so it comes once, at the end.
UNION removes duplicate rows; UNION ALL would keep them.