PostgreSQL 10 High Performance - Third Edition

Hash joins

The primary alternative to a Merge Join, a Hash Join doesn't sort its input. Instead, it creates a hash table from each row of the inner table, scanning for matching ones in the outer. The output will not necessarily be in any useful order.

A query to find all the products that have at one point been ordered by any customer shows a regular Hash Join:

EXPLAIN ANALYZE SELECT prod_id,title FROM products p WHERE EXISTS (SELECT 1 FROM orderlines ol WHERE ol.prod_id=p.prod_id);
QUERY PLAN 
-------------------
Hash Join  (cost=1328.16..2270.16 rows=9724 width=19) (actual time=249.783..293.588 rows=9973 loops=1)
       Hash Cond: (p.prod_id = ol.prod_id)
     -> Seq Scan on products p (cost=0.00..201.00 rows=10000 width=19) (actual time=0.007..12.781 ...