This is a really old conversation and it has steadily become less and less relevant over the past 5 years. The most effective SEO practices these days involve off-page and off site linking and on-page optimization is gets slightly less and less effective every year. Tricks like keyword stuffing URLs, title attributing everything, and no-follow sculpting, which used to actually make a difference at one point in time, essentially make no difference these days. For most SEO aware web developers these days, SEO is more and more about accessibility and usability, as it should be.
The most effective tricks these days mostly involve passing as much link juice to the site as possible, and matching it to good content that is based on sound keyword research and user demand. In order to do this in such a way that actually makes a significant difference in ranking without being penalized is usually beyond the the average developer "dabbling" in SEO. The blackhat term for this is "link laundering" and it's really the only way to get the huge traffic gains in a reliable and sustained manner. Most SEO companies fit into one of the 3 following types:
1. Basic SEO is getting easier to execute with all the hand-holding that Google gives out to webmasters these days, however the benefits are minimal. Most of that realm is about making sure you're not screwing up your content, rather than optimizing it. The kinds of gains you get by doing this are maybe a 30% increase in traffic, assuming you site is not screwed up to begin with.
2. On the other hand, using link laundering/pyramids even less talented SEOs can see somewhere in the area of a 400% increase in traffic without much risk of a penalty. This is a lot trickier though because imitating a strong, natural, niche link graph is a lot harder than most people think.
3. The last strategy is commonly referred to as "turn and burn". This basically involves taking whatever content you have and link spamming the hell out of it which will cause it's traffic to spike briefly before being banned from Google's index. Ever seen all those spammy comments in you blogs that are pointing to a really crappy landing page? This is what they are doing.
Now granted I've made a lot of generalities here. Most of the time I see people discussing this topic they seem to not understand what SEO is compared to what it was 7 years ago which is why I'm taking the time here to explain this. The rules change a little when you start out with a valuable asset such as a large quantity of good content (such as any of the Q&A sites), or a set of very high quality links (anyone that's been covered by the major news outlets) because you can base your strategy on getting the most out of those assets.
Anyway, now that I've gone off on that tangent the easy answer is yes, good SEO is good for the web because it makes content more usable and accessible, and bad SEO is bad for the web because it means more spam.