Simpleboard (now: Joomlaboard) is the only forum which is
fully integrated with Mambo/Joomla. It can only run within Mambo/Joomla.
Some other forums (SimpleMachines, PHPBB, vBulletin) are "sort of" integrated by means of a "bridge" script. I.e. they accept the login information of your Mambo/Joomla site as login for themselves, but for the rest, they run their own thing.
Other forum scripts can only be given a Mambo/Joomla look in the sense that they can be made accessible to your visitors through a "wrapper". That's very simple, but wrapped scripts are
not a part of your Joomla/Mambo site - they only look as if they were

With regard to what forum to use, I'd say give some a try. Joomlaboard is neat because of its integration, but it is rather limited in its functionality. SMF and PHPBB are well established names, and they are very good, but prepare yourself for a good amount of tweaking, for instance to give these forums a look which corresponds to the rest of your site. I didn't try vBulletin, because it is commercial - if I'm not mistaken.
Good luck!
Ludo