StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You want:</p> <pre><code>SELECT keyword FROM A WHERE NOT EXISTS ( SELECT TOP 1 1 FROM B WHERE A.keyword LIKE '%' + B.word + '%' ) </code></pre> <p>Note that doing this kind of like query with a <code>%</code> at the start of the term causes indexes on <code>B.word</code> to not be used.</p> <p>My test code (using SQL Server so the table declares for testing are TSQL but still relevant):</p> <pre><code>DECLARE @A TABLE ( keyword varchar(50) ) DECLARE @B TABLE ( word varchar(50) ) INSERT INTO @A VALUES ('php'), ('php developer'), ('Developer'), ('C#') INSERT INTO @B VALUES ('developer'), ('fcuk'), ('music') SELECT keyword FROM @A A WHERE NOT EXISTS (SELECT TOP 1 1 FROM @B B WHERE a.keyword LIKE '%' + B.word + '%') </code></pre> <p>Results:</p> <pre><code>keyword php C# </code></pre> <p>Edit: A little more explanation:</p> <p>This query is the logical extension of your query:</p> <pre><code>select * from A where keyword not in( select word from B); </code></pre> <p>Which can be written as the following (which is logically the exact same):</p> <pre><code>SELECT * FROM A WHERE NOT EXISTS ( SELECT * FROM B WHERE A.keyword = B.word) </code></pre> <p>What we want is to do is exclude records where the A.keyword <code>contains</code> the <code>B.Word</code> value. This is done by pre-pending and appending a <code>'%'</code> character and performing a <code>LIKE</code> rather than <code>=</code></p> <p>thus the query becomes:</p> <pre><code>SELECT * FROM A WHERE NOT EXISTS ( SELECT * FROM B WHERE A.keyword LIKE = '%' + B.word + '%') </code></pre> <p>The <code>SELECT 1 1</code> is just pointless and can equally be <code>SELECT *</code> if you rather, they both to the same thing inside an <code>EXISTS</code> statement and there's many answers here on StackOverflow dedicated to explaining why they're the same.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload