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 遲臥エ
ON 邏 BETWEEN 譛菴 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蜿・
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縺ィ繧ィ繝ゥ繝シ縺ォ縺ェ縺」縺ヲ縺励∪縺蝣エ蜷
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縺ヲ縺縺ェ縺縲
螟夜Κ邨仙粋貍皮ョ怜ュ(+)
窶サ 竊薙→蜷後§
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
縺ョ荳。譁ケ
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繧九
閾ェ辟カ邨仙粋
邨仙粋譚。莉カ謖螳夂┌縺励ョ邨仙粋(譚。莉カ繧呈欠螳壹〒縺阪↑縺)
蛻怜錐縲√ョ繝シ繧ソ蝙九√し繧、繧コ縺ョ蛻励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譖エ譁ー
(
縲SELECT 蛻 FROM 陦ィ1 JOIN 陦ィ2 ON ス
)
SET 蛻 = '蛟、'