SQL語句中JOIN的用法場景分析
記錄：256
寫SQL最高境界：SELECT * FROM 表名。當然這是一句自嘲。探究一下SQL語句中JOIN的用法，直到經歷這個場景，變得想驗證一下究竟。
一、場景
把關系型數據庫A中表TEST_TB01和TEST_TB02遷移到大數據平臺M（MaxCompute大數據平臺）。TEST_TB01單表1000萬條記錄，TEST_TB02單表80萬條記錄。
在關系型數據庫中，TEST_TB01和TEST_TB02中有主鍵約束。在產生新增業(yè)務數據時，不會存在重復數據插入。但是，當數據遷移到大數據平臺后，由于在大數據平臺中無主鍵約束功能。在產生新增業(yè)務數據時，TEST_TB01和TEST_TB02均均插入了重復數據。
在一個計算任務中，TEST_TB01和TEST_TB02根據某個字段JOIN連接，計算出了一份結果數據，數據推送到使用方的關系型數據庫C。直接導致了C數據庫的對應表的表空間撐爆，監(jiān)控預警。
原因：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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
四、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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
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;
執(zhí)行結果：
五、SQL中使用JOIN有重復與無重復數據區(qū)別
在SQL語句中使用JOIN有重復數據情況，使用JOIN連接，符合連接字段相等的記錄的結果集是笛卡爾積，第一個表的行數乘以第二個表的行數。
六、解決方式
1.先去重再使用JOIN連接
根據業(yè)務規(guī)則先對TEST_TB01和TEST_TB02分別去重再使用JOIN連接。
2.先使用JOIN連接再去重
根據業(yè)務規(guī)則先對TEST_TB01和TEST_TB02使用JOIN連接生成結果集，再對結果集去重。
3.建議
在生產環(huán)境特別是數據量大場景，推薦使用第一種方式，先逐個表去重再使用JOIN連接。
七、關系型數據庫驗證表結構
本例是在DataWorks環(huán)境（即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的用法內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
SQL Server 的全文搜索(Full-Text Search）是基于分詞的文本檢索功能，依賴于全文索引。下面通過本文給大家介紹SQL Server 全文搜索功能介紹，需要的朋友參考下吧2017-12-12
今天同學向我提了一個問題，我覺得蠻有意思，現記錄下來大家探討下。問題是：在一個表里面，有一個允許為空的字段，空是可以重復的，但是不為空的值需要唯一。2011-03-03
通過系統(tǒng)數據庫獲取用戶所有數據庫中的視圖、表、存儲過程
本文主要講了通過系統(tǒng)數據庫獲取用戶所有數據庫中的視圖、表、存儲過程的方法，大家參考使用吧2014-04-04
本文給大家分享幾個比較好用的sql語句，在數據庫中經常會用到，需要的朋友可以參考下本篇文章。2015-09-09
SqlServer編寫數據庫表的操作方式(建庫、建表、修改語句)
這篇文章主要介紹了SqlServer編寫數據庫表的操作方式(建庫、建表、修改語句)的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-09-09
SQL Server 排序函數 ROW_NUMBER和RANK 用法總結
下面的例子和SQL語句均在SQL Server 2008環(huán)境下運行通過，使用SQL Server自帶的AdventureWorks數據庫。2009-07-07
今天寫MySQL時遇到使用表別名的問題，這里重新總結一下,需要的朋友可以參考下2017-02-02
本文主要介紹了SQL Server2017使用IP作為服務器名連接服務器，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-09-09
死鎖（deadlock）指進程之間互相永久阻塞的狀態(tài)，SQL可以檢測到死鎖，并選擇終止其中一個事務以干預死鎖狀態(tài)。2010-08-08
這篇文章主要介紹了SQL?Server?DATEDIFF()?函數的定義和用法,通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-12-12