INNER JOIN 演算

INNER JOINã?¨ã?¯ã€?明示的ã?ªçµ?å?ˆå?¥ã‚’記述ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã‚‹ã€?JOIN 演算ã?§ã?™ã€‚

æ§‹æ–‡

表� [ INNER ] JOIN 表� { ON 真�� }

ONã?«çœŸå?½å¼?を記述ã?™ã‚‹ã?“ã?¨ã?§ã€?çµ?å?ˆå?¥ã?Œè¨˜è¿°ã?§ã??ã?¾ã?™ã€‚

ON節��る����直接�表�よ��外部������文�ら�直接�SELECT文���間��る表��視��。 以下�例��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