LIKE using subquery returning multiple rows

Question

There 2 tables emailinfo(id,email) and keywordinfo(id,keyword). emailinfo contains 80,000 rows and keywordinfo contains 2000 rows.

I want emails from emailinfo table which does not contains keywords from keywordinfo table.

I want not like condition on multiple rows

I have kept all keywords in keywordinfo table

Now I have to fetch all the emails from emailinfo table which not containing any keywords from keywordinfo table.

I want query like following,

select email 
from emailinfo 
where email not like % (select keyword from keywordinfo)

I have tried following query

SELECT email 
FROM   emailinfo
       join keywordinfo on email  like '%' + keyword +'%'**

but its not giving me proper results and is very slow.

Answer 1

Instead of trying to come up with a scenario using LIKE, have you considered using FULL TEXT INDEXING?

Pinal Dave has a fairly good introduction on it that can help get you started: http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Answer 2

An alternative formulation eliminates the like:

Select
From emailinfo ie
Where not exists (select *
           From  ki
          Where charindex(ki.keyword, ie.email) > 0)

I apologize for the formatting, I'm on a mobile device.

I would also recommend that you change your table structures so each keyword is in a separate row. Then you won't need the like operator.

Answer 3

How about a slight variation of your second query...?

SELECT Email 
FROM   EmailInfo
LEFT JOIN KeywordInfo ON Email LIKE '%' + Keyword + '%'
WHERE KeywordInfo.ID IS NULL

Example Data:

CREATE TABLE #EmailInfo (ID INT, Email VARCHAR(50))
INSERT INTO #EmailInfo (ID, Email) VALUES (1, '[e-mail]')
INSERT INTO #EmailInfo (ID, Email) VALUES (2, '[e-mail]')
INSERT INTO #EmailInfo (ID, Email) VALUES (3, '[e-mail]')
INSERT INTO #EmailInfo (ID, Email) VALUES (4, '[e-mail]')
INSERT INTO #EmailInfo (ID, Email) VALUES (5, '[e-mail]')
INSERT INTO #EmailInfo (ID, Email) VALUES (6, '[e-mail]')

CREATE TABLE #KeywordInfo (ID INT, Keyword VARCHAR(50))
INSERT INTO #KeywordInfo (ID, Keyword) VALUES (1, 'sample')
INSERT INTO #KeywordInfo (ID, Keyword) VALUES (2, 'test')

SELECT Email 
FROM   #EmailInfo EmailInfo
LEFT JOIN #KeywordInfo KeywordInfo ON Email LIKE '%' + Keyword + '%'
WHERE KeywordInfo.ID IS NULL

DROP TABLE #EmailInfo
DROP TABLE #KeywordInfo

Answer 4

The following query will returns all the records from emailinfo table which don't have any keyword defined in keywordinfo table.

It's advisable to have an index on emailinfo.email fields to make the query execution faster.

SELECT * FROM emailinfo 
where not exists (Select 1 from
 keywordinfo where emailinfo.email like '%' + keywordinfo.keyword +'%')