Difference Between 2 union queries

What is the difference between the following 2 queries? (They both produce the same results)

select *
from (
  select * from (
           select *
           from phppos_items
           where name like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
           where item_number like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
  开发者_StackOverflow中文版         where category like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  ) as top_rows
order by `name` limit 16

vs

select *
from (
           (select *
           from phppos_items
           where name like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  union
           (select *
           from phppos_items
           where item_number like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  union
           (select *
           from phppos_items
           where category like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  ) as top_rows
order by `name` limit 16

The first version has another set of temporary tables, which is useless and a waste of resources in this specific case.
All the following will produce same result:

SELECT * FROM T1;

SELECT * FROM (SELECT * FROM T1);


SELECT * FROM (SELECT * FROM (SELECT * FROM T1));

...
...

to infinity :-)

And in your very specific case this will be enough:

select *
           from phppos_items
           where 
                 (name like 'AB10LA2%' OR item_number like 'AB10LA2%' OR category like 'AB10LA2%')
           and deleted = 0
           order by `name` limit 16

The first query embeds an extra set of SELECT * FROM (sub-query). It will take slightly longer for the optimizer to sort it out.

Functionally, there is no real difference between the queries. The second is simpler and therefore preferable.

The only difference I see is the in the top one, the subquery components of the UNION are named. Obviously, since the second version doesn't error, MySQL doesn't require subquery names in this construction. BTW, have you tested that this version is faster than the corresponding single query using OR?