Sexual tension is a situation which can arise between two or more people when they feel attracted to each other, but unable or unwilling to act on that attraction. In film, fiction, and television, it is a very commonly used plot device; you can probably think of a few examples of famous characters who are drawn to each other but unable to act on it, like Mr. Darcy and Elizabeth in Pride and Prejudice. In addition to occurring in fictional settings, of course, sexual tension also happens in real life.
This situation can have different effects on different people, depending on the type of attraction they feel, and their own emotions and behavior patterns. Often, sexual tension manifests itself in the form of awkward conversations filled with loaded questions and layers of meaning. Some people experience physical feelings as well, like the sensation that sparks are flying between them and the object of interest, or a heightened physical awareness of the other person. Thoughts may frequently turn to the object of interest as well, sometimes causing embarrassment or confusion.
Sometimes, people can become very adversarial when they experience sexual attention and struggle with their sense of attraction. The classic story about young children being mean to each other to diffuse their interest in each other is also true of adults, and such adversarial relationships often make for great fiction, film, or television. In Much Ado About Nothing, for example, Beatrice and Benedict trade barbs with each other constantly before finally marrying in the end.
In fiction, sexual tension can make a great plot device. As tension slowly builds up between characters, the audience can't help but be drawn in, comparing the slowly growing relationship with their own life experiences. Many authors and filmmakers are quite adept at drawing out the tension, allowing the plot to build to a boil before finally releasing the tension and marking the end of the story.
Episodic television also relies heavily on sexual tension, and sometimes the resolution of the tension spells the end of the series, as viewers find less to be drawn to when they see a happy relationship between the characters. In other series, the resolution of the tension creates a new direction for the series and the story, but the authors manage to keep the story interesting by creating a new dynamic between the characters. Soap operas in particular are famous for drawing out the sexual tension and creating tangled webs of sexual relationships and attraction.