Yes the hostname on the cert must match the hostname portion of the url the cert is requested from. That is a requirement because there is an expectation that SSL not only secure the communication (encryption) but also ensure the user is connecting to the proper server (authentication). Without that requirement it would be trivial to spoof users with a MITM attack. User browses https://example.com, MITM hacker can't create a valid CA signed cert for example.com so he just uses any valid cert. User sees the url is https://example.com and it is SSL secured so he must be safe right? Nope he is connected to the hackers server. So the browser is going to warn the user. Hey you tried to go to https://example.com, the real example.com would have provided a cert for example.com but I got something different so very likely you are under attack right now.
WWW or not to WWW
One common scenario which leads to errors is requesting a cert with a hostname of www.* such as www.example.com. Everything is fine if the user tries to go to https://www.example.com, but if you decide you want to join the 21st century and handle traffic at https://example.com the user will get an error (you also can't redirect without a different error). User is trying to go to example.com but getting a cert for a different hostname (www.example.com).
Avoid getting a www.* only cert
It is (semi) standard practice for the CA to include www.* as an alt name for cert when the requested hostname is a root domain (no subdomain). A request for a cert with a hostname of
example.com will work with both https://example.com and https://www.example.com but if a request for a cert for
www.example.com will only work with https://www.example.com.
My cert has already been issued with a www.* hostname
You could get a second cert issued to handle the https://example.com traffic and use the existing www only cert for https://www.example.com but that is kinda pointless as the new cert can handle both hostnames. You may want to check if your CA will reissue a cert with the root hostname instead of www for free. I doubt they will but they might especially if it is a more expensive cert.
Well not always true
It looks like some CAs (see comments) will now do the opposite and for a cert requested on www.example.com will add example.com as an alt name. Nothing can be simple I guess. Check with your CA and the cert details. To avoid browser warnings the hostname of the url being used needs to be listed in the cert as either the hostname or the Subject Alternate Name.