首頁 > 軟體

SQL語句中JOIN的用法場景分析

2021-07-22 13:01:27

記錄:256

寫SQL最高境界:SELECT * FROM 表名。當然這是一句自嘲。探究一下SQL語句中JOIN的用法,直到經歷這個場景,變得想驗證一下究竟。

一、場景

把關係型資料庫A中表TEST_TB01和TEST_TB02遷移到巨量資料平臺M(MaxCompute巨量資料平臺)。TEST_TB01單表1000萬條記錄,TEST_TB02單表80萬條記錄。

在關係型資料庫中,TEST_TB01和TEST_TB02中有主鍵約束。在產生新增業務資料時,不會存在重複資料插入。但是,當資料遷移到巨量資料平臺後,由於在巨量資料平臺中無主鍵約束功能。在產生新增業務資料時,TEST_TB01和TEST_TB02均均插入了重複資料。

在一個計算任務中,TEST_TB01和TEST_TB02根據某個欄位JOIN連線,計算出了一份結果資料,資料推播到使用方的關係型資料庫C。直接導致了C資料庫的對應表的表空間撐爆,監控預警。

原因:TEST_TB01和TEST_TB02有重複資料,使用JOIN連線後,生成了10億+條資料,共計200G+資料,直接推播到C資料庫。

那次考慮不周,瞬間懵了,感覺SQL語句中的JOIN變得陌生極了。於是想探究一下以作記錄。

二、建表

TEST_TB01建表語句:

create table TEST_TB01
(
  sensor_id   BIGINT,
  part_id     BIGINT
 )
COMMENT '資料表一';

TEST_TB02建表語句:

create table TEST_TB02
(
  part_id    BIGINT,
  elem_id    BIGINT
 )
 COMMENT '資料表二';

三、SQL語句中使用JOIN無重複資料情況

在SQL語句中使用JOIN無重複資料情況,即在TEST_TB01和TEST_TB02表中均無重複資料情況。分別使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN驗證。

在TEST_TB01插入資料:

insert into TEST_TB01 (sensor_id,part_id) values(2101,9911);
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);
insert into TEST_TB01 (sensor_id,part_id) values(2104,9914);
insert into TEST_TB01 (sensor_id,part_id) values(2105,9915);

在TEST_TB02插入資料:

insert into TEST_TB02 (part_id,elem_id) values(9911,8901);
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);
insert into TEST_TB02 (part_id,elem_id) values(9916,8906);

檢視TEST_TB01資料:

檢視TEST_TB02資料:

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根據part_id使用JOIN連線,只返回兩個表(TEST_TB01和TEST_TB02)中連線欄位相等的記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根據part_id使用INNER JOIN連線,只返回兩個表(TEST_TB01和TEST_TB02)中連線欄位相等的記錄。INNER JOIN和JOIN效果等價。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
INNER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根據part_id使用LEFT JOIN連線,左連線,返回左表(TEST_TB01)中所有的記錄以及右表(TEST_TB02)中連線欄位相等的記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
LEFT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

4.在SQL中使用LEFT OUTER JOIN

TEST_TB01和TEST_TB02根據part_id使用LEFT OUTER JOIN連線,左外連線,返回左表(TEST_TB01)中所有的記錄以及右表(TEST_TB02)中連線欄位相等的記錄。LEFT OUTER JOIN

和LEFT JOIN等價。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
LEFT OUTER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根據part_id使用RIGHT JOIN連線,右連線,返回右表(TEST_TB02)中所有的記錄以及左表(TEST_TB01)中連線欄位相等的記錄

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
RIGHT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根據part_id使用FULL JOIN連線,外連線,返回兩個表中的行:LEFT JOIN + RIGHT JOIN所有行記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
FULL JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

四、SQL語句中使用JOIN有重複資料情況

在SQL語句中使用JOIN有重複資料情況,即在TEST_TB01和TEST_TB02表中均有重複資料情況。分別使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN驗證。

在TEST_TB01插入資料:

insert into TEST_TB01 (sensor_id,part_id) values(2101,9911);
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);
insert into TEST_TB01 (sensor_id,part_id) values(2104,9914);
insert into TEST_TB01 (sensor_id,part_id) values(2105,9915);
--造重複資料
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);

在TEST_TB02插入資料:

insert into TEST_TB02 (part_id,elem_id) values(9911,8901);
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);
insert into TEST_TB02 (part_id,elem_id) values(9916,8906);
--造重複資料
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);

檢視TEST_TB01資料:

檢視TEST_TB02資料:

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根據part_id使用JOIN連線,只返回兩個表(TEST_TB01和TEST_TB02)中連線欄位相等的記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根據part_id使用INNER JOIN連線,只返回兩個表(TEST_TB01和TEST_TB02)中連線欄位相等的記錄。INNER JOIN和JOIN效果等價。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
INNER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根據part_id使用LEFT JOIN連線,左連線,返回左表(TEST_TB01)中所有的記錄以及右表(TEST_TB02)中連線欄位相等的記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
LEFT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

4.在SQL中使用LEFT OUTER JOIN

TEST_TB01和TEST_TB02根據part_id使用LEFT OUTER JOIN連線,左外連線,返回左表(TEST_TB01)中所有的記錄以及右表(TEST_TB02)中連線欄位相等的記錄。LEFT OUTER JOIN

和LEFT JOIN等價。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
LEFT OUTER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根據part_id使用RIGHT JOIN連線,右連線,返回右表(TEST_TB02)中所有的記錄以及左表(TEST_TB01)中連線欄位相等的記錄

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
RIGHT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根據part_id使用FULL JOIN連線,外連線,返回兩個表中的行:LEFT JOIN + RIGHT JOIN所有行記錄。

SQL語句:

SELECT
  *
FROM
  TEST_TB01 aa
FULL JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

執行結果:

五、SQL中使用JOIN有重複與無重複資料區別

在SQL語句中使用JOIN有重複資料情況,使用JOIN連線,符合連線欄位相等的記錄的結果集是笛卡爾積,第一個表的行數乘以第二個表的行數。

六、解決方式

1.先去重再使用JOIN連線

根據業務規則先對TEST_TB01和TEST_TB02分別去重再使用JOIN連線。

2.先使用JOIN連線再去重

根據業務規則先對TEST_TB01和TEST_TB02使用JOIN連線生成結果集,再對結果集去重。

3.建議

在生產環境特別是資料量大場景,推薦使用第一種方式,先逐個表去重再使用JOIN連線。

七、關係型資料庫驗證表結構

本例是在DataWorks環境(即MaxCompute巨量資料平臺)下驗證,即在關係型資料庫驗證除表結構差異,其它均相同。

在ORACLE資料庫建表語句:

create table TEST_TB01
(
  sensor_id  NUMBER(16),
  part_id  NUMBER(16)
 );
 
 create table TEST_TB02
(
  part_id  NUMBER(16),
  elem_id  NUMBER(16) 
 );

在MySQL資料庫建表語句:

CREATE TABLE TEST_TB01
(
  sensor_id  BIGINT,
  part_id  BIGINT
 );
 
 CREATE TABLE TEST_TB02
(
  part_id  BIGINT,
  elem_id  BIGINT 
 );

以上,感謝。

到此這篇關於SQL語句中JOIN的用法的文章就介紹到這了,更多相關SQL JOIN的用法內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com