Onions such as Walla Wallas, Vadalias, Mauis and others are sweet because they have lower quantities of the sulfur compounds that cause the "bite" associated with more pungent onions.  There is some discussion as to whether this is because the particular variety is less capable of making the sulfur compounds or if perhaps because they are grown in soil that has very low quantities of sulfur and therefore less is absorbed into the plant.

In either case, these are the same compounds that cause your eyes to sting and water when you are cutting onions (see I Never Hurt an Onion, So Why Did It Make Me Cry?).

Sweet onions are preferred by some cooks in recipes where they are served raw, such as in salads or sandwiches.  If you are cooking the onions though, it makes more sense to use less expensive varieties such as common yellow onions.  The reason for this is that the sulfur compounds cook out as the onions are sautéed.  If you are caramelizing onions for something like a pissaladiere (see Flavor Layering) or to make French onion soup, it is nearly impossible to tell whether the onions you started with were a sweet or regular variety.