To produce identical ciphertext for different datatypes, cast the input of AES_ENCRYPT to the same data type to produce identical ciphertext.
The ciphertext produced by AES_ENCRYPT differs for two different data types given the same input value and same key. A join of two ciphertext columns that holds encrypted values of two different data types may therefore not return identical results.
For example, assume:
CREATE TABLE tablea(c1 int, c2 smallint); INSERT INTO tablea VALUES (100,100);
The value AES_ENCRYPT(c1, 'key') differs from AES_ENCRYPT(c2,'key') and the value AES_ENCRYPT(c1,'key') differs from AES_ENCRYPT(100,'key').
To resolve this issue, cast the input of AES_ENCRYPT to the same data type. For example, the results of these code fragments are the same:
AES_ENCRYPT(c1, 'key');
AES_ENCRYPT(CAST(c2 AS INT), 'key');
AES_ENCRYPT(CAST(100 AS INT), 'key');