Most of all people want exclusivity. Only having a handful of neighbours is something that is hard to buy. It's easier enforced through regulation.
Established neighbourhoods push for regulations to keep people out.
In the past it absolutely was for racist reasons. Today more likely economic ones. Apartments means renters which means less wealthy people. Exclusive detached home areas are pretty much guaranteed to have a high floor of wealth.
People don't like to be casually accused of racism when there are other perfectly reasonable explanations.
This argument never made sense for me. Being able to put more units in the same space drastically increases the value of the land. It of course reduces the value of other things like having towering buildings around your 2 story home which blocks your view. In this sense, home owners need to turn to zoning to prevent that despite it being detrimental to their land value.
So rarely if ever is new apartment development targeted at a mansion district, and I've never seen an apartment dweller oppose that concept. I don't know why they would and I wouldn't expect them to.
If I'm in a city and and I own one of these houses, it's clear economically that wanting to limit building multi-unit housing near me enhances the value growth of my property - because you can't build nearly as many SFH as you can apartments. I'm sure racism and fear of the other is still an issue (I'm from the south, I know it's an issue based on personal experience) but it's far more economic imho today. The end result is the same, people want to hold on to what they see as part of their "intrinsic value" in their neighborhood setup.