ALL ​

ALL keeps duplicate result rows. It is the default behavior of a SELECT, so SELECT ALL and SELECT have the same meaning.

UNION ALL also preserves duplicates from both inputs. Use DISTINCT or UNION when duplicate rows should be removed.

Syntax ​

SELECT ALL expressions FROM source
query UNION ALL query

Examples ​

Retaining repeated values ​

CREATE TABLE visits (country TEXT);
INSERT INTO visits VALUES ('GB'), ('GB'), ('FR');

SELECT ALL country
FROM visits
ORDER BY country
FORMAT csv;

"FR"
"GB"
"GB"

Appending query results ​

SELECT 1 AS value
UNION ALL
SELECT 1
FORMAT csv;

1
1

ALL does not define an output order. Add a final ORDER BY when order matters.