Raw red onions are used more for their color than flavor, when you cook them the color pretty much goes away.
I used to make onion soup using half red and half yellow onions, just because the recipe I was using called for that.
White onions are stronger than either red or yellow onions.
Sweet onions are entirely different, ordinary yellow onions get pretty sweet if you caramelize them. I made a batch of French onion soup with Vidalia onions once, it was almost too sweet to eat.