天下高月小说,好看的课外书,已完结小说排行榜

主頁 > 知識庫 > SQL語句中JOIN的用法場景分析

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

記錄：256

寫SQL最高境界：SELECT * FROM 表名。當(dāng)然這是一句自嘲。探究一下SQL語句中JOIN的用法，直到經(jīng)歷這個場景，變得想驗證一下究竟。

一、場景

把關(guān)系型數(shù)據(jù)庫A中表TEST_TB01和TEST_TB02遷移到大數(shù)據(jù)平臺M（MaxCompute大數(shù)據(jù)平臺）。TEST_TB01單表1000萬條記錄，TEST_TB02單表80萬條記錄。

在關(guān)系型數(shù)據(jù)庫中，TEST_TB01和TEST_TB02中有主鍵約束。在產(chǎn)生新增業(yè)務(wù)數(shù)據(jù)時，不會存在重復(fù)數(shù)據(jù)插入。但是，當(dāng)數(shù)據(jù)遷移到大數(shù)據(jù)平臺后，由于在大數(shù)據(jù)平臺中無主鍵約束功能。在產(chǎn)生新增業(yè)務(wù)數(shù)據(jù)時，TEST_TB01和TEST_TB02均均插入了重復(fù)數(shù)據(jù)。

在一個計算任務(wù)中，TEST_TB01和TEST_TB02根據(jù)某個字段JOIN連接，計算出了一份結(jié)果數(shù)據(jù)，數(shù)據(jù)推送到使用方的關(guān)系型數(shù)據(jù)庫C。直接導(dǎo)致了C數(shù)據(jù)庫的對應(yīng)表的表空間撐爆，監(jiān)控預(yù)警。

原因：TEST_TB01和TEST_TB02有重復(fù)數(shù)據(jù)，使用JOIN連接后，生成了10億+條數(shù)據(jù)，共計200G+數(shù)據(jù)，直接推送到C數(shù)據(jù)庫。

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

二、建表

TEST_TB01建表語句：

create table TEST_TB01
(
  sensor_id   BIGINT,
  part_id     BIGINT
 )
COMMENT '數(shù)據(jù)表一';

TEST_TB02建表語句：

create table TEST_TB02
(
  part_id    BIGINT,
  elem_id    BIGINT
 )
 COMMENT '數(shù)據(jù)表二';

三、SQL語句中使用JOIN無重復(fù)數(shù)據(jù)情況

在SQL語句中使用JOIN無重復(fù)數(shù)據(jù)情況，即在TEST_TB01和TEST_TB02表中均無重復(fù)數(shù)據(jù)情況。分別使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN驗證。

在TEST_TB01插入數(shù)據(jù)：

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插入數(shù)據(jù)：

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數(shù)據(jù)：

查看TEST_TB02數(shù)據(jù)：

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

4.在SQL中使用LEFT OUTER JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

四、SQL語句中使用JOIN有重復(fù)數(shù)據(jù)情況

在SQL語句中使用JOIN有重復(fù)數(shù)據(jù)情況，即在TEST_TB01和TEST_TB02表中均有重復(fù)數(shù)據(jù)情況。分別使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN驗證。

在TEST_TB01插入數(shù)據(jù)：

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);
--造重復(fù)數(shù)據(jù)
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);

在TEST_TB02插入數(shù)據(jù)：

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);
--造重復(fù)數(shù)據(jù)
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);

查看TEST_TB01數(shù)據(jù)：

查看TEST_TB02數(shù)據(jù)：

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

4.在SQL中使用LEFT OUTER JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根據(jù)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;

執(zhí)行結(jié)果：

五、SQL中使用JOIN有重復(fù)與無重復(fù)數(shù)據(jù)區(qū)別

在SQL語句中使用JOIN有重復(fù)數(shù)據(jù)情況，使用JOIN連接，符合連接字段相等的記錄的結(jié)果集是笛卡爾積，第一個表的行數(shù)乘以第二個表的行數(shù)。

六、解決方式

1.先去重再使用JOIN連接

根據(jù)業(yè)務(wù)規(guī)則先對TEST_TB01和TEST_TB02分別去重再使用JOIN連接。

2.先使用JOIN連接再去重

根據(jù)業(yè)務(wù)規(guī)則先對TEST_TB01和TEST_TB02使用JOIN連接生成結(jié)果集，再對結(jié)果集去重。

3.建議

在生產(chǎn)環(huán)境特別是數(shù)據(jù)量大場景，推薦使用第一種方式，先逐個表去重再使用JOIN連接。

七、關(guān)系型數(shù)據(jù)庫驗證表結(jié)構(gòu)

本例是在DataWorks環(huán)境（即MaxCompute大數(shù)據(jù)平臺）下驗證，即在關(guān)系型數(shù)據(jù)庫驗證除表結(jié)構(gòu)差異，其它均相同。

在ORACLE數(shù)據(jù)庫建表語句：

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數(shù)據(jù)庫建表語句：

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

以上，感謝。

到此這篇關(guān)于SQL語句中JOIN的用法的文章就介紹到這了,更多相關(guān)SQL JOIN的用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

淺談mysql join底層原理
MYSQL數(shù)據(jù)庫基礎(chǔ)之Join操作原理
解決Mysql的left join無效及使用的注意事項說明
mysql left join快速轉(zhuǎn)inner join的過程
為什么代碼規(guī)范要求SQL語句不要過多的join
mysql高效查詢left join和group by(加索引)
MySQL的join buffer原理
SQL之各種join小結(jié)詳細講解

標(biāo)簽：泰安廣東濟源武威汕頭濟寧安徽臺州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL語句中JOIN的用法場景分析》，本文關(guān)鍵詞 SQL,語句,中,JOIN,的,用法,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。