LEFT OUTER JOIN演算ã?¯ã€?join節を記述ã?§ã??ã‚‹JOIN演算ã?®ä¸€ã?¤ã?§ã?™ã€‚ ã?“ã?®æ¼”ç®—ã?§ã?¯æœ€åˆ?ã?®(å·¦ã?®)表ã?«ã?¦ã€?一致ã?™ã‚‹è¡Œã?ŒäºŒã?¤ç›®ã?®(å?³ã?®)表ã?«ã?ªã?„行ã?¯ã€?二ã?¤ç›®ã?®è¡¨ã?®NULLã?®è¡Œã?«çµ?å?ˆã?•れã?Ÿã‚‚ã?®ã?¨ã?—ã?¦ã€?çµ?æžœã?«æ®‹ã‚Šã?¾ã?™ã€‚
表� LEFT [ OUTER ] JOIN 表� { ON 真�� }
ON節ã?«ã?‚ã‚‹ã?„ã?šã‚Œã?®çœŸå?½å¼?ã‚‚ã€?直接ã?®è¡¨ã?¨ç›´æŽ¥ã?®å•?ã?„å?ˆã‚?ã?›æ–‡ã?«ã?‚ã‚‹ã?„ã?‹ã?ªã‚‹è¡¨ã‚’å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ ON節ã?¯çµ?å?ˆã?•れã?¦ã?„ã?ªã?„表をå?‚ç…§ã?™ã‚‹ã?“ã?¨ã‚‚ã?§ã??ã?¾ã?™ã?—ã€?çµ?å?ˆã?•れã?¦ã?„る表をå?‚ç…§ã?—ã?ªã??ã?¨ã‚‚ã?‹ã?¾ã?„ã?¾ã?›ã‚“。(ã?Ÿã? ã?—一般的ã?«ã?¯å?‚ç…§ã?—ã?¾ã?™ã€‚)
--都市ã?«å¯¾å¿œã?™ã‚‹ã‚¢ã‚¸ã‚¢ã?®å›½ SELECT CITIES.COUNTRY, CITIES.CITY_NAME, REGION FROM Countries LEFT OUTER JOIN Cities ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE WHERE REGION = 'Asia' -- LEFT JOINã?¨ã?„ã?†åˆ¥ã?®æ›¸ã??æ–¹ã?«ã‚ˆã‚Šã€?å‰?例ã?¨å?Œã?˜çµ?果を -- 実ç?¾ã?™ã‚‹ã€‚ SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION FROM COUNTRIES LEFT JOIN CITIES ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE WHERE REGION = 'Asia'
-- EMPLOYEEã?¨DEPAETMENTã?¨ã?„ã?†è¡¨ã‚’çµ?å?ˆã?—ã?¦ã€? -- 1930よりå‰?ã?«ç”Ÿã?¾ã‚Œã?Ÿ(BIRTHDATE)å…¨ã?¦ã?®ç¤¾å“¡ã?®ã€? -- 社員番å?·(EMPNO)ã?¨ã€? -- 社員ã?®è‹—å—(LASTNAME)ã?¨ã€? -- 部署番å?·(EMPLOYEEã?¨ã?„ã?†è¡¨ã?®WORKDEPTã?¨ã€?DEPARTMENTã?¨ã?„ã?†è¡¨ã?®DEPTNO)ã?¨ã€? -- 部署å??(DEPTNAME)ã‚’é?¸æŠžã?™ã‚‹ã€‚ SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT ON WORKDEPT = DEPTNO AND YEAR(BIRTHDATE) < 1930 -- 管ç?†è€…ã?®ã?„ã?ªã?„部署をå?«ã‚?å…¨ã?¦ã?®éƒ¨ç½²ã‚’ã€? -- 部署ã?®ç®¡ç?†è€…ã?®ç¤¾å“¡ç•ªå?·ã?¨è‹—å—ã?¨å…±ã?«ä¸€è¦§ã?™ã‚‹ã€‚ SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE ON MGRNO = EMPNO