I think it depends on the context in which the dialog pops up.
Context 1: A standalone/independent action is performed ("Add item to favorites?"). In this case the yes/no -only options should be fine. It is a 'safe' situation, so there is no need for a 'cancel'.
Context 2: The action is part of a process, or will kick-start a process (imagine this to be a 'dangerous' situation, depending on your definition of safe/dangerous). Let's imagine the user is working on a text document, and closes the application without saving first. There may be many documents open at the same time, and the "process" involves looping through the open documents, and asking the user "Do you wish to save your changes to xxx.doc?". Let's consider the options:
Scenario 1: [Yes / No / Cancel]
- "Yes" saves changes, and (eventually) closes the app.
- "No" doesn't save changes and (eventually) closes the app.
- "Cancel" doesn't save changes (it does the same as 'No' in terms of
the original dialog question), but doesn't close the app. The process/loop ends immediately, and all the documents remain available for editing (app reacts as if the user didn't try to close it).
Scenario 2: [Yes / No]
- "Yes" saves changes, and (eventually) closes the app.
- "No" doesn't save changes and (eventually) closes the app.
The only difference came with the assumption that the app makes on what the user actually wants to achieve by closing the app (either clicking the close 'x' button, or pressing some short-cut key). Specifically, do the designers anticipate that users may close the app by accident (in general: will someone kick-off a difficult/impossible-to-reverse process, or find themselves in a 'dangerous' situation, and if so, should we allow them to get out of it / stop it somehow?) In this case, I think erring on the side of caution is better (include the 'cancel' button, even though it achieves the same effect as the 'no' when considering the wording of the dialog message).
As always (irrespective of "situation") I think the wording of the dialog message is the most important.