Joins on Encrypted Columns

• The joining columns have the same datatype. For cipher text comparisons, char and varchar are considered to be the same datatypes, as are binary and varbinary.

• For int and float types, the columns have the same length. For numeric and decimal types, the columns must have the same precision and scale.

• The joining columns are encrypted with the same key.

• The joining columns are not part of an expression. For example, you cannot perform a cipher text join on a join where t.encr_col1 = s.encr_col1 +1.

• The encryption key was created with init_vector and pad set to NULL.

• The join operator is ‘=’ or ‘<>’.

• The data uses the default sort order.

This example sets a schema to join on cipher text:

create encryption key new_cc_key for AES
        with init_vector NULL
create table customer
        (custid int,
        creditcard char(16) encrypt with new_cc_key)
create table daily_xacts
       (cust_id int, creditcard char(16) encrypt with
        new_cc_key, amount money........)

You can also set up indexes on the joining columns:

create index cust_cc on customer(creditcard)create index daily_cc on daily_xacts(creditcard)

SAP ASE executes the following select statement to total a customer’s daily charges on a credit card without decrypting the creditcard column in either the customer or the daily_xacts table.

select sum(d.amount) from daily_xacts d, customer c
     where d.creditcard = c.creditcard and
     c.custid = 17936