For things that require RoHS, I've been using a version of lead-free popular with assembly houses- Sn99.3/Cu0.7 2% flux, which seems to work okay.
The joints are not quite as shiny as the beloved Sn63/Pb37 with RMA flux, but quite acceptable.
Left two joints are lead-free, right joint is Sn63/Pb37.
@Kaz & @LongStrokinYerMomma are near to the right explanation.
When you talk about mechanical properties of a metal/alloy we have to consider lattice structures. And in this case not much chemical reaction is of our concern.
You see, two phenomena are responsible for this observation:
Ability of metal/alloy to be drawn into wire is called ductility. When solder wire's billet is drawn through various dies of reducing diameters - it undergoes a process called strain hardening which makes it more resilient (i.e. to bend repeatedly without fracturing easily) to shear/deforming forces as compared to the initial cubical billet of same alloy. Hence when you melt it, it loses strain hardening & undergoes re-crystallization which makes it appear more brittle.
Diamond is the hardest material, not just because of its bonds but because of its perfect lattice structure. If you compare degree of perfection of lattices PER unit mass of a small cube, say 1mm3 & a large cube, say 20mm3 of chemically identical alloy/metal/mix, you will find the smaller cube to be more perfect hence stronger/harder than the larger cube, even though their chemical compositions are exactly same (this is what user @LongStrokinYerMomma pointed out in his Abstract from that paper)
To get a simpler everyday feel of it, think of breaking a stick, you can easily break a 2-feet long stick but not 10-cm long stick, yes in this case lever-action/torque-arm plays a role, but you get the idea.
Your logic:
The solder wire is less dense, either through being hollow or having a flux core, making it seem easier to bend. This seems less likely, because tin whiskers seems much harder than solder wire despite being thinner.
is perfectly valid, it partly explains why soldering wire is pliable. But note that the assertion Solder on a circuit board is just as soft as the wire solder it came from is definitely incorrect.
Best Answer
During the transition from liquid to solid, the solder must briefly be in an intermediate state. Kind of a slushy state where it is not solid, but also does not flow by itself. If the pin moves while the solder is in that state, then the motion of the part will create a hole or tunnel in the solder larger than the pin.
Then the solder solidifies fully.
So now you have a pin inside of a solder hole, with clearance all around it. Depending on whether it moves or how much it moves, it will come in and out of contact with the walls of the hole. In other words, it will have intermittent contact.
Depending on current and voltage through the pin, intermittent contact can lead to heating (due to high resistance) and electrical arcing (due to breaking circuit while current is flowing) which can lead to additional failures.
For a low-power, low-voltage logic connection, it can lead to intermittent erroneous behavior because the signal may not be in the logic state it is supposed to be in.