How about this for a complete answer:
DNS has the concept of a CNAME record, where an entry can point to a different entry, as per adaptr's answer. So, a DNS lookup of "redirect.example.com" will return a CNAME that says "example.hostingservice.com", which will subsequently be looked up to some A record with an IP address (with perhaps more levels, if the target of a CNAME is also a CNAME, etc.) This is a standard part of DNS.
Some DNS providers, but not all, will let you create a non-standard "HTTP redirect" record. For example: http://help.dnsmadeeasy.com/record-entry/http-redirection/
This non-standard record appears to be what the OP refers to, and is geared towards hosted web sites, where it may not be possible to set up custom domains to your liking. What happens is that the "HTTP Redirection" record let's you map the website http://redirect.example.com to something like http://example.hostingservice.com. The DNS for "redirect.example.com" actually resolves to a server run by that DNS provider. That server runs a specialized web server, that will issue an HTTP redirect so that the browser goes from http://redirect.example.com to http://example.hostingservice.com.
A couple things get mixed up in this: there's a DNS query that resolves to a machine controlled by the DNS provider. There is an HTTP response from that machine's web server which sends people off to a different web server. This is not purely DNS, and, in fact, only works for web services because of the HTTP redirect. You can't use this for, say, mail. This is entirely for people who don't want to (or can't) set up their own web server to issue the HTTP redirect themselves. My example would be, say, an expensive SSL certificate for a custom domain on Heroku. Instead of paying for that, the DNS provider can give us a "HTTP redirect" record for an easy-to-remember hostname. Hitting that easy-to-remember hostname in a web browser will send us to the hard-to-remember Heroku server running SSL.