left join on 和 where 的使用

筛选条件放在left join on后面和放在where后面的结果不一样的。
记住on后面是连接条件，where后面是筛选条件就OK啦！
即：on后面的条件筛选参与连接的记录，主表中的记录会全部出现结果集中，而只有符合on条件的从表记录才会出现在结果集中。
SELECT A.ORDER_ID, B.TRANS_TYPE
  FROM    GW_GATEWAY_ORDER A
       LEFT JOIN
          SYS_MCHT_SETTLE_DETAIL B
       ON A.ORDER_ID = B.ORDER_ID
WHERE A.ORDER_ID >= 20101209150044021 AND A.ORDER_ID <= 20101210150048099;
结果：


ORDER_ID * TRANS_TYPE
20101209150044021 1
20101209150044021 2
20101210141359000 1
20101210144447001




SELECT A.ORDER_ID, B.TRANS_TYPE
  FROM    GW_GATEWAY_ORDER A
       LEFT JOIN
          SYS_MCHT_SETTLE_DETAIL B
       ON A.ORDER_ID = B.ORDER_ID AND B.trans_type = 1
WHERE A.ORDER_ID >= 20101209150044021 AND A.ORDER_ID <= 20101210150048099;
结果：




ORDER_ID * TRANS_TYPE
20101209150044021 1
20101210141359000 1
20101210144447001





SELECT A.ORDER_ID, B.TRANS_TYPE
  FROM    GW_GATEWAY_ORDER A
       LEFT JOIN
          SYS_MCHT_SETTLE_DETAIL B
       ON A.ORDER_ID = B.ORDER_ID
WHERE A.ORDER_ID >= 20101209150044021
   AND A.ORDER_ID <= 20101210150048099
   AND B.trans_type = 1;
结果：


ORDER_ID * TRANS_TYPE
20101209150044021 1
20101210141359000 1