Why did Sandor 'The Hound' Clegane abandon King's Landing in the middle of battle?

Sandor was basically a good man and he was obeying Joffrey only because he was bound by duty.

Sandor had a past with fire. In one of the episodes from Season 1, we learn that when Sandor was a kid, his elder brother Gregor ("the Mountain") shoved his face into brazier over a trivial incident, resulting in Sandor's horrific scars.

During the Battle of the Blackwater, he initially comports himself bravely, but the fire really shakes him. It's possible his memories of childhood flooded back, which is why he became disillusioned in regards to knighthood and duty. (He often speaks of his disdain for knights and their supposed nobility, citing his monstrous brother as an example. Sandor never accepts knighthood for this reason.)

The combination of his personal dread, and the realization that he's facing death by fire for King he does not love or respect (Joffrey's treatment of Sansa, who Sandor has great sympathy for is a major factor), led Sandor to abandon his post and his king. That's why he turns back, gets drunk, and abandons King's Landing.

We learn how Sandor "The Hound" Clegane was first disfigured during S01E04 at a tournament held for Ned Stark becoming Hand of the King:

[Video Link was dead, but transcribing remains... Huzzah!]

[Petyr Baelish to Sansa Stark] One evening Gregor found his little brother playing with a toy by the fire, Gregor's toy, a wooden knight. Gregor never said a word; he just grabbed his brother by the scruff of his neck and shoved his face into the burning coals, held him there while the boy screamed, while his face melted.

At the Battle of the Blackwater (as you mention S02E09) The Hound is confronted by a soldier who is completely on fire. This causes The Hound to freeze up and not dispatch his burning attacker and is only saved when Bronn shoots the solider with an arrow (scene below).

The Hound becomes disengaged in the battle after this encounter and retreats back through the gates when he and Tyrion exchange some words (same scene as above).

[Tyrion] You're on the wrong side of the wall.

[Sandor, voice quivering] I lost half my men. The Blackwater is on fire.

[Joffrey, shouting] Dog, I command you to go back out there and fight!

[the Hound shakes his head]

[Tyrion] You're in the Kingsguard, Clegane. We must beat them back or they're going to take the city... your king's city.

[Sandor] Fuck the Kingsguard. Fuck the city. Fuck the king.

[the Hound walks away]

I do not think it was Joffrey's attitude that caused Sandor to leave as Joffrey never treated Sandor well (always calling him "dog", speaking to him a derogatory manner, etc.) Sandor flashed back to his childhood and realized that if he was going to die, it sure as hell wasn't going to by burning to death. He almost admits as much to Sansa after he leaves the battle and goes to her chambers (scene below).

[Sandor] The lady is starting to panic.

[Sansa] What are you doing here?

[Sandor] Not here for long, I'm going...

[Sansa] Where!

[Sandor] Someplace that isn't burning. North might be, could be.

[Sansa] What about the King?

[Sandor] He can die just fine on his own.

This ends up being a recurring theme. See his reaction during the duel with Beric Dondarrion (S03E04) and being asked to look into the flames by Thoros of Myr (S07E01).