分区表join时，过滤分区，以下两种写法都可以，其执行过程相同

    • 过滤条件写在on中 ```sql explain select a.city_name,b.name from crisps_ods.ods_crisps_mch_address a left join crisps_ods.ods_crisps_mch_detail b on a.mch_detail_id = b.id and b.p_date = ‘2022-03-31’;

    STAGE DEPENDENCIES: Stage-4 is a root stage , consists of Stage-5, Stage-1 Stage-5 has a backup stage: Stage-1 Stage-3 depends on stages: Stage-5 Stage-1 Stage-0 depends on stages: Stage-3, Stage-1

    STAGE PLANS: Stage: Stage-4 Conditional Operator

    Stage: Stage-5 Map Reduce Local Work Alias -> Map Local Tables: $hdt$_1:b Fetch Operator limit: -1 Alias -> Map Local Operator Tree: $hdt$_1:b TableScan alias: b Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Filter Operator predicate: (p_date = ‘2022-03-31’) (type: boolean) Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Select Operator expressions: id (type: bigint), name (type: varchar(128)) outputColumnNames: _col0, _col1 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: bigint) 1 _col0 (type: bigint)

    Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: a Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: mch_detail_id (type: bigint), city_name (type: varchar(50)) outputColumnNames: _col0, _col1 Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col0 (type: bigint) 1 _col0 (type: bigint) outputColumnNames: _col1, _col3 Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col1 (type: varchar(50)), _col3 (type: varchar(128)) outputColumnNames: _col0, _col1 Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work

    Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: a Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: mch_detail_id (type: bigint), city_name (type: varchar(50)) outputColumnNames: _col0, _col1 Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: bigint) sort order: + Map-reduce partition columns: _col0 (type: bigint) Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: varchar(50)) TableScan alias: b Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Filter Operator predicate: (p_date = ‘2022-03-31’) (type: boolean) Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Select Operator expressions: id (type: bigint), name (type: varchar(128)) outputColumnNames: _col0, _col1 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Reduce Output Operator key expressions: _col0 (type: bigint) sort order: + Map-reduce partition columns: _col0 (type: bigint) Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE value expressions: _col1 (type: varchar(128)) Reduce Operator Tree: Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col0 (type: bigint) 1 _col0 (type: bigint) outputColumnNames: _col1, _col3 Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col1 (type: varchar(50)), _col3 (type: varchar(128)) outputColumnNames: _col0, _col1 Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

    Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink

    2. - 过滤条件写在where中
    3. ```sql
    4. explain select
    5. a.city_name,b.name from crisps_ods.ods_crisps_mch_address a 
    6. left join ( select * from crisps_ods.ods_crisps_mch_detail where p_date = '2022-03-31') b
    7. on a.mch_detail_id = b.id ;
    10. STAGE DEPENDENCIES:
    11.   Stage-4 is a root stage , consists of Stage-5, Stage-1
    12.   Stage-5 has a backup stage: Stage-1
    13.   Stage-3 depends on stages: Stage-5
    14.   Stage-1
    15.   Stage-0 depends on stages: Stage-3, Stage-1
    17. STAGE PLANS:
    18.   Stage: Stage-4
    19.     Conditional Operator
    21.   Stage: Stage-5
    22.     Map Reduce Local Work
    23.       Alias -> Map Local Tables:
    24.         $hdt$_1:ods_crisps_mch_detail 
    25.           Fetch Operator
    26.             limit: -1
    27.       Alias -> Map Local Operator Tree:
    28.         $hdt$_1:ods_crisps_mch_detail 
    29.           TableScan
    30.             alias: ods_crisps_mch_detail
    31.             Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    32.             Filter Operator
    33.               predicate: (p_date = '2022-03-31') (type: boolean)
    34.               Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    35.               Select Operator
    36.                 expressions: id (type: bigint), name (type: varchar(128))
    37.                 outputColumnNames: _col0, _col1
    38.                 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    39.                 HashTable Sink Operator
    40.                   keys:
    41.                     0 _col0 (type: bigint)
    42.                     1 _col0 (type: bigint)
    44.   Stage: Stage-3
    45.     Map Reduce
    46.       Map Operator Tree:
    47.           TableScan
    48.             alias: a
    49.             Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE
    50.             Select Operator
    51.               expressions: mch_detail_id (type: bigint), city_name (type: varchar(50))
    52.               outputColumnNames: _col0, _col1
    53.               Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE
    54.               Map Join Operator
    55.                 condition map:
    56.                      Left Outer Join0 to 1
    57.                 keys:
    58.                   0 _col0 (type: bigint)
    59.                   1 _col0 (type: bigint)
    60.                 outputColumnNames: _col1, _col3
    61.                 Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    62.                 Select Operator
    63.                   expressions: _col1 (type: varchar(50)), _col3 (type: varchar(128))
    64.                   outputColumnNames: _col0, _col1
    65.                   Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    66.                   File Output Operator
    67.                     compressed: false
    68.                     Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    69.                     table:
    70.                         input format: org.apache.hadoop.mapred.SequenceFileInputFormat
    71.                         output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
    72.                         serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    73.       Local Work:
    74.         Map Reduce Local Work
    76.   Stage: Stage-1
    77.     Map Reduce
    78.       Map Operator Tree:
    79.           TableScan
    80.             alias: a
    81.             Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE
    82.             Select Operator
    83.               expressions: mch_detail_id (type: bigint), city_name (type: varchar(50))
    84.               outputColumnNames: _col0, _col1
    85.               Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE
    86.               Reduce Output Operator
    87.                 key expressions: _col0 (type: bigint)
    88.                 sort order: +
    89.                 Map-reduce partition columns: _col0 (type: bigint)
    90.                 Statistics: Num rows: 22040 Data size: 1278351 Basic stats: COMPLETE Column stats: NONE
    91.                 value expressions: _col1 (type: varchar(50))
    92.           TableScan
    93.             alias: ods_crisps_mch_detail
    94.             Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    95.             Filter Operator
    96.               predicate: (p_date = '2022-03-31') (type: boolean)
    97.               Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    98.               Select Operator
    99.                 expressions: id (type: bigint), name (type: varchar(128))
    100.                 outputColumnNames: _col0, _col1
    101.                 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    102.                 Reduce Output Operator
    103.                   key expressions: _col0 (type: bigint)
    104.                   sort order: +
    105.                   Map-reduce partition columns: _col0 (type: bigint)
    106.                   Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
    107.                   value expressions: _col1 (type: varchar(128))
    108.       Reduce Operator Tree:
    109.         Join Operator
    110.           condition map:
    111.                Left Outer Join0 to 1
    112.           keys:
    113.             0 _col0 (type: bigint)
    114.             1 _col0 (type: bigint)
    115.           outputColumnNames: _col1, _col3
    116.           Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    117.           Select Operator
    118.             expressions: _col1 (type: varchar(50)), _col3 (type: varchar(128))
    119.             outputColumnNames: _col0, _col1
    120.             Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    121.             File Output Operator
    122.               compressed: false
    123.               Statistics: Num rows: 24244 Data size: 1406186 Basic stats: COMPLETE Column stats: NONE
    124.               table:
    125.                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat
    126.                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
    127.                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    129.   Stage: Stage-0
    130.     Fetch Operator
    131.       limit: -1
    132.       Processor Tree:
    133.         ListSink