Diffuse light sources are the most common and useful lights in painting. They make great fill lights, rim lights, and key lights, they always make subjects look appealing. They tend to soften features and are good for creating a pensive emotional response when used as key lights. Light reflected from "lambert surfaces" always fits into this category.
I didn't have time to paint examples and the great masters are better at it, so instead here's Edmund Blair Leighton above, using a bright overcast sky to support the gentleness of the scene. William Bouguereau's painting below that uses the common "north light" from a window to soften the girl's features and emphasize her beauty.
Direct or spot lights have a strong visual impact and are useful for creating drama or tension. You can mix the effects of direct light sources with the effect of diffuse light sources by using light sources that are somewhere in between. Here's a great example from Paul Delaroche of the drama created by a direct light source. Below that, Edmund Blair Leighton shows how a diffuse light can have some of the dramatic effect of a direct light, in this case by using a large window but positioning it some distance behind the subject.
Remember that spot lights are really just direct sources with something "off camera" casting a shadow, so the light beam has a specific shape to it. This shape doesn't always have a hard edge, a great example being sunlight blocked by soft-edged clouds (see Albert Bierstadt example below, on the rocks). So you can also mix the softening effects and gradients you'd get from a diffuse source with the hard shadows of a direct source.
Finally, nearby light sources are the most dramatic of all, but they can also be distracting if too strong or used too often, because of the visual tension they create. I also forgot to explain another thing about them on the other site, so I'll post it here instead.