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 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
Table 2 : Fruits
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 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