MariaDB Crash Course - Ben Forta [51]
The following example sorts the results returned by the previously used UNION:
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
Output
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | TNT1 | 2.50 |
| 1003 | FC | 2.50 |
| 1003 | SLING | 4.49 |
+---------+---------+------------+
Analysis
This UNION takes a single ORDER BY clause after the final SELECT statement. Even though the ORDER BY appears to only be a part of that last SELECT statement, MariaDB in fact uses it to sort all the results returned by all the SELECT statements.
* * *
Note: Combining Different Tables
For the sake of simplicity, all the examples in this chapter combined queries using the same table. However, everything you learned here also applies to using UNION to combine queries of different tables.
* * *
Summary
In this chapter, you learned how to combine SELECT statements with the UNION operator. Using UNION, you can return the results of multiple queries as one combined query, either including or excluding duplicates. The use of UNION can greatly simplify complex WHERE clauses and retrieving data from multiple tables.
18. Full-Text Searching
In this chapter, you learn how to use MariaDB’s full-text searching capabilities to perform sophisticated data querying and selection.
Understanding Full-Text Searching
* * *
Note: Not All Engines Support Full-Text Searching
As explained in Chapter 21, “Creating and Manipulating Tables,” MariaDB supports the use of several underlying database engines. The MariaDB ARIA engine supports full-text searching, and all the crashcourse tables were created to use the ARIA engine (by specifying ENGINE=Aria) in the CREATE TABLE statements in create.sql. Keep this in mind, if you need full-text searching functionality in your applications; you need to use an engine that supports this capability.
* * *
In Chapter 8, “Using Wildcard Filtering,” you were introduced to the LIKE keyword that is used to match text (and partial text) using wildcard operators. Using LIKE it is possible to locate rows that contain specific values or parts of values, regardless of the location of those values within row columns.
In Chapter 9, “Searching Using Regular Expressions,” text-based searching was taken one step further with the introduction to using regular expressions to match column values. Using regular expressions, it is possible to write sophisticated matching patterns to locate the desired rows.
But as useful as these search mechanisms are, they have several important limitations:
• Performance—Wildcard and regular expression matching usually requires that MariaDB try and match each and every row in a table (and table indexes are rarely of use in these searches). As such, these searches can be time-consuming as the number of rows to be searched grows.
• Explicit control—Using wildcard and regular expression matching, it is difficult (and not always possible) to explicitly control what is and what is not matched. An example of this is a search specifying a word that must be matched, a word that must not be matched, and a word that may or may not be matched but only if the first word is indeed matched.
• Intelligent results—Although wildcard- and regular expression–based searching provide for flexible searching, neither provides an intelligent way to select results. For example, searching for a specific word returns all rows that contain that word and does not distinguish between rows that contain a single match and those that contain multiple matches (ranking them as potentially better matches). Similarly, searches for a