Use a LIKE clause in part of an INNER JOIN

Asked 2008-08-21T16:45:48.183

Can/Should I use a LIKE criteria as part of an INNER JOIN when building a stored procedure/query? I'm not sure I'm asking the right thing, so let me explain.

I'm creating a procedure that is going to take a list of keywords to be searched for in a column that contains text. If I was sitting at the console, I'd execute it as such:

SELECT Id, Name, Description
  FROM dbo.Card
 WHERE Description LIKE '%warrior%' 
       OR
       Description LIKE '%fiend%' 
       OR 
       Description LIKE '%damage%'

But a trick I picked up a little while go to do "strongly typed" list parsing in a stored procedure is to parse the list into a table variable/temporary table, converting it to the proper type and then doing an INNER JOIN against that table in my final result set. This works great when sending say a list of integer IDs to the procedure. I wind up having a final query that looks like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblExclusiveCard ON dbo.Card.Id = @tblExclusiveCard.CardId

I want to use this trick with a list of strings. But since I'm looking for a particular keyword, I am going to use the LIKE clause. So ideally I'm thinking I'd have my final query look like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' + @tblKeyword.Value + '%'

Is this possible/recommended?

Is there a better way to do something like this?

The reason I'm putting wildcards on both ends of the clause is because there are "archfiend", "beast-warrior", "direct-damage" and "battle-damage" terms that are used in the card texts.

I'm getting the impression that depending on the performance, I can either use the query I specified or use a full-text keyword search to accomplish the same task?

Other than having the server do a text index on the fields I want to

sql sql-server design-patterns

Edit Report

4 Answers

Your first query will work but will require a full table scan because any index on that column will be ignored. You will also have to do some dynamic SQL to generate all your LIKE clauses.

Try a full text search if your using SQL Server or check out one of the Lucene implementations. Joel talked about his success with it recently.

answered 2008-08-21T17:06:11.050

Personally, I have done it before, and it has worked out well for me. The only issues i could see is possibly issues with an unindexed column, but i think you would have the same issue with a where clause.

My advice to you is just look at the execution plans between the two. I'm sure that it will differ which one is better depending on the situation, just like all good programming problems.

answered 2008-08-21T16:57:28.357

a trick I picked up a little while go to do "strongly typed" list parsing in a stored procedure is to parse the list into a table variable/temporary table

I think what you might be alluding to here is to put the keywords to include into a table then use relational division to find matches (could also use another table for words to exclude). For a worked example in SQL see Keyword Searches by Joe Celko.

answered 2009-05-01T08:52:31.220

Try this;

SELECT Id, Name, Description
FROM dbo.Card
INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' + 
                                CONCAT(CONCAT('%',@tblKeyword.Value),'%') + '%'

answered 2009-05-21T07:40:35.017

Use a LIKE clause in part of an INNER JOIN

4 Answers

Your Answer