You can combine the results of two identically structured queries with the UNION keyword.
For example, if you wanted a list of all contact info from two separate tables, authors and editors, for instance, you could use the UNION keyword like so:
select name, email, phone_number
from authors
u...
If you need to sort the results of a UNION, use this pattern:
( SELECT ... )
UNION
( SELECT ... )
ORDER BY
Without the parentheses, the final ORDER BY would belong to the last SELECT.
When adding a LIMIT to a UNION, this is the pattern to use:
( SELECT ... ORDER BY x LIMIT 10 )
UNION
( SELECT ... ORDER BY x LIMIT 10 )
ORDER BY x LIMIT 10
Since you cannot predict which SELECT(s) will the "10" will come from, you need to get 10 from each, then further whittle do...
SELECT name, caption as title, year, pages FROM books
UNION
SELECT name, title, year, 0 as pages FROM movies
When combining 2 record sets with different columns then emulate the missing ones with default values.
SELECT 1,22,44
UNION
SELECT 2,33,55
SELECT 1,22,44
UNION
SELECT 2,33,55
UNION
SELECT 2,33,55
The result is the same as above.
use UNION ALL
when
SELECT 1,22,44
UNION
SELECT 2,33,55
UNION ALL
SELECT 2,33,55
This UNION ALL combines data from multiple tables and serve as a table name alias to use for your queries:
SELECT YEAR(date_time_column), MONTH(date_time_column), MIN(DATE(date_time_column)), MAX(DATE(date_time_column)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
...
