SQL MATCH

What is the SQL MATCH function used for?

MATCH evaluates a full-text search string against one or more FULLTEXT-indexed columns and returns a relevance score or a Boolean result.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MATCH: MySQL 3.23+, MariaDB 5.3+, Amazon Aurora MySQL, Percona Server. SQLite supports a different MATCH syntax for FTS modules; PostgreSQL, SQL Server, and Oracle use alternative full-text functions and do not recognize MATCH.

SQL MATCH Full Explanation

MATCH is the core of MySQL and MariaDB full-text search. It compares a search expression against text stored in FULLTEXT-indexed columns. When used in a SELECT list, MATCH...AGAINST returns a relevance score (a floating-point value); when placed in a WHERE or HAVING clause, it filters rows whose relevance is greater than zero. Search behavior changes with the chosen modifier: IN NATURAL LANGUAGE MODE ranks by term frequency, IN BOOLEAN MODE interprets operators like +, -, and *, and WITH QUERY EXPANSION reruns the search with related terms to widen recall. Only columns of type CHAR, VARCHAR, or TEXT that belong to a FULLTEXT index can participate. Searches are case-insensitive for non-binary collations, ignore stopwords, and apply minimum length rules (4 characters by default). MATCH works only on MyISAM, InnoDB, and Aria engines that support FULLTEXT. It cannot reference columns from different tables or be combined with ordinary comparison operators.

SQL MATCH Syntax

SELECT ...
MATCH (col1 [, col2 ...]) AGAINST (search_expr [modifier]);

SQL MATCH Parameters

column_list (list) - One or more FULLTEXT-indexed CHAR, VARCHAR, or TEXT columns.
search_expr (string) - The search phrase enclosed in quotes.
modifier (enum) - Optional mode|||IN NATURAL LANGUAGE MODE (default), IN BOOLEAN MODE, WITH QUERY EXPANSION, or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION.

Example Queries Using SQL MATCH

-- Natural language relevance ranking
SELECT id,
       title,
       MATCH(title, body) AGAINST ('database index') AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('database index')
ORDER BY score DESC
LIMIT 10;

-- Boolean mode requiring sql and excluding oracle
SELECT *
FROM docs
WHERE MATCH(content) AGAINST ('+sql -oracle' IN BOOLEAN MODE);

-- Query expansion to broaden recall
SELECT id, subject
FROM emails
WHERE MATCH(subject, body) AGAINST ('"error 1213"' WITH QUERY EXPANSION);

Expected Output Using SQL MATCH

Queries return only rows whose FULLTEXT score is positive
When MATCH appears in the SELECT list, an extra column (score) shows the relevance value
ORDER BY score DESC sorts most-relevant records first

Use Cases with SQL MATCH

Implement site or application search without external engines
Rank articles, emails, or forum posts by relevance
Filter large text collections for specific keywords
Provide advanced Boolean search with inclusion and exclusion terms

Common Mistakes with SQL MATCH

Forgetting to create a FULLTEXT index before using MATCH
Mixing columns from multiple tables inside one MATCH call
Expecting exact substring matches without quoting phrases
Using ordinary comparison operators (e.g., MATCH(...) > 0) instead of AGAINST in WHERE
Ignoring stopword and minimum-length settings that silently drop terms

First Introduced In

MySQL 3.23 (1999)

Frequently Asked Questions

What is the difference between NATURAL LANGUAGE and BOOLEAN MODE?

NATURAL LANGUAGE MODE ranks results purely by statistical relevance without interpreting special symbols. BOOLEAN MODE treats +, -, *, ~, and parentheses as operators, giving you fine-grained control over inclusion, exclusion, wildcards, and weighting.

Do I need a FULLTEXT index to use MATCH?

Yes. MATCH only works on columns that belong to a FULLTEXT index. Add one with ALTER TABLE ... ADD FULLTEXT (col1, col2);

How do I boost the importance of a column?

List higher-priority columns first in the MATCH column list, or multiply the returned relevance score by a weight in the SELECT clause (e.g., MATCH(title) AGAINST (...) * 2).

Can I combine MATCH with regular WHERE conditions?

Absolutely. You can filter by MATCH score and other columns simultaneously, such as WHERE MATCH(content) AGAINST ('sql') AND status = 'published';