It's probably mentioned in the above link but...
"Connected" hub is the hub with the best reception and performance to the router if the router based on algorithm baked into HomeKit. If the difference in reception and performance to the network between a "Connected" hub and non "Connected" hub (standby) is marginal they won't switch. However if its performance difference substantial (and meets HomeKits requirements) the "Connected" hub will switch.
So for example if you have an AppleTV with a wireless connection has throughput of 800mb/s with RSSI -40 and a HomePod has a throughput of 800mb/s with an RSSI of -50. They probably won't switch because both are excellent. However if you have a "Connected" home with a throughput of 400mb/s and plug in an AppleTV right next to router and its 800mb/s with RSSI -20 they will almost certainly switch automatically.
"Standby" hubs are acting as repeaters to communicate with devices in close proximity (bluetooth). This makes them effectively network extenders.
For example, with my front door lock which is bluetooth it doesn't work reliably when I disconnect the HomePod that is closest too it. That HomePod is always in standby due to its range from the router.
"Standby" hubs are in standby because they will become "Connected" if something causes the "Connected" hub to disconnect or throughput and reception become very poor.
The exception in my experience is the iPad, I can't force that to become the connected device with other hubs plugged in, presumably due to conversing battery as much as possible.
I made a video of my Home Hubs switching the connected around after I disconnect the connected HomePod Right from the network and then reconnect it. First it goes to the AppleTV 4k, then to the AppleTV then puts everything in standby than switches to the HomePod Left and stays that way. It will likely be using the HomePod Right later on as it seems that has the best connection to the router.