一、UNION ALL 与UNION的区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
union all的效率比union快的多，如果可以确认合并的结果集中不包含重复数据的话，那么就用union all
里面排序不起作用，放在最外面再进行排序

  1.             (
  2.         SELECT
  3.             null as video_comp_info_id,
  4.             null as camera_id,
  5.             null as create_time,
  6.             null as publish_flag,
  7.             null as publish_time,
  8.             null as video_type,
  9.             push_table.video_push_id,
  10.             push_table.video_is_push,
  11.             push_table.video_urgent,
  12.             push_table.video_calculation_type,
  13.             video_init_info.video_url,
  14.             video_init_info.video_descrption as video_description,
  15.             alarm_id,
  16.             alarm_base_info.alarm_type,
  17.             alarm_base_info.alarm_address,
  18.             alarm_base_info.duty_ranks_id,
  19.             alarm_base_info.area_id,
  20.             ranks_info.RANKS_NAME,
  21.             ranks_info.ANCESTORS,
  22.             alarm_type.DICT_LABEL,
  23.             alarm_type.DICT_CODE,
  24.             alarm_base_info.alarm_base_info_name AS alarmName,
  25.             alarm_base_info.create_time AS alarmTime
  26.         FROM
  27.             ( SELECT * FROM video_init_push WHERE del_flag = 0 ) AS push_table
  28.             LEFT JOIN video_init_info ON video_init_info.video_init_info_id = push_table.video_init_id
  29.             LEFT JOIN camera_info ON camera_info.camera_info_id = video_init_info.camera_id
  30.             LEFT JOIN alarm_base_info ON alarm_base_info.alarm_base_info_id = camera_info.alarm_id
  31.             LEFT JOIN alarm_type ON alarm_type.DICT_CODE = alarm_base_info.alarm_type
  32.             LEFT JOIN ranks_info ON ranks_info.RANKS_ID = alarm_base_info.duty_ranks_id
  33.         )
  35.         union all 
  37.         (
  38.             SELECT
  39.                 video_comp.video_comp_info_id as video_comp_info_id,
  40.                 video_comp.camera_id,
  41.                 video_comp.create_time,
  42.                 video_comp.publish_flag,
  43.                 video_comp.publish_time,
  44.                 video_comp.video_type,
  45.                 null as video_push_id,
  46.                 null as video_is_push,
  47.                 null as video_urgent,
  48.                 video_comp.video_calculation_type,
  49.                 video_comp.video_url,
  50.                 video_comp.video_description,
  51.                 alarm_id,
  52.                 alarm_base_info.alarm_type,
  53.                 alarm_base_info.alarm_address,
  54.                 alarm_base_info.duty_ranks_id,
  55.                 alarm_base_info.area_id,
  56.                 ranks_info.RANKS_NAME,
  57.                 ranks_info.ANCESTORS,
  58.                 alarm_type.DICT_LABEL,
  59.                 alarm_type.DICT_CODE,
  60.                 alarm_base_info.alarm_base_info_name AS alarmName,
  61.                 alarm_base_info.create_time AS alarmTime
  62.             FROM
  63.             ( SELECT * FROM `video_comp_info` WHERE del_flag = 0 ) AS video_comp
  64.             LEFT JOIN camera_info ON camera_info.camera_info_id = video_comp.camera_id
  65.             LEFT JOIN alarm_base_info ON alarm_base_info.alarm_base_info_id = camera_info.alarm_id
  66.             LEFT JOIN alarm_type ON alarm_type.DICT_CODE = alarm_base_info.alarm_type
  67.             LEFT JOIN ranks_info ON ranks_info.RANKS_ID = alarm_base_info.duty_ranks_id
  68.             LEFT JOIN sys_area ON sys_area.AREA_ID=alarm_base_info.area_id
  70.         )
  71.                 ORDER BY 
  72.                         video_push_id DESC,
  73.                         video_urgent DESC,
  74.             video_calculation_type ASC,
  75.                         video_comp_info_id DESC