To be more specific: the CPU has circuitry that, upon power-up, either a) sets the program counter to a specific initial address (like all zeros), or b) fetches a word from a specific location in read-only memory (the "reset vector") and loads it into the program counter.

Typically, the initial program counter value points to startup code in read-only memory. (On PCs, "read-only memory" is the BIOS, on most 8-bit game consoles, it's the chip in the game cartridge, on microcontrollers it's the PROM or flash memory where the firmware is stored, etc.)

It wouldn't make much sense to start executing code out of RAM on startup because the contents of RAM will be random garbage. Powering up an old game console without a cartridge does something similar--when there is no ROM chip physically connected to the CPU, all attempts to access ROM return 0x00 or 0xFF or something random, and the CPU just executes bogus instructions until it's powered off.

It's even more interesting than that. One of the things that the BIOS has to do, if I recall correctly, is to set up the memory controller. Until it does that, it's only able to execute out of registers and cache, no RAM.

If you're interested, Coreboot (an open source BIOS replacement) has interesting information on this.