Hi @Amardeepthi Venkata Siripurapu ,
Welcome to Microsoft Q&A!
Or think of the it this way: the client needs to know which values to decrypt and which to not encrypt. But it only gets the information once, for the entire column.
Thanks Erland for the detailed explanation.
I tried changing the collation for unencrypted field to match the encrypted ones.
This is needed. Because the union needs to be the same type of data.
Therefore, you must either query separately OR decrypt the column before using the union query(That is, modify the encryption type of the column to Plaintext).
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.