The difference is that you can link to a SHARED library with the linker, but you cannot link to a MODULE with the linker. On some platforms.
So… to be fully cross-platform and work everywhere CMake works, you should never do this:
# This is a big NO-NO:
add_library(mylib MODULE ${srcs})
target_link_libraries(myexe mylib)
To be fair, on Windows, they’re both just dlls, and so this code might actually work. But when you take it to a platform where it’s impossible to link to the MODULE, you’ll encounter an error.
Bottom line: if you need to link to the library, use SHARED. If you are guaranteed that the library will only be loaded dynamically, then it’s safe to use a MODULE. (And perhaps even preferable to help detect if somebody does try to link to it…)