There are several Editions of SQL Server and this has changed over the different versions. However the simplest way to look at this is with SQL 2012, which is the latest, released version at the time of writing. For more details on the editions, see Features Supported by the Editions of SQL Server 2012.
- Enterprise: the "complete" edition, however socket/core limitions apply to some licensing models
- Business Intelligence: lacks a lot of the "enterprise class" features
- Standard: scaled down from Enterprise but suitable for most needs
- Web: an enhanced Express really with some management features for support
- Express with Advanced Services: as Express with Tools but includes extras like SQL Server Data Tools, Full Text Search, Reporting Services
- Express with Tools: same as Express but with Management Studio
- Express: this is the free, capacity and feature limited edition, however it does have the basic engine
- Developer: the same as Enterprise but cannot be deployed to production, usually downloaded with MSDN Subscription, or possible for a small fee
Clearly issues like cost will have a bearing on which is chosen, however note that the SQL Server related pages on this site do not take notice of Editions or reference them.