Convert pair comparison sql for MS SQL in Hibernate

Question

We use hibernate to handle different databases. There's a problem about using pair comparison sql when the data source is MS SQL. We want to know how to convert this statement for ms sql.

The following is the simplified sql.

notice:

1. column1 and column2 both are uuid and numeric type. ( in ms sql will be numeric(19,0)
2. the converted sql statement should be follow the form -- WHERE condition IN sub-query
3. new statement should take execution performance into consideration.

code:

select * from table1 t1
where  (t1.column1, t1.column2) in 
            select ( sub.column1, sub.column2) from table2 sub
            where sub.column3 = $var

the following is my trial I'm afraid of its low performance on string comparison and making index no effect.

SELECT *
FROM TABLE_1 T1
WHERE CAST(COL1 AS VARCHAR(19))+CAST(COL2 AS VARCHAR(19))
IN (SELECT CAST(COL1 AS VARCHAR(19))+CAST(COL2 AS VARCHAR(19))
    FROM TABLE_2 T2
    WHERE T2.COL3 = 'A')

is there any suggestion for us? thanks a lot!!

Answer 1

Why do you have to do a Where-In clause rather than a join?

select 
    t1.* 
from 
    table1 t1
    inner join table2 sub on
        t1.column1 = sub.column1 
        and t1.column2 = sub.column2
        and sub.column3 = $var