MySQL - Excluding Specific Terms using Boolean Full-Text Search

Excluding Specific Terms Using Boolean Full-Text Search

Write a MySQL query to search for "marketing" in the "Content" column but exclude results that contain the term "advertising" using boolean operators.

Solution:

-- Search for the term 'marketing' but exclude results containing 'advertising' using BOOLEAN MODE.
-- The MATCH function is used to specify the column (Content) to search within.
-- AGAINST defines the search query and the mode (BOOLEAN MODE) for advanced search capabilities.
-- '+marketing' ensures that the term 'marketing' must be present in the results.
-- '-advertising' excludes any rows where the term 'advertising' appears.
SELECT * FROM Articles
WHERE MATCH(Content) AGAINST('+marketing -advertising' IN BOOLEAN MODE);

Explanation:

• Purpose of the Query:
• To retrieve articles about "marketing" while filtering out those that mention "advertising".
• Demonstrates the exclusion operator (-) in BOOLEAN MODE.
• Key Components:
• +marketing -advertising : Requires "marketing" and excludes "advertising".
• IN BOOLEAN MODE : Specifies the use of boolean operators.
• Real-World Application:
• Ideal for refining search results to meet specific content criteria.

Notes:

• Boolean mode supports a range of operators for precise query tuning.
• Check full-text index compatibility with boolean operators.

For more Practice: Solve these Related Problems:

• Write a MySQL query to search for "marketing" in the Content column but exclude rows containing "advertisement" and "promo" using BOOLEAN MODE.
• Write a MySQL query to perform a full-text search on the Content column for "marketing" while filtering out any articles that mention "ads" in any form.
• Write a MySQL query to search for "marketing" in the Content column using BOOLEAN MODE and exclude results containing both "advertising" and "sales".
• Write a MySQL query to execute a boolean full-text search for "marketing" in the Content column and omit rows that contain "banner" using the exclusion operator.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Full-Text Search in Natural Language Mode.
Next MySQL Exercise: Updating a Row and Rerunning a Full-Text Search.