En realidad las "tres" formas tienen el mismo significado, solo cambia el tratamiento.

探偵 es el sustantivo "detective" o "investigador privado" en su forma mas general

探偵さん es el tratamiento respetuoso con el que se le llama a un detective en lugar de usar su nombre, es como decir "señor detective"

名探偵 es un título que se otorga a un investigador que se ha hecho de fama, generalmente se usa acompañando al nombre de la persona (por decir 名探偵山田), en español equivale a decir "el famoso detective..."

Aparte hay otras palabras que se pueden traducir como "detective" en español, por ejemplo 刑事 que se usa sólo como el grado de detective dentro de la policía (similar a "capitán" o "sargento" de la policía).