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.

 

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 and INTERSECT ALL 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 and INTERSECT ALL in PostgreSQL 2

 

Table 2 : Fruits

INTERSECT and INTERSECT ALL in PostgreSQL 3

 

 

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 and INTERSECT ALL in PostgreSQL 4

 

 

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 and INTERSECT ALL 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.

    View all posts