No, many products use hashing instead of sequential access and
merges.

I would also have done this query like so in a conventional SQL engine
built on top of a file system with indexes:

1) Build temp table using (S.city = 'NY')  -- should be pretty small
subset, but if it is not indexed, you may have to scan the table.
2) (SH.snum = S.snum) with an index on what looks like PK-FK.  In some
products that relationship would be in a linked list structure and be
really fast.
3) (P.pnum = SH.pnum) with an index

In short, the answer is that it all depends...

Found this on DB2 site regarding sorting decisions:

"When it evaluates a nested loop join, the optimizer also decides
whether to sort the outer table before performing the join. If it
orders the outer table, based on the join columns, the number of read
operations to access pages from disk for the inner table might be
reduced, because they are more likely to be be in the buffer pool
already. If the join uses a highly clustered index to access the inner
table and if the outer table has been sorted, the number of index
pages accessed might be minimized.
In addition, if the optimizer expects that the join will make a later
sort more expensive, it might also choose to perform the sort before
the join. A later sort might be required to support a GROUP BY,
DISTINCT, ORDER BY or merge join."

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/adm
in/c0005311.htm

As is often the case, it depends on the database engine. There is no 'for all
cases do x then y'.

There is an interesting book called "databases for smarties' or
something similar, which shows how much variation you can get in sql
statements on different database engines. In some cases, even the order
of the components of the where clause can have significant differences
in either performance or results (particularly with respect to outer
joins apparently). these differences can exist even when the different
engines supposedly support the same SQL standard.

Tim