CODEWITHSUNDEEP
sqlsql-serverdesign-patterns
Dillie-O
Dillie-O

Reputation: 29755

Use a LIKE clause in part of an INNER JOIN

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

Upvotes: 22

Views: 112090

Answers (11)

Luis H Cabrejo
Luis H Cabrejo

Reputation: 324

Yes you can, but you need to use this format.

like CONCAT('%', @tblKeyword.Value, '%')

Upvotes: 0

Manoj
Manoj

Reputation: 21

try it...

select * from table11 a inner join  table2 b on b.id like (select '%'+a.id+'%') where a.city='abc'.

Its works for me.:-)

Upvotes: 2

John
John

Reputation: 584

Try this

    select * from Table_1 a
    left join Table_2 b on b.type LIKE '%' + a.type + '%'

This practice is not ideal. Use with caution.

Upvotes: 14

Kashif
Kashif

Reputation:

Try this;

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

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57093

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.

Upvotes: 1

jason saldo
jason saldo

Reputation: 9980

@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: Recommended SQL database design for tags or tagging

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 0

jason saldo
jason saldo

Reputation: 9980

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.

Upvotes: 5

Chris Miller
Chris Miller

Reputation: 4899

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.

Upvotes: 0

Darren Kopp
Darren Kopp

Reputation: 77697

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.

Upvotes: 1

Nick Berardi
Nick Berardi

Reputation: 54894

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?

Upvotes: 1

Related Questions