Remember that UNION (and its cousins INTERSECT and EXCEPT) essentially require each of the provided queries to be run separately and the results collated. In this example (using PostgreSQL, but the same applies in MySQL), we can see exactly what's happening.
I have a small table with some users and their favourite colours:
peter=# select * from users;
┌────┬─────────┬──────────────────┐
│ id │ name │ favourite_colour │
├────┼─────────┼──────────────────┤
│ 3 │ Francis │ blue │
│ 4 │ Toby │ blue │
│ 1 │ Joey │ red │
│ 2 │ Dwayne │ purple │
└────┴─────────┴──────────────────┘
(4 rows)
Time: 0.400 msNow, let's say we want all users with a favourite colour of blue or red; if we use a UNION the following happens:
peter=# explain select name from users where favourite_colour = 'blue' union select name from users where favourite_colour = 'red' ;
┌───────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────┤
│ HashAggregate (cost=35.33..35.39 rows=6 width=58) │
│ Group Key: users.name │
│ -> Append (cost=0.00..35.31 rows=6 width=58) │
│ -> Seq Scan on users (cost=0.00..17.62 rows=3 width=58) │
│ Filter: ((favourite_colour)::text = 'blue'::text) │
│ -> Seq Scan on users users_1 (cost=0.00..17.62 rows=3 width=58) │
│ Filter: ((favourite_colour)::text = 'red'::text) │
└───────────────────────────────────────────────────────────────────────────┘
(7 rows)
Time: 3.868 msAs you can see, the query plan involves
- two sequental scans (
Seq Scan) that each perform aFilter, - an
Appendoperation which is actually performing theUNION, - plus
Group KeyandHashAggregatesteps, from which the resulting recordset can be returned.