SQL DMLシ夂オ仙粋(JOIN)

讎りヲ/邨仙粋譁ケ豕

窶サ隍謨ー縺ョ繝繝シ繝悶Ν繧偵¥縺」縺、縺代k縲
縺昴ョ荳翫〒SELECT縺励◆繧翫☆繧九
諠蝣ア蜃ヲ逅雉譬シ逧縺ォ險縺縺ィ縲檎オ仙粋縲

(1)繝繝シ繝悶Ν繧 , 縺ァ蛹コ蛻繧翫仝HERE蜿・縺ァ邨槭k
SELECT * FROM 陦ィ1, 陦ィ2
WHERE 陦ィ1.蛻 = 陦ィ2.蛻 AND ス
窶サWHERE蜿・辟。縺励ョ蝣エ蜷茨シ壼ィ邨縺ソ蜷医o縺幄。ィ遉コ
(縲蜊倡エ皮オ仙粋縲榊盾辣ァ)

(2)JOIN蜿・縺ァ邨仙粋譚。莉カ繧呈欠螳
SELECT * FROM 繝繝シ繝悶Ν1
JOIN 繝繝シ繝悶Ν2 ON 繝繝シ繝悶Ν1.蛻暦シ = 繝繝シ繝悶Ν2.蛻暦シ

遲我セ。邨仙粋_髱樒ュ我セ。邨仙粋

遲我セ。邨仙粋
閾ェ辟カ邨仙粋縲^N蜿・縲ゞSING蜿・繧剃スソ逕ィ縺励◆邨仙粋譎ゅョ譚。莉カ縺ォ縲=縲阪r逕ィ縺縺ヲ蛻玲欠螳壹r陦後≧邨仙粋縲

髱樒ュ我セ。邨仙粋
ON蜿・繧剃スソ逕ィ縺励◆邨仙粋譎ゅョ譚。莉カ縺ォ縲=縲阪r逕ィ縺縺ェ縺蛻玲欠螳壹r陦後≧邨仙粋縲
BETWEEN縲!N縲´IKE遲峨ョWHERE蜿・縺ォ逕ィ縺繧峨l繧区、懃エ「譚。莉カ繧貞茜逕ィ縺ァ縺阪k縲
迚ケ螳壹ョ遽蝗イ縺ァ譚。莉カ繧呈欠螳壹☆繧狗オ仙粋譁ケ豕
邨ヲ荳弱ユ繝シ繝悶Ν
豌丞錐 邨ヲ荳 邏
莨願陸 180,000 ?
逕ー荳ュ 195,000 ?
逋ス莠 260,000 ?
遲臥エ壹ユ繝シ繝悶Ν
邏 譛菴 譛鬮
3 150,001 200,000
4 200,001 300,000
5 300,001 400,000
SELECT *
FROM 邨ヲ荳 JOIN 遲臥エ
ONBETWEEN 譛菴 AND 譛鬮
豌丞錐 邨ヲ荳 遲臥エ
莨願陸 180,000 3
逕ー荳ュ 195,000 4
逋ス莠 260,000 4

蜀驛ィ邨仙粋

邨仙粋蟇セ雎。
蛻怜錐
邨仙粋蟇セ雎。
蛻怜梛
隍謨ー蛻 邨仙粋蛻励ョ
SELECT
繝。繝ェ繝繝
NATURAL
JOIN
蛻・蜷咲ァーテ 蛻・蝙凝
(證鈴サ吶ョ蝙句、画鋤)
蠑キ蛻カAND譚。莉カ 蛻嶺ソョ鬟セテ 險倩ソー縺梧・ス
JOIN
USING
蛻・蜷咲ァーテ 蛻・蝙凝
(證鈴サ吶ョ蝙句、画鋤)
譚。莉カ謖螳壺雷 蛻嶺ソョ鬟セテ 陦ィ遉コ蛻励r貂帙i縺帙k
JOIN
ON
蛻・蜷咲ァー笳 蛻・蝙銀無
(譏守、コ逧蝙句、画鋤)
譚。莉カ謖螳壺雷 蛻嶺ソョ鬟セ笳 豁」遒コ

INNER JOIN

邨仙粋蟇セ雎。縺ョ陦ィ(繝繝シ繝悶Ν)縲∽ク。譁ケ縺ォ蜈ア騾壹☆繧九Ξ繧ウ繝シ繝縺ョ縺ソ繧堤オ仙粋繝サ謚ス蜃コ縺吶k邨仙粋譁ケ豕
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

竊鯛薙r邨仙粋

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蜿・繧剃スソ縺縺ョ縺ォ驕ゥ縺励※縺繧句エ蜷
(閾ェ辟カ邨仙粋縺ィ豈碑シ縺励※)
繝サ邨仙粋縺吶k陦ィ縺ォ蜷悟錐縺ァ蛻・蝙九ョ蛻励′縺ゅj縲∬ェ辟カ邨仙粋縺吶k縺ィ繧ィ繝ゥ繝シ縺ォ縺ェ縺」縺ヲ縺励∪縺蝣エ蜷
繧ィ繝ゥ繝シ縺ィ縺ェ繧句励r邨仙粋譚。莉カ縺九i螟悶○繧
繝サ邨仙粋縺吶k陦ィ縺ォ蜷悟錐縺ァNULL險ア螳ケ縺ョ蛻励′縺ゅj縲∬ェ辟カ邨仙粋縺吶k縺ィ繧ィ繝ゥ繝シ縺ォ縺ェ縺」縺ヲ縺励∪縺蝣エ蜷

DELETE
FROM 陦ィ1
USING 陦ィ2
WHERE
縲縲陦ィ2 シ主 = 陦ィ1 シ主 --邨仙粋譚。莉カ
AND ス --譚。莉カ莉

螟夜Κ邨仙粋

邨仙粋譚。莉カ縺ォ荳閾エ縺吶k繝繝シ繧ソ縺ォ蜉縺医※縲∫オ仙粋蟇セ雎。縺ョ陦ィ(繝繝シ繝悶Ν)縲√←縺。繧峨°迚譁ケ縺ォ縺縺代≠繧九Ξ繧ウ繝シ繝峨b謚ス蜃コ繝サ邨仙粋縺吶k邨仙粋譁ケ豕
縺薙ョ縺ゥ縺。繧峨°迚譁ケ縺ォ縺縺代≠繧玖。ィ縺ョ謖螳壹↓繧医▲縺ヲ縲
繝サ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)縺ィ縺励※陦ィ遉コ縺励※縺繧九
シ晏キヲ陦ィ縺ョ繝ャ繧ウ繝シ繝峨ッ蜈ィ縺ヲ陦ィ遉コ縺輔l縺ヲ縺繧倶ク譁ケ縲∝承陦ィ縺ョAssembly縺ョ繝ャ繧ウ繝シ繝峨ッ陦ィ遉コ縺輔l縺ヲ縺縺ェ縺縲

蜿ウ螟夜Κ邨仙粋

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繝ャ繧ウ繝シ繝峨′陦ィ遉コ縺輔l縺ヲ縺繧九
シ晏承陦ィ縺ョ繝ャ繧ウ繝シ繝峨ッ蜈ィ縺ヲ陦ィ遉コ縺輔l縺ヲ縺繧倶ク譁ケ縲
蟾ヲ陦ィ縺ョC++縺ョ繝ャ繧ウ繝シ繝峨ッDEPTNO繧呈戟縺溘↑縺轤コ縲∬。ィ遉コ縺輔l縺ヲ縺縺ェ縺縲

螟夜Κ邨仙粋貍皮ョ怜ュ(+)

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

蟾ヲ螟夜Κ邨仙粋縺ァ縺ッ陦ィ遉コ縺輔l縺ェ縺九▲縺蘗ssembly縺ョ繝ャ繧ウ繝シ繝峨′縲
蜿ウ螟夜Κ邨仙粋縺ァ縺ッ陦ィ遉コ縺輔l縺ェ縺九▲縺櫃++縺ョ繝ャ繧ウ繝シ繝峨′陦ィ遉コ縺輔l繧九

閾ェ辟カ邨仙粋

NATURAL JOIN蜿・
邨仙粋譚。莉カ謖螳夂┌縺励ョ邨仙粋(譚。莉カ繧呈欠螳壹〒縺阪↑縺)
蛻怜錐縲√ョ繝シ繧ソ蝙九√し繧、繧コ縺ョ蛻励r閾ェ蜍輔〒謚ス蜃コ縺励※邨仙粋縺吶k縲
繝サ邨仙粋譚。莉カ縺ォ蜷郁エ縺吶k蛻励′辟。縺蝣エ蜷医蜊倡エ皮オ仙粋縺ォ縺ェ繧九
繝サ邨仙粋譚。莉カ縺ォ蜷郁エ縺吶k蛻励′1縺、縺ョ蝣エ蜷医蜀驛ィ邨仙粋縺ォ縺ェ繧九
繝サ2縺、縲3縺、繝サ繝サ繝サ縺ィ閾ェ辟カ邨仙粋繧定。後≧蝣エ蜷医蟾ヲ螟夜Κ邨仙粋(LEFT OUTER JOIN)縺ォ縺ェ繧九
繝サ邨仙粋譚。莉カ蛻励′隍謨ー縺ゅk蝣エ蜷医ス LEFT OUTER JOIN ス ON ス AND ス 縺ィ蜷梧ァ倥∫オ仙粋譚。莉カ縺檎オ槭i繧後k縲

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

閾ェ蟾ア邨仙粋

シ代▽縺ョ陦ィ蜀縺ァ隲也炊逧縺ェ隍謨ー縺ョ陦ィ繧剃ス懈舌@縺滉ク翫〒縲√◎繧後◇繧後r邨仙粋縺吶k邨仙粋
窶サ竭蜈繝繝シ繝悶Ν

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

蜊倡エ皮オ仙粋

蛻・蜷搾シ夂峩遨咲オ仙粋/繧ッ繝ュ繧ケ邨仙粋
繝繧ォ繝ォ繝育ゥ(逶エ遨)縺檎函謌舌&繧後k縲

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
窶サ逕キ縺縺代↓邨槭▲縺溽函蠕偵ユ繝シ繝悶Ν縺ィ繝繧ケ繝医rNO蛻励ョ譚。莉カ縺ァ邨仙粋(邨仙粋陦後′蟆代↑縺)
SELECT * FROM 繝繧ケ繝 JOIN 逕溷セ
縲ON 逕溷セ.NO = 繝繧ケ繝.NO
WHERE 諤ァ蛻・ = 逕キ
窶サ荳。繝繝シ繝悶Ν縺ォ縺翫¢繧起O蛻励ョ譚。莉カ縺ァ蜈ィ莉カ邨仙粋縺励◆蠕後∫塙縺縺代↓邨槭k(邨仙粋陦後′螟壹>)

邨仙粋縺励◆蛻励r譖エ譁ー

UPDATE
(
縲SELECT 蛻 FROM 陦ィ1 JOIN 陦ィ2 ON ス
)
SET 蛻 = '蛟、'

Follow me!

蜑阪ョ險倅コ

SQL DMLシ壼渕譛ャ謫堺ス(CRUD)