Rewriting nested queries as joins often leads to more efficient execution and more effective optimization. In general, sub-query unnesting is always done for correlated sub-queries with, at most, one table in the FROM clause, which are used in ANY, ALL, and EXISTS predicates. A uncorrelated sub-query, or a sub-query with more than one table in the FROM clause, is flattened if it can be decided, based on the query semantics, that the sub-query returns at most one row.
SELECT * FROM SH.products p WHERE p.prod_id = (SELECT s.prod_id FROM SH.sales
s WHERE s.cust_id = 100996 AND s.quantity_sold = 1 )
can be rewritten as:
SELECT p.* FROM SH.products p, sales s WHERE p.prod_id = s.prod_id AND
s.cust_id = 100996 AND s.quantity_sold = 1