2014/12/22 / 譛邨よ峩譁ｰ譌･譎 : 2019/09/08 邀ｳ濶ｯ螟ｪ SQL

SQL DMLｼ夂ｵ仙粋(JOIN)

讎りｦ/邨仙粋譁ｹ豕

窶ｻ隍謨ｰ縺ｮ繝繝ｼ繝悶Ν繧偵￥縺｣縺､縺代ｋ縲
縺昴ｮ荳翫〒SELECT縺励◆繧翫☆繧九
諠蝣ｱ蜃ｦ逅雉譬ｼ逧縺ｫ險縺縺ｨ縲檎ｵ仙粋縲

(1)繝繝ｼ繝悶Ν繧 , 縺ｧ蛹ｺ蛻繧翫仝HERE蜿･縺ｧ邨槭ｋ
SELECT * FROM 陦ｨ1, 陦ｨ2
WHERE 陦ｨ1.蛻 = 陦ｨ2.蛻 AND ｽ
窶ｻWHERE蜿･辟｡縺励ｮ蝣ｴ蜷茨ｼ壼ｨ邨縺ｿ蜷医ｏ縺幄｡ｨ遉ｺ
(縲蜊倡ｴ皮ｵ仙粋縲榊盾辣ｧ)

(2)JOIN蜿･縺ｧ邨仙粋譚｡莉ｶ繧呈欠螳
SELECT * FROM 繝繝ｼ繝悶Ν1
JOIN 繝繝ｼ繝悶Ν2 ON 繝繝ｼ繝悶Ν1.蛻暦ｼ = 繝繝ｼ繝悶Ν2.蛻暦ｼ

遲我ｾ｡邨仙粋_髱樒ｭ我ｾ｡邨仙粋

遲我ｾ｡邨仙粋
閾ｪ辟ｶ邨仙粋縲＾N蜿･縲ゞSING蜿･繧剃ｽｿ逕ｨ縺励◆邨仙粋譎ゅｮ譚｡莉ｶ縺ｫ縲=縲阪ｒ逕ｨ縺縺ｦ蛻玲欠螳壹ｒ陦後≧邨仙粋縲

髱樒ｭ我ｾ｡邨仙粋
ON蜿･繧剃ｽｿ逕ｨ縺励◆邨仙粋譎ゅｮ譚｡莉ｶ縺ｫ縲=縲阪ｒ逕ｨ縺縺ｪ縺蛻玲欠螳壹ｒ陦後≧邨仙粋縲
BETWEEN縲！N縲´IKE遲峨ｮWHERE蜿･縺ｫ逕ｨ縺繧峨ｌ繧区､懃ｴ｢譚｡莉ｶ繧貞茜逕ｨ縺ｧ縺阪ｋ縲
迚ｹ螳壹ｮ遽蝗ｲ縺ｧ譚｡莉ｶ繧呈欠螳壹☆繧狗ｵ仙粋譁ｹ豕
邨ｦ荳弱ユ繝ｼ繝悶Ν
豌丞錐 邨ｦ荳 邏
莨願陸 180,000 ?
逕ｰ荳ｭ 195,000 ?
逋ｽ莠 260,000 ?
遲臥ｴ壹ユ繝ｼ繝悶Ν
邏 譛菴 譛鬮
3 150,001 200,000
4 200,001 300,000
5 300,001 400,000
SELECT *
FROM 邨ｦ荳 JOIN 遲臥ｴ
ON 邏 BETWEEN 譛菴 AND 譛鬮
豌丞錐 邨ｦ荳 遲臥ｴ
莨願陸 180,000 3
逕ｰ荳ｭ 195,000 4
逋ｽ莠 260,000 4

蜀驛ｨ邨仙粋

邨仙粋蟇ｾ雎｡
蛻怜錐		 邨仙粋蟇ｾ雎｡
蛻怜梛		 隍謨ｰ蛻 邨仙粋蛻励ｮ
SELECT		 繝｡繝ｪ繝繝
NATURAL
JOIN		 蛻･蜷咲ｧｰﾃ 蛻･蝙凝
(證鈴ｻ吶ｮ蝙句､画鋤)		 蠑ｷ蛻ｶAND譚｡莉ｶ 蛻嶺ｿｮ鬟ｾﾃ 險倩ｿｰ縺梧･ｽ
JOIN
USING		 蛻･蜷咲ｧｰﾃ 蛻･蝙凝
(證鈴ｻ吶ｮ蝙句､画鋤)		 譚｡莉ｶ謖螳壺雷 蛻嶺ｿｮ鬟ｾﾃ 陦ｨ遉ｺ蛻励ｒ貂帙ｉ縺帙ｋ
JOIN
ON		 蛻･蜷咲ｧｰ笳 蛻･蝙銀無
(譏守､ｺ逧蝙句､画鋤)		 譚｡莉ｶ謖螳壺雷 蛻嶺ｿｮ鬟ｾ笳 豁｣遒ｺ

INNER JOIN

邨仙粋蟇ｾ雎｡縺ｮ陦ｨ(繝繝ｼ繝悶Ν)縲∽ｸ｡譁ｹ縺ｫ蜈ｱ騾壹☆繧九Ξ繧ｳ繝ｼ繝縺ｮ縺ｿ繧堤ｵ仙粋繝ｻ謚ｽ蜃ｺ縺吶ｋ邨仙粋譁ｹ豕
SELECT 逕溷ｾ.*, 繝繧ｹ繝.*
FROM 逕溷ｾ INNER JOIN 繝繧ｹ繝
FROM 逕溷ｾ JOIN 繝繧ｹ繝縲窶ｻINNER縺ｯ逵∫払蜿ｯ
ON 逕溷ｾ.ID = 繝繧ｹ繝.ID

EMPNO ENAME DEPTNO
1 C# 1
2 C++ 2
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 3
7 PERL 3

竊鯛薙ｒ邨仙粋

DEPTNO DNAME BOSS
1 MicroSoft 1
2 Almighty 2
3 WEB 3

竊 邨先棡

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
4 VB6 1 MicroSoft
3 VB.NET 1 MicroSoft
5 JAVA 2 Almighty
2 C++ 2 Almighty
7 PERL 3 WEB
6 JS 3 WEB
USING蜿･

ｽ FROM (繝繝ｼ繝悶Ν蜷1) JOIN (繝繝ｼ繝悶Ν蜷2)
USING 蛻怜錐

竊薙→蜷後§
ｽ FROM (繝繝ｼ繝悶Ν蜷1) JOIN (繝繝ｼ繝悶Ν蜷2)
ON (繝繝ｼ繝悶Ν蜷1).蛻怜錐 = (繝繝ｼ繝悶Ν蜷2).蛻怜錐

SELECT *
FROM 繝繝ｼ繝悶Ν1 JOIN 繝繝ｼ繝悶Ν2
USING (蛻1)
窶ｻﾃ暦ｼ哢ATURAL JOIN縲笳具ｼ哽OIN

USING蜿･繧剃ｽｿ縺縺ｮ縺ｫ驕ｩ縺励※縺繧句ｴ蜷
(閾ｪ辟ｶ邨仙粋縺ｨ豈碑ｼ縺励※)
繝ｻ邨仙粋縺吶ｋ陦ｨ縺ｫ蜷悟錐縺ｧ蛻･蝙九ｮ蛻励′縺ゅｊ縲∬ｪ辟ｶ邨仙粋縺吶ｋ縺ｨ繧ｨ繝ｩ繝ｼ縺ｫ縺ｪ縺｣縺ｦ縺励∪縺蝣ｴ蜷
縲繧ｨ繝ｩ繝ｼ縺ｨ縺ｪ繧句励ｒ邨仙粋譚｡莉ｶ縺九ｉ螟悶○繧
繝ｻ邨仙粋縺吶ｋ陦ｨ縺ｫ蜷悟錐縺ｧNULL險ｱ螳ｹ縺ｮ蛻励′縺ゅｊ縲∬ｪ辟ｶ邨仙粋縺吶ｋ縺ｨ繧ｨ繝ｩ繝ｼ縺ｫ縺ｪ縺｣縺ｦ縺励∪縺蝣ｴ蜷

DELETE
FROM 陦ｨ1
USING 陦ｨ2
WHERE
縲縲陦ｨ2 ｼ主 = 陦ｨ1 ｼ主 --邨仙粋譚｡莉ｶ
AND ｽ --譚｡莉ｶ莉

螟夜Κ邨仙粋

邨仙粋譚｡莉ｶ縺ｫ荳閾ｴ縺吶ｋ繝繝ｼ繧ｿ縺ｫ蜉縺医※縲∫ｵ仙粋蟇ｾ雎｡縺ｮ陦ｨ(繝繝ｼ繝悶Ν)縲√←縺｡繧峨°迚譁ｹ縺ｫ縺縺代≠繧九Ξ繧ｳ繝ｼ繝峨ｂ謚ｽ蜃ｺ繝ｻ邨仙粋縺吶ｋ邨仙粋譁ｹ豕
縺薙ｮ縺ｩ縺｡繧峨°迚譁ｹ縺ｫ縺縺代≠繧玖｡ｨ縺ｮ謖螳壹↓繧医▲縺ｦ縲
繝ｻLEFT OUTER JOIN
繝ｻRIGHT OUTER JOIN
縺ｮ繧繧頑婿縺後≠繧九

窶ｻ邨仙粋蜑阪ユ繝ｼ繝悶Ν

EMPNO ENAME DEPTNO
1 C# 1
2 C++ (NULL)
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 3
7 PERL 3

DEPTNO DNAME
1 MicroSoft
2 Almighty
3 WEB
4 Assembly
蟾ｦ螟夜Κ邨仙粋

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
2 C++ (NULL) (NULL)
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB

窶ｻ蟾ｦ縺ｮ陦ｨｼ哘MPLOYEE縺ｮC++縺ｫ縺翫¢繧汽EPTNO縺ｯ(NULL)縺ｧ縲
陦ｨｼ咼EPARTMENT縺ｫ隧ｲ蠖薙☆繧九Ξ繧ｳ繝ｼ繝峨ｯ辟｡縺縺後
邨仙粋蠕後ｮ陦ｨ縺ｮDNAME繧(NULL)縺ｨ縺励※陦ｨ遉ｺ縺励※縺繧九
ｼ晏ｷｦ陦ｨ縺ｮ繝ｬ繧ｳ繝ｼ繝峨ｯ蜈ｨ縺ｦ陦ｨ遉ｺ縺輔ｌ縺ｦ縺繧倶ｸ譁ｹ縲∝承陦ｨ縺ｮAssembly縺ｮ繝ｬ繧ｳ繝ｼ繝峨ｯ陦ｨ遉ｺ縺輔ｌ縺ｦ縺縺ｪ縺縲

蜿ｳ螟夜Κ邨仙粋

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB
(NULL) (NULL) 4 Asesembly

窶ｻ蟾ｦ縺ｮ蛻暦ｼ哘MPLOYEE縺ｫ縺翫＞縺ｦ縲
蜿ｳ縺ｮ陦ｨｼ咼EPARTMENT縺ｮAssembly繧奪EPTNO蛻励↓謖√▽繝ｬ繧ｳ繝ｼ繝峨ｯ辟｡縺縺後
邨仙粋蠕後ｮ陦ｨ縺ｫ縺ｯAssembly繝ｬ繧ｳ繝ｼ繝峨′陦ｨ遉ｺ縺輔ｌ縺ｦ縺繧九
ｼ晏承陦ｨ縺ｮ繝ｬ繧ｳ繝ｼ繝峨ｯ蜈ｨ縺ｦ陦ｨ遉ｺ縺輔ｌ縺ｦ縺繧倶ｸ譁ｹ縲
蟾ｦ陦ｨ縺ｮC++縺ｮ繝ｬ繧ｳ繝ｼ繝峨ｯDEPTNO繧呈戟縺溘↑縺轤ｺ縲∬｡ｨ遉ｺ縺輔ｌ縺ｦ縺縺ｪ縺縲

螟夜Κ邨仙粋貍皮ｮ怜ｭ(+)

SELECT ｽ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID;
窶ｻ 竊薙→蜷後§
FROM MYTABLE M INNER JOIN EMPLOYEE E ON M.ID = E.ID;

SELECT ｽ FROM MYTABLE M, EMPLOYEE E WHERE M.ID(+) = E.ID;
窶ｻ 竊薙→蜷後§
FROM MYTABLE M RIGHT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;

SELECT ｽ FROM MYTABLE M, EMPLOYEE E WHERE M.ID = E.ID(+);
窶ｻ 竊薙→蜷後§
FROM MYTABLE M LEFT OUTER JOIN EMPLOYEE E ON M.ID = E.ID;

螳悟ｨ螟夜Κ邨仙粋

繝ｻLEFT OUTER JOIN
繝ｻRIGHT OUTER JOIN
縺ｮ荳｡譁ｹ

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO

EMPNO ENAME DEPTNO DNAME
1 C# 1 MicroSoft
2 C++ (NULL) (NULL)
3 VB.NET 1 MicroSoft
4 VB6 1 MicroSoft
5 JAVA 2 Almighty
6 JS 3 WEB
7 PERL 3 WEB
(NULL) (NULL) 4 Asesembly

蟾ｦ螟夜Κ邨仙粋縺ｧ縺ｯ陦ｨ遉ｺ縺輔ｌ縺ｪ縺九▲縺蘗ssembly縺ｮ繝ｬ繧ｳ繝ｼ繝峨′縲
蜿ｳ螟夜Κ邨仙粋縺ｧ縺ｯ陦ｨ遉ｺ縺輔ｌ縺ｪ縺九▲縺櫃++縺ｮ繝ｬ繧ｳ繝ｼ繝峨′陦ｨ遉ｺ縺輔ｌ繧九

閾ｪ辟ｶ邨仙粋

NATURAL JOIN蜿･
邨仙粋譚｡莉ｶ謖螳夂┌縺励ｮ邨仙粋(譚｡莉ｶ繧呈欠螳壹〒縺阪↑縺)
蛻怜錐縲√ョ繝ｼ繧ｿ蝙九√し繧､繧ｺ縺ｮ蛻励ｒ閾ｪ蜍輔〒謚ｽ蜃ｺ縺励※邨仙粋縺吶ｋ縲
繝ｻ邨仙粋譚｡莉ｶ縺ｫ蜷郁ｴ縺吶ｋ蛻励′辟｡縺蝣ｴ蜷医蜊倡ｴ皮ｵ仙粋縺ｫ縺ｪ繧九
繝ｻ邨仙粋譚｡莉ｶ縺ｫ蜷郁ｴ縺吶ｋ蛻励′1縺､縺ｮ蝣ｴ蜷医蜀驛ｨ邨仙粋縺ｫ縺ｪ繧九
繝ｻ2縺､縲3縺､繝ｻ繝ｻ繝ｻ縺ｨ閾ｪ辟ｶ邨仙粋繧定｡後≧蝣ｴ蜷医蟾ｦ螟夜Κ邨仙粋(LEFT OUTER JOIN)縺ｫ縺ｪ繧九
繝ｻ邨仙粋譚｡莉ｶ蛻励′隍謨ｰ縺ゅｋ蝣ｴ蜷医ｽ LEFT OUTER JOIN ｽ ON ｽ AND ｽ 縺ｨ蜷梧ｧ倥∫ｵ仙粋譚｡莉ｶ縺檎ｵ槭ｉ繧後ｋ縲

SELECT *
FROM 繝繝ｼ繝悶Ν1 NATURAL JOIN 繝繝ｼ繝悶Ν2 NATURAL JOIN 繝繝ｼ繝悶Ν3

閾ｪ蟾ｱ邨仙粋

ｼ代▽縺ｮ陦ｨ蜀縺ｧ隲也炊逧縺ｪ隍謨ｰ縺ｮ陦ｨ繧剃ｽ懈舌＠縺滉ｸ翫〒縲√◎繧後◇繧後ｒ邨仙粋縺吶ｋ邨仙粋
窶ｻ竭蜈繝繝ｼ繝悶Ν

EMPNO ENAME BOSS
1 C# 1
2 C++ 2
3 VB.NET 1
4 VB6 1
5 JAVA 2
6 JS 7
7 PERL 7

窶ｻ竭｡蜈繝繝ｼ繝悶Ν蜀縺ｮBOSS繧呈歓蜃ｺ縺励◆繝繝ｼ繝悶Ν

EMPNO ENAME
1 C#
2 C++
7 PERL

窶ｻ竭縺ｨ竭｡繧達OSS = EMPNO縺ｧ邨仙粋
SELECT E.EMPNO, E.ENAME, E.BOSS, B.ENAME BOSSNAME
FROM EMPLOYEE E LEFT OUTER JOIN EMPLOYEE B
ON E.BOSS = B.EMPNO

EMPNO ENAME BOSS BOSSNAME
1 C# 1 C#
2 C++ 2 C++
3 VB.NET 1 C#
4 VB6 1 C#
5 JAVA 2 C++
6 JS 7 PERL
7 PERL 7 PERL

蜊倡ｴ皮ｵ仙粋

蛻･蜷搾ｼ夂峩遨咲ｵ仙粋/繧ｯ繝ｭ繧ｹ邨仙粋
繝繧ｫ繝ｫ繝育ｩ(逶ｴ遨)縺檎函謌舌＆繧後ｋ縲

SELECT *
FROM 繝繝ｼ繝悶Ν1 CROSS JOIN 繝繝ｼ繝悶Ν1
竊薙→蜷後§
(1)SELECT * FROM 繝繝ｼ繝悶Ν1, 繝繝ｼ繝悶Ν2
(2)SELECT * FROM 繝繝ｼ繝悶Ν1 NATURAL JOIN 繝繝ｼ繝悶Ν2
窶ｻ蜈ｱ騾壼礼┌縺励ｮ蝣ｴ蜷
(3)SELECT * FROM 繝繝ｼ繝悶Ν1 (INNER) JOIN 繝繝ｼ繝悶Ν2
窶ｻ邨仙粋譚｡莉ｶ辟｡縺
(4)SELECT * FROM 繝繝ｼ繝悶Ν1 (INNER) JOIN 繝繝ｼ繝悶Ν2
ON 繝繝ｼ繝悶Ν1.蛻 = 繝繝ｼ繝悶Ν1.蛻
窶ｻ辟｡諢丞袖縺ｪ邨仙粋譚｡莉ｶ

騾溷ｺｦ謾ｹ蝟

SELECT * FROM 繝繧ｹ繝 JOIN (SELECT * 逕溷ｾ WHERE 諤ｧ蛻･ = 逕ｷ) AS 逕ｷ逕溷ｾ
縲ON 逕ｷ逕溷ｾ.NO = 繝繧ｹ繝.NO
窶ｻ逕ｷ縺縺代↓邨槭▲縺溽函蠕偵ユ繝ｼ繝悶Ν縺ｨ繝繧ｹ繝医ｒNO蛻励ｮ譚｡莉ｶ縺ｧ邨仙粋(邨仙粋陦後′蟆代↑縺)
SELECT * FROM 繝繧ｹ繝 JOIN 逕溷ｾ
縲ON 逕溷ｾ.NO = 繝繧ｹ繝.NO
WHERE 諤ｧ蛻･ = 逕ｷ
窶ｻ荳｡繝繝ｼ繝悶Ν縺ｫ縺翫¢繧起O蛻励ｮ譚｡莉ｶ縺ｧ蜈ｨ莉ｶ邨仙粋縺励◆蠕後∫塙縺縺代↓邨槭ｋ(邨仙粋陦後′螟壹＞)

邨仙粋縺励◆蛻励ｒ譖ｴ譁ｰ

UPDATE
(
縲SELECT 蛻 FROM 陦ｨ1 JOIN 陦ｨ2 ON ｽ
)
SET 蛻 = '蛟､'

Follow me!

繧ｫ繝繧ｴ繝ｪ繝ｼ
SQL
繧ｿ繧ｰ
SQL

蜑阪ｮ險倅ｺ

SQL DMLｼ壼渕譛ｬ謫堺ｽ(CRUD)
2014/12/22
SQL

谺｡縺ｮ險倅ｺ

SQL DMLｼ夐寔險(繧ｰ繝ｫ繝ｼ繝怜喧)
2014/12/22