Testing for query equivalence

In one of the Hash Join examples, a complicated query using EXISTS was used to determine whether each product had ever been ordered. There's theoretically another way to figure that out: the inventory information for each product includes a sales count. If those are complete—every product is included in that inventory, even if it's never been sold—then a query looking for zero sales of an item should give the same results:

SELECT prod_id FROM inventory WHERE sales=0;

This looks like the same list, but it's long enough that comparing every entry would be tedious. You can easily compare the output from two queries to see if they produce the same rows using the EXCEPT construct:

SELECT prod_id FROM products p WHERE NOT EXISTS (SELECT 1 FROM orderlines ol WHERE ol.prod_id=p.prod_id)
EXCEPT
SELECT prod_id FROM inventory WHERE sales=0;

This is particularly useful to know when working on optimizing queries by rewriting them. It...