MacBook currently has quiet SSD, will adding extra HDD cause constant noise?
Certainly it depends on the drive.  From my experience using Apple laptops that contained HDD's, there was NO noise unless the drive was failing (an unfortunately common occurrence).  But some HDD's are definitely louder than others.

One thing to clarify though: you will not hear the drive "spinning".  You may hear the drive being accessed. The primary source of noise is the actuator arm, not the spindle/platters.  When idle the drive should be silent.