SQL語句中JOIN的用法場景分析
記錄：256
寫SQL最高境界：SELECT * FROM 表名。當(dāng)然這是一句自嘲。探究一下SQL語句中JOIN的用法，直到經(jīng)歷這個(gè)場景，變得想驗(yàn)證一下究竟。
一、場景
把關(guān)系型數(shù)據(jù)庫A中表TEST_TB01和TEST_TB02遷移到大數(shù)據(jù)平臺(tái)M（MaxCompute大數(shù)據(jù)平臺(tái)）。TEST_TB01單表1000萬條記錄，TEST_TB02單表80萬條記錄。
在關(guān)系型數(shù)據(jù)庫中，TEST_TB01和TEST_TB02中有主鍵約束。在產(chǎn)生新增業(yè)務(wù)數(shù)據(jù)時(shí)，不會(huì)存在重復(fù)數(shù)據(jù)插入。但是，當(dāng)數(shù)據(jù)遷移到大數(shù)據(jù)平臺(tái)后，由于在大數(shù)據(jù)平臺(tái)中無主鍵約束功能。在產(chǎn)生新增業(yè)務(wù)數(shù)據(jù)時(shí)，TEST_TB01和TEST_TB02均均插入了重復(fù)數(shù)據(jù)。
在一個(gè)計(jì)算任務(wù)中，TEST_TB01和TEST_TB02根據(jù)某個(gè)字段JOIN連接，計(jì)算出了一份結(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ù)，共計(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驗(yàn)證。
在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連接，只返回兩個(gè)表（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連接，只返回兩個(gè)表（TEST_TB01和TEST_TB02）中連接字段相等的記錄。INNER JOIN和JOIN效果等價(jià)。
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等價(jià)。
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連接，外連接，返回兩個(gè)表中的行：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驗(yàn)證。
在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連接，只返回兩個(gè)表（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連接，只返回兩個(gè)表（TEST_TB01和TEST_TB02）中連接字段相等的記錄。INNER JOIN和JOIN效果等價(jià)。
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等價(jià)。
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連接，外連接，返回兩個(gè)表中的行：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é)果集是笛卡爾積，第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(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ù)量大場景，推薦使用第一種方式，先逐個(gè)表去重再使用JOIN連接。
七、關(guān)系型數(shù)據(jù)庫驗(yàn)證表結(jié)構(gòu)
本例是在DataWorks環(huán)境（即MaxCompute大數(shù)據(jù)平臺(tái)）下驗(yàn)證，即在關(guān)系型數(shù)據(jù)庫驗(yàn)證除表結(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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
SQL Server中檢查字段的值是否為數(shù)字的方法
這篇文章主要介紹了SQL Server中檢查字段的值是否為數(shù)字的方法,使用ISNUMERIC函數(shù)實(shí)現(xiàn),需要的朋友可以參考下2014-06-06
本文主要介紹了VS自帶的SQL server修改密碼并連接使用，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-04-04
SQL Server中數(shù)學(xué)函數(shù)的用法
這篇文章介紹了SQL Server中數(shù)學(xué)函數(shù)的用法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
SQL Server 日期和時(shí)間的內(nèi)部存儲(chǔ)過程
在SQL Server的內(nèi)部存儲(chǔ)中，日期和時(shí)間不是以字符串的形式存儲(chǔ)的，而是使用整數(shù)來存儲(chǔ)的。這篇文章主要介紹了SQL Server 日期和時(shí)間的內(nèi)部存儲(chǔ),需要的朋友可以參考下2019-12-12
sql函數(shù)實(shí)現(xiàn)去除字符串中的相同的字符串
去除字符串中的相同的字符，此功能在開發(fā)過程中很實(shí)用，為此本文整理了一些，希望對你了解它有所幫助2013-01-01
Sql Server中判斷表、列不存在則創(chuàng)建的方法
這篇文章主要給大家分享了Sql Server中判斷表、列是否存在，如果不存在則創(chuàng)建的方法，文章先給大家簡單介紹了Sql Server中判斷表中某列是否存在的方法，文中給出了詳細(xì)的示例代碼，需要的朋友們可以參考借鑒，下面來一起看看吧。2017-03-03
解析SQL?Server?CDC配合Kafka?Connect監(jiān)聽數(shù)據(jù)變化的問題
這篇文章主要介紹了SQL?Server?CDC配合Kafka?Connect監(jiān)聽數(shù)據(jù)變化,除了數(shù)據(jù)庫開啟CDC支持以外，主要還是要將變更的數(shù)據(jù)通過Kafka?Connect傳輸數(shù)據(jù)，Debezium是目前官方推薦的連接器，本文給大家分享實(shí)現(xiàn)步驟，感興趣的朋友跟隨小編一起看看吧2021-12-12
一次性壓縮Sqlserver2005中所有庫日志的存儲(chǔ)過程
通過下面這個(gè)可以執(zhí)行單個(gè)數(shù)據(jù)庫日志壓縮，問題每次都要寫數(shù)據(jù)庫名字，日志大的話執(zhí)行速度沒有那么快2012-01-01
本文主要介紹了SQL Server里書簽查找的性能傷害，具有很好的參考價(jià)值。下面跟著小編一起來看下吧2017-03-03
本文將為大家介紹如何防止SQL注入，并通過腳本來過濾SQL中注入的字符。解決SQL注入安全的問題解決，希望對大家有所幫助。2016-07-07