LEFT OUTER JOIN演算

LEFT OUTER JOIN演算ã?¯ã€?join節を記述ã?§ã??ã‚‹JOIN演算ã?®ä¸€ã?¤ã?§ã?™ã€‚ ã?“ã?®æ¼”ç®—ã?§ã?¯æœ€åˆ?ã?®(å·¦ã?®)表ã?«ã?¦ã€?一致ã?™ã‚‹è¡Œã?ŒäºŒã?¤ç›®ã?®(å?³ã?®)表ã?«ã?ªã?„行ã?¯ã€?二ã?¤ç›®ã?®è¡¨ã?®NULLã?®è¡Œã?«çµ?å?ˆã?•れã?Ÿã‚‚ã?®ã?¨ã?—ã?¦ã€?çµ?æžœã?«æ®‹ã‚Šã?¾ã?™ã€‚

æ§‹æ–‡

表� LEFT [ OUTER ] JOIN 表�
{
    ON 真�� 
    }

ON節ã?«ã?‚ã‚‹ã?„ã?šã‚Œã?®çœŸå?½å¼?ã‚‚ã€?直接ã?®è¡¨ã?¨ç›´æŽ¥ã?®å•?ã?„å?ˆã‚?ã?›æ–‡ã?«ã?‚ã‚‹ã?„ã?‹ã?ªã‚‹è¡¨ã‚’å?‚ç…§ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ ON節ã?¯çµ?å?ˆã?•れã?¦ã?„ã?ªã?„表をå?‚ç…§ã?™ã‚‹ã?“ã?¨ã‚‚ã?§ã??ã?¾ã?™ã?—ã€?çµ?å?ˆã?•れã?¦ã?„る表をå?‚ç…§ã?—ã?ªã??ã?¨ã‚‚ã?‹ã?¾ã?„ã?¾ã?›ã‚“。(ã?Ÿã? ã?—一般的ã?«ã?¯å?‚ç…§ã?—ã?¾ã?™ã€‚)

例 1

--都市�対応�るアジア�国

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'

例 2


-- 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