Is tragic love or fulfilled love the better subject for a story?
I don't think that it is fair to say that one is "better" than the other. It all depends on what it is that you are setting out to do with the story. When you leave the reader at the end, do you want for him or her to keep thinking about what might have been had the romantic connection been fulfilled or do you want the reader to feel satisfied with the outcome and comfortable in the probability of happily ever after? Hollywood tends to go for the happily ever after ending because that is what makes the majority of audiences happy. Many novels and plays have been changed for film to reflect this desire. Audiences want to see the hero prevail and, in a romance, this is accomplished when the star crossed lovers live happily ever after. In real life, however, as in many very good works of fiction, a happy ending is not guaranteed!
There is one novel that makes for a great story, in spite of the fact that love is fulfilled and conflict is de minimus. That is Happy All the Time, by Laurie Colwin, one of my favorite writers for fiction and food writing. It is remarkable how she managed to write a satisfying story about people who are essentially as the title describes.
Generally, though, I agree with those who think that a story needs conflict, resolved or unresolved. Tragic love, of course, is unfulfilled love, but even a story that ends with fulfilled love usually needs some conflict. We seem to need this tension or conflict to want to read on to see what happens next. I wonder if this is simply a human tendency, the same tendency that makes us want to read bad news in the paper or to slow down when we see a car accident.
This is an interesting question because, if you notice, all Hollywood movies that show a romance always end it up becoming fulfilled. Why? Because Hollywood knows that people are not going to pay 11 dollars per movie ticket to go and see misery and disgrace unless the genre specifically calls for it. Therefore, in terms of audience, fulfilled love is often more acclaimed than the misery and punishment that comes from tragic love.
However, let's go back to the topic of genre: If you are writing a Gothic story in which fate decides all and the atmosphere is cold and lonely, there is no reason why you want to fulfill anything. After all, Gothic lit is all about unfinished everything. Yet, if you are writing under a realistic fiction or modern fiction genre, you may want to fulfill the relationship so that the reader can come full circle with the story.
It depends on what you mean by "better." Do you mean more interesting? More fulfilling? More moving? You need to establish criteria for what makes a story good in your eyes (or the eyes of your audiences).
I think that tragic love is more interesting. We are programmed to expect and desire everything to "turn out ok" in a typical story. When the love turns toward tragedy, that desire intensifies and heightens our suspense and emotional involvement. It jerks tears and prompts that "NOOOOOO!" that might escape our lips as we turn the page or sit in the theater. It prods that sense of regret and what-could-have-been that we all have lurking inside us, and prompts questioning about whether the suffering was worth it.
Fulfilled love, though, is... well... more fulfilling! It wraps everything up the way we want it to go and leaves that happy, glowy feeling that warms our heart at the end of a Disney movie. This is what we all want for ourselves, right? A happy ending. It's so satisfying to engage in these stories, especially after the characters have had struggles, pain, and missed communications along the way. Our heroes win--that always elicits a smile from engaged readers and viewers.
More moving? Well, I think I'd argue that both categories could suit or fail to reach this qualification. As far as an emotionally moving story goes, it's more about the believability of characters or situations, as well as the overall quality of the storytelling. If you can manage to create any kind of love story that avoids cliche and features unique characters, you'll have something to be proud of!
I think that at some point, at least, the love has to be unfulfilled so that there is some stress in the story. I don't think it has to end up being tragic, but I do think there has to be some uncertainty.
Stories are supposed to show something about the human condition. That usually comes out best under stressful conditions. If the love just goes completely smoothly, there is no stress and we don't find out anything about your characters. So give them some adversity, but it's okay if it comes out well in the end.
Your question is, which is the better subject for a story. Both are good subjects. The deciding factor should be, what is your story. Unless you're going to force the ending in a certain direction to fulfil certain criteria, you should be going with the story you have thought up. If it's good enough, the ending won't matter.
However, if you're asking from a marketing perspective, happy endings, called Romances, sell better; probably earn a lot of money, if Danielle Steele is any yardstick. Unfulfilled love stories, Tragedies basically, may not have mass appeal, but they have lasting value. Posterity will probably be reading tragedies while the romances will have ended up on the scrapheap.
As I write this, Jane Austen is tapping me on the shoulder to say that fulfilled love does sell, and does have staying power. There will always be the exception, which proves that if you simply write the story that you have, without paying attention to market forces, and if the story is good enough, which is another way of saying, if it is deeply felt, it will succeed regardless of the ending.