What is major difference between MSI and EXE installation files?
What is an installer? Well, an installer prepares a system to allow a program to run: it installs that program. During this process, an installer (on Windows) might copy files into system locations, place shortcuts to launch it, update libraries, remove older versions, etc.. There is no universal installer standard or format; any program that performs these actions ("installs" another program) may be called an installer.
An "exe" file in Windows is known as the Portable Executable file, and contains arbitrary instructions, either native machine code or .NET bytecode. There is nothing about the PE format that will identify it as an installer. Of course, with arbitrary machine code you are able to write an installer - and those "exe" installers you see take that approach.
Some people write their own custom installers. Others use installer generators to save some time and effort; these include NSIS, InnoSetup, InstallShield and more.
MSI is a specific format used to store information about an installation, a database of what steps to take and which files to put where. By itself, it is not executable; it is actually interpreted by msiexec.exe on Windows systems. This interpreter will then follow the steps laid out in the MSI file. You could in theory create any exe that uses a database similar to MSI (and NSIS, InnoSetup, etc., do something similar).
What makes MSI special is that it's included with and interpreted by built-in tools, and it is also integrated into Active Directory for easier deployment across many machines in a corporate environment, and Clem's answer covers that in more detail.
MSI files are database files (specifically MS-SQL databases) which describe the installation via database tables and install via a complex set of installation sequences defined within the MSI file itself.
MSI files are not installing as EXE files in the background as "Clem" writes - they are consumed by the Windows Installer Engine that is accessible via the msiexec.exe command line interface. Or perhaps try Advanced Installer's annotated msiexec.exe command line samples. Then the install itself is run via two different sequences: the user interface sequence and the actual installation sequence - the latter runs in system context using the LocalSystem account. This arrangement is necessary to allow the installer temporary admin rights, or elevated rights which is the proper term.
EXE installers can be anything. Most often they are older style script based installers that are not based on databases, but an EXE file can also be a wrapper containing an MSI files and associated runtime components. It all depends and must be investigated in each particular case.
MSI is acronym for Microsoft Silent Installer, it runs .exe installer in background.
It is used in GPO, for example if you want to deploy an application on a Windows Infrastructure you are managing, You have to create a new GPO rule with the MSI and the MSI will be installed on every machine without user intervention needed.
GPO being "Group Policy Object" which "provides the centralized management and configuration of operating systems, applications, and users' settings in an Active Directory environment."