EXCEPT in PostgreSQL returns the rows that are in the first table but not in the second table. (i.e.) Except Operator performs set difference in PostgreSQL. Except Operator removes the duplicate. Except ALL does not remove duplicate.
Both EXCEPT and EXCEPT ALL in PostgreSQL returns the common rows of two different SQLs. They differ in the way they handle duplicates.
Except in PostgreSQL: performs a DISTINCT on the result set, returns set difference in PostgreSQL.
EXCEPT ALL in PostgreSQL: Performs the set difference but does not remove duplicates
Lets discuss with an example. Lets take two table for example
Table 1 : SummerFruits
Table 2 : Fruits
EXCEPT in PostgreSQL:
SELECT * FROM SummerFruits EXCEPT SELECT * FROM Fruits
EXCEPT gets the set difference and also remove duplicates so resultant table will be
EXCEPT ALL in PostgreSQL:
SELECT * FROM SummerFruits EXCEPT ALL SELECT * FROM Fruits
EXCEPT ALL gets the set difference and does not remove duplicates so resultant table will be
