It all depends on what you are looking for. Pickled red onions are fantastic on sandwiches and tacos. On a traditional hamburger I would go with either sweet or yellow. On deli sandwiches I like very thinely sliced white onion with the extra crunch and pungency but I think that's just my preference.
I would say that shallots have a more delicate structure with their thinner scales and has some notes of garlic. They're great in sauces or raw like in something like steak tartar.