UNION ALL combines the rows returned by two SELECT statements and keeps every duplicate. Think of it as stacking two spreadsheet lists without removing repeated entries. Use it when repeated rows are meaningful or when you want the results exactly as each query found them. section 10.3
Keep both matching names
A customer query can find first names for last names beginning with D, while an author query finds first names beginning with C. UNION ALL combines those lists. If both queries return Chris, the result contains Chris twice; UNION would show only one Chris. section 10.3
SELECT FirstName FROM customer WHERE LastName LIKE 'D%' UNION ALL SELECT FirstName FROM author WHERE FirstName LIKE 'C%' ORDER BY FirstName;
This complete query combines filtered customer and author names, preserves duplicate names, and sorts the combined result by FirstName. section 10.3
UNION ALL can sometimes produce the same rows as one SELECT with OR conditions. For example, two separate searches for Daly and Dixon can be combined, or one search can test for either name. Knowing both approaches is useful when a query performs worse than expected. section 10.3
Compared with UNION, UNION ALL avoids duplicate removal, so UNION is generally slower because it does extra work. But when a UNION ALL result is very large and repetitive, UNION may send less data over the network. Consider both the required result and performance. section 10.3
In MySQL, UNION ALL can help emulate a FULL OUTER JOIN: combine a LEFT JOIN with a RIGHT JOIN, then filter the second query to exclude matching rows already returned by the first. Without that filter, shared rows appear twice. section 10.4
Avoid mixing UNION and UNION ALL in one query. Their order affects which duplicates remain, and some database systems may reject the query or yield confusing results. section 10.7 section 10.8
