oracle基礎積累-join,left join,right join,inner join,full join,left outer join,right outer join 之間的差別

場景:

        基礎知識查詢.

環境:

        Oracle Database 11g; PL/SQL Developer

1.表資料

1.1 傳感器采集區域 sensor_collect_region

1.2 傳感器采集資料 sensor_collect_data

2. join

        兩張表根據on條件進行連接配接查詢,查詢出兩張表完全比對的結果集.

select *
  from sensor_collect_data aa
  join sensor_collect_region bb
    on aa.region = bb.region
           

3. left join (左連接配接)

    兩張表根據on條件進行左連接配接查詢,會傳回left join左邊表所有行,即使右表沒有比對記錄.

select *
  from sensor_collect_data aa
  left join sensor_collect_region bb
    on aa.region = bb.region
           

4. right join (右連接配接)

    兩張表根據on條件進行右連接配接查詢,會傳回right join右邊表所有行,即使左表沒有比對記錄.

select *
  from sensor_collect_data aa
 right join sensor_collect_region bb
    on aa.region = bb.region
           

5. inner join (内連接配接)

    兩張表根據on條件進行内連接配接查詢,查詢出兩張表完全比對的結果集.

select *
  from sensor_collect_data aa
 inner join sensor_collect_region bb
    on aa.region = bb.region
           

6. full join (全連接配接)

    兩張表根據on條件進行連接配接查詢,傳回左表和右表中所有記錄,包括沒有比對的記錄.

select *
  from sensor_collect_data aa
  full join sensor_collect_region bb
    on aa.region = bb.region
           

7. left outer join

   與left join相同

select *
  from sensor_collect_data aa
  left outer join sensor_collect_region bb
    on aa.region = bb.region
           

8. right outer join

   right join 相同

select *
  from sensor_collect_data aa
 right outer join sensor_collect_region bb
    on aa.region = bb.region
           

9. 左連接配接和右連接配接進行union操作

select *
  from sensor_collect_data aa
  left join sensor_collect_region bb
    on aa.region = bb.region
union
select *
  from sensor_collect_data aa
 right join sensor_collect_region bb
    on aa.region = bb.region	
           

10. 結論

        join和inner join等價

        left join和left outer join等價

        right join和right outer join等價

        left join與right join進行union操作和full join等價    

11.附建表語句

11.1 建表語句一

create table SENSOR_COLLECT_DATA
(
  sensor_id NUMBER(16) not null,
  region    VARCHAR2(16) not null,
  s1        NUMBER(6,3),
  s2        NUMBER(6,3),
  s3        NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA
  is '傳感器采集資料';
comment on column SENSOR_COLLECT_DATA.sensor_id
  is '資料id實體唯一辨別';
comment on column SENSOR_COLLECT_DATA.region
  is '傳感器安裝區域';
comment on column SENSOR_COLLECT_DATA.s1
  is '傳感器采集的值1';
comment on column SENSOR_COLLECT_DATA.s2
  is '傳感器采集的值2';
comment on column SENSOR_COLLECT_DATA.s3
  is '傳感器采集的值3';
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191502, '2019', 12, 33, 55);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191514, '2018', 22, 33, 5);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191513, '2018', 2, 3, 66);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191515, '2028', 8, 56, 77);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191517, '2017', 22, 33, 33);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191516, '2017', 22.22, 33.33, 55.33);
insert into SENSOR_COLLECT_DATA (sensor_id, region, s1, s2, s3)
values (20191501, '2019', 12.88, 888.55, 3.22);
commit;
           

11.2 建表語句二

create table sensor_collect_region
(
  region         VARCHAR2(16) not null,
  region_name    VARCHAR2(128),
  region_type    VARCHAR2(128)
);
comment on table sensor_collect_region
  is '傳感器采集區域';
comment on column sensor_collect_region.region
  is '傳感器安裝區域,實體唯一辨別';
comment on column sensor_collect_region.region_name
  is '名稱';
comment on column sensor_collect_region.region_type
  is '類型';
insert into SENSOR_COLLECT_REGION (region, region_name, region_type)
values ('2017', '江蘇', '1');
insert into SENSOR_COLLECT_REGION (region, region_name, region_type)
values ('2018', '福建', '2');
insert into SENSOR_COLLECT_REGION (region, region_name, region_type)
values ('2019', '浙江', '1');
insert into SENSOR_COLLECT_REGION (region, region_name, region_type)
values ('2016', '黑龍江', '3');
commit;  
           

以上,感謝.