Sex is an action between two people that is usually taken either for fun/release, or in order to have children - or both.
Fable
In Fable and Fable: The Lost Chapters, once you got married, you could have sex with your partner (whether your partner is male or female). The screen would fade to black and you would hear generic grunting and moaning sounds. Afterwards, you would end up standing where you were beforehand, and your spouse would say something about you.
Sex with prostitutes in the Darkwood Bordello is possible at any point in the game for a fee, unless you own the bordeello, where it then becomes free.
Fable II
In Fable II, you can now choose to have protected (with the use of a condom) or unprotected sex. You can have sex if either your spouse 'suggests' it or if you have the 'come back to my place' expression, learned using the book Come Hither, Dear then use a bed. If you have unprotected sex with your spouse, the female (If you chose not to have a gay marriage), whether it be the NPC or the player's character, may get pregnant. The screen will still fade to black as in Fable. Also, non-marital sex, extramarital sex and polygamy are possible in Fable II, as sex can happen with people you are not married to, with the risk of STDs if you don't use the condom along with some "Corruption" points. Your spouse may also give you an STD.
Fable II also allows the player to participate in orgies by using the 'come back to my place' expression on more than one villager. There is an achievement associated to this action (The Swinger). With the release of the See the Future DLC another achievement for sex becomes available. Having sex, or watching another player having sex, 25 times grants the player "The Paramour."
Fable III
Just as with Fable II, in Fable III you can still have sex with people you are not married to and can contract STDs. Prostitution still exists in Albion. There's even a chance to turn an orphanage into a whore house at the near end of the game. Sex with your co-op partner is also possible, along with getting STDs from them. The sex scene itself consists of a black screen with much giggling, creaking bedsprings, silly comments, and even sillier music.
Some Legendary Weapons require the player to have sex under set rules in order to unlock their augments.