The CHARSET_COLL collation produces a binary ordering based upon the current client character set. The NOT CASESPECIFIC version is designed to produce the results if the strings were converted to uppercase and then sorted in binary order on the client.
CHARSET_COLL gives you a collation that matches the client character set. For example, if the client character set is KANJIEBCDIC5035_0I, then the collation matches KANJIEBCDIC5035_0I order (rather than EBCDIC order).