Depends on the aircraft.
In most piston driven aircraft, the main battery is there for starting. The main battery also sort of runs all electrical equipment, lights and avionics. The only thing it does not run is the ignition system of the engine. I say “sort of” because the alternator supplies the battery and aircraft with more than enough electricity to supply the aircraft’s needs and recharge the battery simultaneously and at a slightly higher voltage. If the alternator goes out, it should not interrupt the total supply of electricity to the aircraft systems.
Think of the battery as a bucket with a hole in it, placed under a faucet (the alternator/generator). As long as the faucet is open enough to supply more than the bucket needs to remain full, you are getting all of the faucets water. If you partially or fully close the faucet, you are only going to get the water that comes from the bucket. But, your water flow is never completely interrupted.
On the other hand, the standby battery is a separate, isolated supply of electricity. As long as the main battery/alternator combination is supplying electricity, the relay is self supporting to keep the circuit closed to supply electricity from the main battery to the aircraft’s Bus Bar (electrical connection to the aircraft’s systems). If the main battery/alternator combo is not supplying electricity because it is dead, the relay will open the circuit from the main battery. It will simultaneously close the circuit supplying electricity from the standby battery to the Bus Bar. This would happen if the alternator/generator was offline or inop long enough to drain the battery.
The relay is probably a spring and electromagnetic set up. The state of being ON makes the main battery active. The state of being OFF makes the standby battery active.
Think of it like the faucet and bucket in the previous example, if the bucket were attached to a spring loaded shut off valve for another faucet. That second faucet was being fed by a container of water. It also had a hose bypassing the bucket. Once the bucket became light enough, the second faucet would turn on to supply you with water. At least, temporarily in a no water emergency.