As already stated, Night's Watch used to be an honorable order which was charged with defending the realm from terrors that lurked beyond the wall e.g. the Others. With time however, in aftermath of Battle for Dawn, the others were gone for good apparently. Slowly, people started growing cynical of the idea that such things ever existed and general idea became that Wall is just a useless heap of ice with sole purpose of stopping Wildling raiders.
Nowadays, Watch is made up of mostly former rogues and criminals.
Night's Watch is exclusively male order. They do not accept females. The only requirement for joining the Night's Watch is that you must be a male.
Why do People Join the Night's Watch?
Following are the reasons:
1. A chance at redemption after a great crime
If you have committed a great crime, your prosecutor might offer you a chance to redeem your honor by serving at the Wall or simply as mercy after your conviction.
Brynden "Bloodraven" Rivers was convicted on the charge of murdering Aenys Blackfyre. King Aegon V first sentenced him to execution but later offered him a chance to redeem himself at the Wall. Brynden Rivers accepted and later became Lord Commander of the Night's Watch. Similarly Eddard Stark was offered a chance to join the Night's Watch.
2. Lack of choice as a prisoner of small-account
If you are a prisoner of little to no importance, you don't apparently get a say in it. Lords of the realm can give wandering recruiters of Night's Watch their pick of the dungeons. Lord Eddard Stark offered the same to Yoren in KL.
Lucamore "The Lusty" Strong was a knight of Kingsguard. He broke his vow of celibacy and was gelded by King Jaehaerys I. Later the King sent him to the wall.
Chett was sent to the wall after he was convicted of murdering a girl.
Ulmer was an outlaw from infamous Kingswood Brotherhood. After the band was destroyed, Ulmer was sent to the wall.
Jaqen H'ghar, Rorge & Biter were sent to the Wall when Yoren picked them. It's another story that they never got there.
3. As punishment for Losing a political conflict
If you were involved in a political conflict and lose, the winning party might force you to join the Night's Watch. Some may even go as far as to threaten to kill you if you refuse. This is a favored tactic as it removes one's political opponents from the screen for good since NW is not allowed to do politics.
Eddard Stark was offered a chance to Join the Watch after he lost his political conflict with Cersei.
Vorian Dayne was sent to the wall after he lost the war against invading Martells under Nymeria's command. Along with him, Five of his allies were also sent to the wall after the defeat. Those included Garrison Fowler, Lucifer Dryland, Benedict Blackmont, Albin Manwoody and Yorick V Yronwood.
Ser Alliser Thorne and Jaremy Rykker were commanded to choose between axe and wall by Tywin Lannister after fall of King's Landing. Both of them chose Wall.
4. No prospects of Inheritance
If you are a younger son of a noble house, you will have no chances of inheritance since the eldest son gets it all. Instead serving as Household Knight for your brother, some young men choose to join Night's Watch in hopes of rising to Command of a Castle or Lord Commandership of the Watch itself. Some nobles of younger branches join due to their financial conditions. Bastards of high lords can't inherit as well which is why some of them join the Night's Watch where even a bastard can be lord commander.
Ser Waymar Royce was youngest son of his father and thus stood no chance of inheriting lands of House Royce. Therefore he joined the Night's Watch.
Eddison Tollett was a squire from a younger branch of House Tollett. He often speaks of being raised in a poor household. He joined the Nightswatch.
Denys Mallister was a younger son of House Mallister (Speculation since there has never been a Lord Denys Mallister but he was born in Castle of Seagard). He rose to command of Shadow Tower and was very close to being elected Lord Commander.
Lord Commander Hoare was younger son of King Halleck Hoare of Riverlands and Iron Isles. As his elder brother Prince Harren was the heir of his father, Hoare joined the Night's Watch (Since no other explanation is given).
Jon Snow joined the Night's Watch because he was a bastard with no better prospects. Robb would have inherited Winterfell and Rickon and Bran would hold lands for Robb. Which meant Jon would have to make his own destiny.
5. Wish to retire from usual duties and join the NW
You may wish to take down the mantle of your usual duties and take the black. It may or may not be completely out of personal choice.
Lord Commander Jeor Mormont abdicated his position as Lord of Bear Island and joined the NW as his son Jorah was an adult whom he probably deemed capable of running affairs of the Bear Isle.
Donal Noye quit from his service at Stormsend for House Baratheon and joined the Night's Watch instead when he lost an arm.
6. Wish to stay away from Intrigues and conspiracies of the Lords
If you have claims on certain titles, different lords may seek to use you for their own ends. To save yourself from this, you may choose to join the Night's watch because that means end of your claims.
Aemon Targaryen joined Night's Watch so that he could not be approached by lords unhappy with rule of his brother King Aegon V and be used against him.
7. Being raised as a member of Night's Watch since childhood
Night's Watch often adopts abandoned Kids who are then raised up to be members of the Night's Watch.
Mance Rayder was a wildling child but he was taken and raised up by the Watch.
8. You don't Sleep hungry at wall
Some poor people in cities might wish to join the watch just for food, housing and clothing. There were some of this sort in Arya's entourage such as Hot pie.
9. Because that's the only thing you ever wanted since you were a little girl
Night's Watch is exclusively male. They do not accept women as members. But some women may try to join the watch anyways by disguising themselves as men.
Danny Flint was a daughter of House Flint. She disguised herself as a boy and joined the Night's Watch. She was posted at Nightfort. Eventually her secret was revealed and she was raped and murdered by her sworn brothers.
Why do people visit the wall without the intention of joining the Night's Watch?
Following would be the reasons:
1. One of the Man-made wonders
Wall is a wonder in the world. It is one of its kind and therefore draws visitors. Tyrion Lannister visited the wall for just seeing it.
2. Trade with the Watch
You may visit the wall in order to trade with the black brothers. At Eastwatch-by-the-sea, trading ships visit often from as far as Essos. Wildlings also trade with the Watch.
3. Seeking refuge
Some people seek the wall for refuge against their enemies.
Alys Karstark reached the wall to seek refuge with Jon Snow who is a distant kin of house Karstark.
After learning about Red Wedding, Arya Stark also wished to make it to Jon at the wall.
It is however unclear that such women can be granted refugee status or not. NW is sworn not to get involved in political conflicts of the realm. While Jon did provide Alys Karstark refuge, it is unclear what any other Lord commander would have done as that would make House Karstark enemies of the Watch and amount to interference in politics.