INTERSECT and INTERSECT ALL in postgresql

Intersect in Postgresql returns the common rows of two or more table. Intersect removes the duplicate after combining. Intersect all does not remove duplicate.i.e.

  • INTERSECT takes the common rows from the multiple tables and removes duplicates
  • INTERSECT ALL takes common rows from multiple tables and keeps duplicates as well.

Both INTERSECT and INTERSECT ALL in Postgresql returns the common rows of two different SQLs. They differ in the way they handle duplicates.

INTERSECT in Postgresql: performs a DISTINCT on the result set, returns the common rows of two different tables

Intersect in Postgresql 1

INTERSECT ALL in Postgresql: intersect all returns the common row but does not remove duplicates

Let’s discuss with an example. Let’s take two tables for example

Table 1 : SummerFruits

Intersect in Postgresql 1

Table 2 : Fruits

Intersect in Postgresql 2

 

INTERSECT in Postgresql:

SELECT * FROM SummerFruits
INTERSECT
SELECT * FROM Fruits

INTERSECT gets the common row from both table also remove duplicates so resultant table will be

Intersect in Postgresql 3

INTERSECT ALL in Postgresql:

SELECT * FROM SummerFruits
INTERSECT ALL
SELECT * FROM Fruits

INTERSECT ALL gets the common row from both table does not remove duplicates so resultant table will be

Intersect in Postgresql 5

 

                                                                                               

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.