I have a cold water faucet in the backyard, the problem is when it is
on the water drips and sprays out of the knob. I'm assuming that the
whole faucet needs to be replaced.

The pipe coming out of the wall stucco is maybe 1/2" pipe, From the
wall to faucet it goes pipe-nut-exposed thread (part of faucet body)
-faucet body.

I was wondering if this is just a compression fitting.. Or is it a
Pipe threaded nut soldered on the pipe and the faucet threads onto it?

Sounds like a typical hose bib. Go to Lowes or Home Depot and get one
for under $4. It is a good idea to hold the pipe with a grip because
after 20 years - and rot can happen. I just bought a house and replaced
four hose bibs in under an hour.