It detects all wireless in range, and asks you which one you'd like to connect. You select yours, enter the password, and from then on it will automatically connect.
If you aren't at home, it'll seek out any unsecured networks, inform you that it can't find yours, and ask you if you'd like to connect. For example we have a grocery store with an internet café that is 'unsecured' because they offer it for free. If it can't find an unsecured network, it will tell you so, and then you just don't have networking. At this point I usually turn Airport off to save battery life.
Same thing at my office. Once you join either of those 'foriegn' nets, it'll automatically connect to that one whenever you find yourself in range of it. My wife spent a month connected to a nearby dormatory's unsecured wifi network when she was in the hospital.
Also I usually mention that the Airport Extreme wireless in the Macbook is the most tenacious wireless seeking network card I have yet seen. It'll grab wireless where others won't. I was at a conference in a hotel recently, and the person sitting next to me in the conf. room had to go out into the lobby to get a signal from the hotel's free wireless with her Dell, while I could get good signal and on the net almost from the parking lot.