String concatenation in where clause

Question

I posted the following question yesterday: Multiple 'in' statements in a where clause that need to match against each other

In this there was some discussion about a feature in Oracle which doesn't exist in Microsoft SQL, the specific line of code being:

where (suite_id,lease_id) in (('CCBG08','205059'),('CCBG14','152424'),('CCCF048','150659'))

Assuming that we have a table of (however with more rows):

suite_id lease_id

CCBG08 150659

CCBG14 152424

I extrapolated on this to create the following solution for Microsoft SQL:

select  *
from    property.lease_period
where   (suite_id + ' ' + lease_id) 
        in (
            ('CCBG08 205059'),
            ('CCBG14 152424'),
            ('CCCF048 150659')
        )

Unfortunately the performance implications of doing something like this are quite significant (as indexes aren't being used). I was wondering how this could be improved?

Solution 1

Presumably we are talking about dynamically-generated SQL here, right? So, in that case, why not just use the long-form:

select  *
from    property.lease_period
where
(suite_id = 'CCBG08' AND lease_id = '205059') OR 
(suite_id = 'CCBG14' AND lease_id = '152424') OR 
(suite_id = 'CCCF048' AND lease_id = '150659')

edit Re-reading your original thread, I see this is basically what you started with and were hoping to avoid. Considering that, you could use the method you posted (string concatenated values) with an index by creating a view off of property.lease_period, and include in that view a calculated column (suite_id + ' ' + lease_id as suite_lease - for example). Finally, create an index for that view on that column. Then change your code to select from this new view and column, rather than the base table.

Solution 2

I would just bite the bullet and spell out the OR conditions explicitly:

SELECT *
    FROM property.lease_period
    WHERE (suite_id = 'CCBG08' AND lease_id = '205059')
       OR (suite_id = 'CCBG14' AND lease_id = '152424')
       OR (suite_id = 'CCCF048' AND lease_id = '150659')

Solution 3

You could convert that particular condition of the WHERE clause into a join like this:

SELECT
…
FROM property.lease_period p
  INNER JOIN (
    VALUES ('CCBG08','205059'), ('CCBG14','152424'), ('CCCF048','150659')
  ) x (suite_id, lease_id)
  ON p.suite_id = x.suite_id AND p.lease_id = x.lease_id

Solution 4

You could use a JOIN onto a temp table/table var, which may be a neater way depending on your scenario/number of combinations:

DECLARE @data TABLE (suite_id VARCHAR(10), lease_id VARCHAR(10))
INSERT @data VALUES('CCBG08', '205059')
INSERT @data VALUES('CCBG14', '152424')
INSERT @data VALUES('CCCF048', '150659')

select  l.*
from    property.lease_period l
    JOIN @data d ON l.suite_id = d.suite_id AND l.lease_id = d.lease_id

Solution 5

Don't forget a solution with cte's ;)

with cte (suite_id, lease_id)  as (
   select         'CCBG08' , '205059' 
   union  select  'CCBG14'  ,'152424' 
   union  select  'CCCF048' , '150659'
) 
select * 
from cte 
 JOIN property.lease_period l 
   ON l.suite_id = cte.suite_id 
  AND l.lease_id = cte.lease_id

View more solutions

Comments

[Michael A]

For educations sake how would you accomplish the above whilst still using indexes?

[Jake Feasel]

If your index contains both suite_id and lease_id, then it will be used.

[Joe Stefanelli]

@MattH Well, a minute and 24 seconds, but who's counting? ;-)

[hookenz]

Doesn't suite_id and lease_id have an index? if not can you create it?

[Michael A]

Sorry, by the above I was referring to the original code in the question. I actually started with what you had here but was hoping to work out how to do this with concatenation for educations sake

[ypercubeᵀᴹ]

This works for SQL-Server 2008. But still, no WHERE (x.suite_id,x.lease_id) = (p.suite_id,p.lease_id) in SQL-Server.

[Andriy M]

@ypercube: No, no tuple comparison, which is a shame. I think I've seen a corresponding request on MS Connect, though I am not sure.