Two years later: The answers above insufficiently take in account the end user's perspective. Because of that I've been confused by Sharepoint for years.
I'll explain it differently :
TL;DR : Think "basic wiki pages".
Site Pages is the library that is here by default for a Team site (which is the very first site template that you see when you create a new site, and also probably the most basic).
This library is meant primarily for Wiki Pages (including the native Home.aspx). By default you can't change their Page Layout -- there's no menu for that in the ribbon. You can only change their Text Layout, which is some sort of half-baked Page Layout integrated directly into the Wiki Page, the reason being that the purpose of Wiki pages is not to make them fancy publishing pages. The purpose of wiki pages is to quickly add pages on many topics and make it possible for most members on the site. -- Think wikipedia.
The Wiki page has only one property (that is, visible by the end-user), which is its Title, which is also its URL. Apart from that, everything else (headers, text formatting, etc.) is stored as-is in the giant "body" area.
The two main content types offered in the Site Pages library are : Wiki Page, Web Part Page.
TL;DR : Think "fancy web pages with custom layout".
Pages is the library that is added when you activate the SharePoint Server Publishing feature on your site collection and web site. Or it's created directly when you create a new Publishing site from the Template.
This library is meant for every other sort of fancy web site pages, the main one being the Article Page content type.
You'll see that this content type has a ton of extra properties, that might not seem useful at first glance, but you'll see that they all aim at making a pretty site. For example, there's the Title field that lets you have a Title different from the URL. There's also the Rollup image field that lets you have a thumbnail picture for that page when it appears in the search results. And so on and so on.
The content types offered in the Site Pages library are : Page (very basic page, but still a Publishing page), Article Page, Welcome Page, Error Page, Enterprise wiki Page (not to be confused with : Wiki Page. This one is the Publishing wiki page)
Why use one or the other?
Most users who want to customize their website visually without messing too much with the master page will immediately ignore the Wiki pages and start modifying the Page Layouts available for the Publishing pages (Article Page and the gang).
The quote that says "tens of thousands of pages" (see most upvoted answer) is really confusing, but what it really says is : "If you're making a wiki site where people can add their pages themselves (with no hierarchy) then if it grows too big it will be a mess. Therefore, maybe you should consider switching to a Publishing site, aka a "real" website, where you decide the structure"
Please note that, SharePoint being SharePoint, you may go in the settings of the "site Pages" library and add some extra Content Types to it: if you add the "Page", "Article Page" and the other Publishing content types, then you can use "Site Pages" to store all your pages. I'm not saying that you should do it, for obvious functionalities reasons explained in other answers, but that you could. The separation between the two is really artificial.
Why were they separated in the first place?
For historical reasons. SharePoint started out as a simple Wiki tool, and then they sucked in the Publishing part. So at first they kept those two aspects separated. And since Microsoft is very good at retro-compatibility, they've left it like that ever since then.