Usage example of mysql union Query

In web projects, the entire site search problem is often encountered, that is, the customer wants to enter a word in the search box of the website, then, the page containing the word will appear in the search results on the entire website. Since a web project cannot use a single table, union searches are generally used to solve the problem.

The following lists the SQL statements used for this union joint search to facilitate the Next memo.

Select * from (SELECT 'id ', 'subobject' FROM 'Article' WHERE 'active' = '1' AND 'subobject' LIKE '% adjust image % 'ORDER BY' add _ time' DESC) as t1union allselect * from (SELECT 'id ', 'class _ name' AS 'subobject' FROM 'web _ class' WHERE 'activity' = '1' AND 'class _ name' LIKE '% adjust image % 'ORDER BY' class _ id' DESC) as t2unionselect * from (SELECT 'id ', 'subobject' FROM 'Article' WHERE 'active' = '1' AND ('subobject' LIKE '% adjust %' OR 'subobject' LIKE '% image % ') order by 'add _ time' DESC) as t3;

The preceding SQL statement uses union all and union to perform joint queries. The difference between the two is that union all lists all query results that meet the conditions, union performs filtering on all query results that meet the criteria to remove duplicate results.

The preceding SQL statement is interpreted as the article table and web_class table belong to two different tables, so duplicate results are not removed here. However, the SQL query statement of the third branch of the preceding joint query is a query statement that is then combined by word segmentation, the query result of this SQL statement must include the query result of the first branch SQL statement, which is unnecessary here. Therefore, duplicate query results are not removed if all is not used.

Articles you may be interested in

• Mysql converts unix timestamp to date format, and MySQL queries the current time
• MySQL uses limit 1 to improve query efficiency
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• 30 methods for optimizing SQL statement query in mysql
• Summary of the causes and solutions for slow mysql Server Query
• Improves the query speed of millions of data records for MySQL
• MySQL limit Query Optimization
• How to query data of mysql today, yesterday, last 7 days, last 30 days, last month, and last month