'At night' means simply 'during the night', whereas 'in the night' has the rather more specific meaning of 'in the middle of the night', and thus tends to be limited to more poetic or graphic contexts than its simpler equivalent. Thus, we might have e.g.
I heard an owl hooting in the night.
(meaning 'at some point in the middle of the night')
but we would not say
?He works in the night.
to mean that his work occurs during the night hours.