In this recipe, we will be discussing various PostgreSQL set operations.
PostgreSQL provides various set operations, which deal with multiple independent data sets. The supported set operators are UNION
/ALL
, INTERSECT
/ALL
, and EXCEPT
/ALL
. In general, we use the set operations in SQL when we need to either join or merge operations among independent datasets. To process these independent datasets, PostgreSQL will evaluate each dataset operation independently, and then it applies the given set operation on the final datasets.
To demonstrate these set operations, let's query the
benchmarsql
to get all the customer IDs, that have not placed any online order:benchmarksql=# EXPLAIN SELECT c_id FROM bmsql_customer EXCEPT SELECT h_c_id FROM bmsql_history; QUERY PLAN --------------------------------------------------------------------...