In my experience they aren't different at all. Steam Workshop is just a distribution platform for mods. It's a mod installer and manager. It doesn't stop you doing it manually.