区别其实很简单:
union:组合数据,去重并保持原有的排序;
union all:简单组合数据(不会去重).
例:
表1 warehouse_test.xt_testing_01
document_item_sku_key document_item_level_key item_quotation_price_num
2068 95 140.00
118070 190 5750.00
123343 190 55.00
118646 190 111.00
表2 warehouse_test.xt_testing_02
document_item_sku_key document_item_level_key item_quotation_price_num
2068 95 140.00
118070 190 5750.00
118646 190 112.00
123343 190 55.00
118646 190 112.00
118646 190 112.00
91618 385 222.00
91618 385 222.00
99031 385 150.00
91618 385 222.00
-- union 去重,效率低
SELECT *
FROM warehouse_test.xt_testing_01
UNION
SELECT *
FROM warehouse_test.xt_testing_02;
结果:
-- union all,效率高
SELECT *
FROM warehouse_test.xt_testing_01
UNION ALL
SELECT *
FROM warehouse_test.xt_testing_02;
结果: