EXE files can be anything. Almost everything you run on your computer is started by an EXE file (exceptions are Windows itself, drivers). They contain executable code for a computer to run, and can do anything.
All About MSI
MSI files are database files, used by Windows Installer. They contain information about an application which is devided into features and components, and every component may hold files, registry data, shortcuts etc. The MSI file also contains the UI that is to be used for installing, and various other data such as any prerequisites to look for, custom actions to execute, the order of the installation procedure, whether to support Administrative installations, etc. It can also contain the actual files to be installed themselves (this isn't always the case though, the files can also be in an external CAB file or just as plain uncompressed files in a location where MSI can find them).
MSI files are the current recommended way of doing installations on Windows. The alternative is writing a program that performs the installation itself.
MSI files are executed by an EXE file that is part of Windows, called MSIEXEC.EXE. This application reads the data in the MSI file and executes the installation.
All About EXE
Windows Installer is pretty new, especially the newest version (3.0). Often installations that use MSI still come with an EXE (e.g. SETUP.EXE). This EXE is a so-called 'bootstrapper'. It doesn't perform the installation, it simply checks if the correct version of Windows Installer is present on the system, if not it launched the MSI Redistributable (MsiInstA.exe or MsiInstW.exe depending on the platform) and then launches MSIEXEC.EXE on the MSI file. In certain cases (especially Internet downloads), the MSI file and MSI redistributable are packed inside that EXE file, so you don't see they're there.
So installations can come in three flavours:
- A custom, third-party installation system in an EXE file.
- A Windows Installer installation in an MSI file.
- An EXE file that bootstraps an MSI file (that may be embedded inside the EXE file).
MSI files can only be installations. EXE files can be literally anything that can run on your computer.