As a bit of background: OS 9 treated displayed each folder's contents only once on screen, and always in its own window, and always in the same place on-screen. This was done to mimic the real life desktop and folder paradigm. OS X follows a more web-like paradigm, where each Finder window can be used to view any folder's contents, and a single folder can be viewed simultaneously in multiple windows.
However, you can relive the old days of OS 9's spatial Finder by turning off the sidebar and toolbar. To do this, click the capsule-shaped button in the upper right of a Finder window. Once the sidebar is gone, opening folders will act like they did in OS 9: opening into their own windows.
Note, it's actually possible to have both OS 9-style folder windows and OS X-style browser windows open at the same time.
[robg adds: This behavior isn't new in Snow Leopard; it works the same way in 10.5. Also, if you use column-view mode, this hint doesn't work; it only applies to opening folders when using icon or list view modes.]

