Double action means when you pull the trigger you both cock and release the hammer (i.e. two 'actions'). Single action means you just release the hammer/striker.
Single action is generally more desirable as it's quicker to shoot and just all round more pleasant as a rule because you don't have to apply much pressure to the trigger. In a single action it's generally the slide moving back that resets the hammer for you instead of you having to do it yourself. E.g. Glock, M9, 1911 - basically any modern pistol can fire in single action. A lot of them will only fire in single action.
Non-blow back pistols are almost always double action as there's no slide resetting the hammer. Blow back are almost always single action. E.g. TM Mk. 23 and most revolvers.
You also have guns that can fire in double or single action like the M9. You can have it uncocked and still pull the trigger back to both pull back and release the hammer (like a double action). As soon as you fire that first BB or bullet, the slide comes back and you're in single action until you run out of ammo.
You can also manually pull the hammer back on most revolvers and then fire it single action, but obviously there's no blow-back system like a slide to reset the hammer so you'll need to do it every shot or go double action. Some revolvers will only do single action and you have to manually pull the hammer back for every shot.
This whole thing really matters more with real steel than it does in airsoft, but for CQB you'd likely want a single action pistol for quicker response and to stop your finger from getting too tired (heh).