欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

left join on 和 where 的使用 博客分类: sqlsql mysql oracle lefton 

程序员文章站 2024-03-20 09:07:22
...
筛选条件放在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



相关标签: left on