I Dont' Understand With “not” In Full Text Search

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014. The following query returns results with 'Supervisor' in the Title field and 'To

Solution 1:

Why does this happen? Because even when searching across multiple columns, the full text search conditions must be satisfied within a single column. Thus your query is equivalent to:

SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (Title), 'Supervisor AND NOT Tokyo') AS tb1 ON tb1.[Key] = post.ID
UNION ALL
SELECT 
    * 
FROM post
    JOIN CONTAINSTABLE([post], (HTML_Description), 'Supervisor AND NOT Tokyo') AS tb1 ON tb1.[Key] = post.ID

Ways to work around this:

You should combine Title and HTML_Description into a single column (you can even create a computed column to do this) and create the full text index on this column.

Worth mentioning: In most cases you can rewrite the query using 2 or more CONTAINSTABLE or CONTAINS statements to search each keyword individually -- CONTAINS(*, 'apples') and CONTAINS(*, 'oranges') instead of CONTAINS(*, 'apples AND oranges') -- but then you'll either get multiple meaningless Rank columns (because each is calculated against a single keyword) or you won't get a Rank at all (if you use CONTAINS). The link below shows a simple example of this. However this doesn't apply to your case because of your use of NOT. You cannot perform a full text search using only a NOT condition -- CONTAINS(*, 'NOT Tokyo') will throw an error.

See Full-Text Search Queries with CONTAINS Clause Search Across Columns