If you believe that SFH buyers do not insist on SFH neighborhoods (whole cities are not neighborhoods), and should welcome denser zoning because it makes their lot more attractive for redevelopment, go circulate a petition for this among SFH owners and see how far it gets.
To your other point about insisting on SFH-only neighborhoods, in large parts of the country, there are apartment buildings and large commercial areas spread between and in single family neighborhoods, and that hasn't stopped people from buying houses there. I wasn't arguing that they all should support higher density zoning, just that it is in their financial best interest to do so. Another major problem is that because the Bay Area has refused to build anything for decades, it has decades of unmet (or to use urbanist language "induced") demand for houses, apartments, roads, transit, etc. that has to be met before prices and congestion will start to go down. To maximize housing affordability you need a mix of sprawl and density with appropriate infrastructure for the type housing built, and if you only do one type of growth you will have many people who are unhappy, which is why in another comment in this thread I accused the YIMBY urbanist of being the same as a NIMBY SFH owner, just with a different preferred housing type. The Gallup survey I linked earlier shows that there are more people currently living in cities (presumably in apartments) who wish to live in suburbs or rural areas (presumably in houses) than the reverse, so there is an unmet demand for "sprawl" and options like remote work.
My major point if a SFH owner in SF was purely motivated by money, they would welcome development on their land and wish to limit it on others'. Many of them aren't though, and they aren't lying or using euphemisms when they say they want the character of their neighborhood preserved. I hear tons of arguments that they are opposing multi-family housing because it would lower their property values, and that just doesn't make sense. A San Francisco with an apartment built for everyone there who wants one and no other changes would most likely still have million dollar houses, though the rent of the apartments would be less.
In addition, many suburban areas are not exclusively single-family houses; they already contain a mixture of apartment buildings and commercial areas, so I dispute your point that single family home buyers insist on exclusively single-family neighborhoods. In most suburban areas I've seen, the apartment buildings tend to cluster together, often near a commercial area, so if you're deep in a sea of houses it's unlikely that your next door neighbor will sell to a developer. People may not be excited if their next door neighbor sells to a developer who plans to build an apartment complex, but that doesn't change the value of their lot, which has increased because the developer is willing to pay more for it if they can build bigger buildings on it.
I was talking about a market with no rules restricting what can be built, and in such a market the neighbor has no say, so their personal opinion is irrelevant. The guy I was arguing with wanted restrictions on housing types he liked to be repealed, but wanted restrictions on housing types he didn't like to be enacted, and I was trying to point out that hypocrisy. Building houses versus apartments is a balancing act governed by demand, but market distortions like banning one type of housing or another can cause an undersupply of certain types of housing, which leads to an increase in prices for the type of housing that is undersupplied and for the substitute it's would-be buyers end up using.
[1] https://news.gallup.com/poll/245249/americans-big-idea-livin...