Use a LIKE clause in part of an INNER JOIN

Question

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?

Answer 1

It seems like you are looking for full-text search. Because you want to query a set of keywords against the card description and find any hits? Correct?

Answer 2

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.

Answer 3

Performance will be depend on the actual server than you use, and on the schema of the data, and the amount of data. With current versions of MS SQL Server, that query should run just fine (MS SQL Server 7.0 had issues with that syntax, but it was addressed in SP2).

Have you run that code through a profiler? If the performance is fast enough and the data has the appropriate indexes in place, you should be all set.

Answer 4

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.

Answer 5

LIKE '%fiend%' will never use an seek, LIKE 'fiend%' will. Simply a wildcard search is not sargable

Answer 6

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 text search, is there anything else I need to do?

Answer 7

@Dillie-O
How big is this table?
What is the data type of Description field?

If either are small a full text search will be overkill.

@Dillie-O
Maybe not the answer you where looking for but I would advocate a schema change...

proposed schema:

create table name(
    nameID identity / int
   ,name varchar(50))

create table description(
    descID identity / int
   ,desc varchar(50)) --something reasonable and to make the most of it alwase lower case your values

create table nameDescJunc(
    nameID  int
    ,descID int)

This will let you use index's without have to implement a bolt on solution, and keeps your data atomic.

related: http://stackoverflow.com/questions/20856

Answer 8

Currently the Card table has approxmiately 6000 records in it. The Description field is a varchar(max) column, but it doesn't have any major novels written into the field. The keyword table itself is maybe 20 records tops and the keyword value is coming from a varchar(100) field.

Answer 9

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.

Answer 10

Try this;

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