INNER JOINã?¨ã?¯ã€?明示的ã?ªçµ?å?ˆå?¥ã‚’記述ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã‚‹ã€?JOIN 演算ã?§ã?™ã€‚
表� [ INNER ] JOIN 表� { ON 真�� }
ONã?«çœŸå?½å¼?を記述ã?™ã‚‹ã?“ã?¨ã?§ã€?çµ?å?ˆå?¥ã?Œè¨˜è¿°ã?§ã??ã?¾ã?™ã€‚
SELECT * FROM SAMP.EMPLOYEE INNER JOIN SAMP.STAFF ON EMPLOYEE.SALARY < STAFF.SALARY
ON節ã?§ã?¯çµ?å?ˆã?•れã?¦ã?„ã?ªã?„表をå?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?¾ã?Ÿçµ?å?ˆã?—ã?¦ã?„る表をå?‚ç…§ã?—ã?ªã??ã?¦ã‚‚ã?‹ã?¾ã?„ã?¾ã?›ã‚“。(一般的ã?«ã?¯å?‚ç…§ã?—ã?¾ã?™ã€‚)
-- EMP_ACTã?¨EMPLOYEEã?¨ã?„ã?†è¡¨ã‚’çµ?å?ˆã?—ã?¾ã?™ã€‚ -- EMP_ACTã?¨ã?„ã?†è¡¨ã‚ˆã‚Šå…¨åˆ—ã‚’é?¸æŠžã?—ã?¦ã€? -- EMPLOYEEã?¨ã?„ã?†è¡¨ã‚ˆã‚Šå¾“æ¥å“¡ã?®è‹—å—(LASTNAME)ã‚’ã??れã?žã‚Œã?®è¡Œã?« -- åŠ ã?ˆã?¾ã?™ã€‚ SELECT SAMP.EMP_ACT.*, LASTNAME FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO -- EMPLOYEEã?¨DEPARTMENTã?¨ã?„ã?†è¡¨ã‚’çµ?å?ˆã?—ã?¦ã€? -- 1930年よりå‰?ã?«ç”Ÿã?¾ã‚Œã?Ÿ(BIRTDATE)å…¨ã?¦ã?®å¾“æ¥å“¡ã?®ã€? -- 従æ¥å“¡ç•ªå?·(EMPNO)ã€?従æ¥å“¡ã?®è‹—å—(LASTNAME)ã€? -- 部署番å?·(EMPLOYEEã?¨ã?„ã?†è¡¨ã?®WORKDEPTã?¨ã€?DEPARTMENTã?¨ã?„ã?†è¡¨ã?®DEPTNO)ã€? -- 部署å??(DEPTNAME)ã‚’é?¸æŠžã?™ã‚‹ã€‚ SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT ON WORKDEPT = DEPTNO AND YEAR(BIRTHDATE) < 1930 -- VALUES節ã?‹ã‚‰é?¸æŠžã?™ã‚‹å•?ã?„å?ˆã‚?ã?›(selectæ–‡ã?®ä¸€ä¾‹)ã?«ã‚ˆã‚Šå€¤ã‚’"生æˆ?"ã?™ã‚‹åˆ¥ã?®ä¾‹ -- ã?“ã?®å•?ã?„å?ˆã‚?ã?›ã?«ã‚ˆã‚Šã€?一行ã?®æƒ…å ±ã‚’æŒ?ã?¤"R1"ã?¨"R2"ã?¨ã?„ã?†2列ã?®"x"ã?¨ã?„ã?†è¡¨ã‚’ -- ã?©ã?®ã‚ˆã?†ã?«å°Žå‡ºã?™ã‚‹ã?®ã?‹ç¤ºã?™ã€‚ SELECT * FROM (VALUES (3, 4), (1, 5), (2, 6)) AS VALUESTABLE1(C1, C2) JOIN (VALUES (3, 2), (1, 2), (0, 3)) AS VALUESTABLE2(c1, c2) ON VALUESTABLE1.c1 = VALUESTABLE2.c1 -- This results in: -- C1 |C2 |C1 |2 -- ----------------------------------------------- -- 3 |4 |3 |2 -- 1 |5 |1 |2 -- å…¨ã?¦ã?®éƒ¨ç½²ã‚’ã€?管ç?†è€…ã?®è‹—å—ã?¨å¾“æ¥å“¡ç•ªå?·ã?¨å…±ã?«ä¸€è¦§ã?™ã‚‹ã€‚ SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME FROM DEPARTMENT INNER JOIN EMPLOYEE ON MGRNO = EMPNO -- å…¨ã?¦ã?®å¾“æ¥å“¡ã?®å¾“æ¥å“¡ç•ªå?·ã?¨è‹—å—ã‚’ã€? -- ã??ã?®ç®¡ç?†è€…ã?®å¾“æ¥å“¡ç•ªå?·ã?¨è‹—å—ã?¨å…±ã?«ä¸€è¦§ã?™ã‚‹ã€‚ SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME FROM EMPLOYEE E INNER JOIN DEPARTMENT INNER JOIN EMPLOYEE M ON MGRNO = M.EMPNO ON E.WORKDEPT = DEPTNO