App installs occur with 3 different ways, an executable with custom code, msi install and executable that wraps/contains msi file within it. Executable files (.exe) contain executable code for a computer to run, and can do multiple things. Most of the software are available for download as executable. Whereas MSI files are database files, used by Windows Installer. They contain information about an application which is divided into features and components, and every component may hold files, registry data, shortcuts etc.
Difference Between MSI or EXE Deployment Through SCCM - Not all apps are msi but the deployment using msi file is easier than exe. As mentioned earlier msi fie contain information about the app, the SCCM can read most of the info from the msi file and use the install and uninstall switches accordingly. I would like to add a note that not all msi's contain uninstall info but most of them contain. With msi files you can achieve a clean install and uninstall of app. With the exe files, you might have to find out the switches that support silent install and usually these are found on the site where you download the executable. Detection method is also something that you need to find out for exe installation.