Working with set operations

In this recipe, we will be discussing various PostgreSQL set operations.

Getting ready

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.

How to do it…

1. 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                                                        
   ----------------------------------------------...