Union and union all in postgresql – (row bind)

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 and Union all in Postgre sql 6

Union all in Postgresql:

Union all concatenates but does not remove duplicates

Union and Union all in Postgre sql 5

Lets discuss with an example. Lets take two table for example

Table 1 : SummerFruits

Union and Union all in Postgre sql 1

Table 2 : Fruits

Union and Union all in Postgre sql 2

 

Union in Postgresql:

SELECT * FROM SummerFruits
UNION
SELECT * FROM Fruits

Union concatenates and also remove duplicates so resultant table will be

Union and Union all in Postgre sql 4

 

Union ALL in Postgresql:

SELECT * FROM SummerFruits
UNION ALL
SELECT * FROM Fruits

Union all concatenates and does not remove duplicates so resultant table will be

Union and Union all in Postgre sql 3

 

                                                                                               

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.