Union and union all in PostgreSQL returns the combined (row binded) values from two or more table. Union removes the duplicate after combining. Union all does not remove duplicate.
Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates
.
Union in PostgreSQL: performs a DISTINCT on the result set, eliminating any duplicate rows. Union concatenates and also remove duplicates
Union all in PostgreSQL: Union all concatenates but does not remove duplicates
Let’s discuss with an example. Let’s take two table for example
Table 1 : SummerFruits
Table 2 : Fruits
Union in PostgreSQL:
SELECT * FROM SummerFruitsUNIONSELECT * FROM Fruits
Union concatenates and also remove duplicates so resultant table will be
Union ALL in PostgreSQL:
SELECT * FROM SummerFruitsUNION ALLSELECT * FROM Fruits
Union all concatenates and does not remove duplicates so resultant table will be
