結合#ケツゾク#

22542 ワード

JOINは2つ以上のテーブルでデータを検索できます.
🐨 JOINがなかったら?
  • は2回以上のクエリ
  • を必要とする.
  • 以前のクエリと現在のクエリの結果を同時に得ることはできません.
    🐨 JOINの使い方
  • 私は何のデータがほしいですか(SELECT)
  • データがどのテーブルにあるか(FROM)
  • が望むテーブル間の共通カラム(FK)とは何か(直接共通のカラムがない場合は複数のテーブルを結合する必要がある)
  • .

    1.コネクタ

    <--Inner Join
    SELECT e.employee_id, e.department_id, d.department_name
        FROM employees e, departments d
        WHERE e.department_id = d.department_id
        AND last_name = 'King';

    만약 primary key가 2개면 2개 다 같아야한다고 명시해줘야한다.

    위의 오라클에서 사용하는 조인 쿼리를 국제 표준인 ANSI JOIN으로 표기하면 아래와 같다.

    --ANSI Join
    SELECT e.employee_id, e.department_id, d.department_name
    	FROM employees e INNER JOIN departments d
    	ON e.department_id = d.department_id
        WHERE last_name = 'King';
    基本的にJOINはINNERJOINなのでINNERJOINの代わりにJOINで表記しても大丈夫です
    --3개 이상의 조인
    SELECT c.cname, s.major, s.syear
        FROM student s, course c, score sc
        WHERE sc.sno = s.sno
        AND sc.cno = c.cno
        AND major = '화학'
        AND syear = 1;
        
    --ANSI JOIN에서의 3개 이상 조인
    --두 개의 테이블을 먼저 조인 후 세 번째 테이블을 조인
    SELECT c.cname, s.major, s.syear
    	FROM stduent s
        JOIN score sc
        	ON s.sno = sc.sno
        JOIN course c
        	ON c.cno = sc.cno
        WHERE major = '화학'
        AND syear = 1;

    2.自己結合


    物理テーブルは1つしかありませんが、論理的には2つのテーブルとして結合すべきで、自己結合と呼ばれます.(通常、上司や部下などの階層を表形式化するために使用されます)
    ex)従業員番号と上司番号を有するテーブルでは、上司番号は同じテーブルの従業員番号を指す.上司に関する情報を印刷する場合は、自分でサインします.
    select a.last_name || '의 매니저는 ' || b.last_name || '이다.'
        from employees a, employees b
        where a.manager_id = b.employee_id
        and a.last_name = 'Kochhar';
    select DISTINCT s1.sno, s1.sname
        from student s1, student s2
        where s1.sname = s2.sname
        and s1.sno != s2.sno;

    3.外部結合


    Outer Joinは、結合条件(Where,On条件節)によって結合された場合、2つのテーブルの1つの値がNULLの行も返される結合形式です.
    外部結合が必要な理由
    内部ジョインは、すべてのローに共通のカラム値が存在することを前提としてカラムを基準としてジョインされるため、カラムNULLのローはジョイン後にデータが失われる可能性があります.このような場合のデータ損失を防ぐために、外部接続です.
    =>既存のロー数をチェックした後、表示されていない情報がある場合は外部結合を使用します.
    SELECT e.employee_id, e.department_id, d.department_name
    	FROM employees e, departments d
        WHERE e.department_id = d.department_id(+); --누락된 반대쪽에 (+)
    --직원에 부서 정보가 없다

    1) LEFT OUTER JOIN


    左の表からデータを読み出し、右の表からJOINターゲットデータを読み出します.比較対象列に値がない場合は、NULL値を使用して入力します.

    2) RIGHT OUTER JOIN


    LEFTER OUTER JOINとは逆に、右側の表からデータを読み出し、左の表からJOINターゲットデータを読み出します.
    「Left/Right Join」は絶対的な基準ではありません.これは、最初にどのテーブルを使用するかによって異なります.
    上の外部接続はANSI JOINで次のように表します.
    SELECT e.employee_id, e.department_id, d.department_name
    	FROM employees e LEFT JOIN departments d --누락된 쪽이 왼쪽이니까 Left Join
        ON e.department_id = d.department_id;

    外部結合の例

    교수 테이블의 ROW 수: 36개
    과목 테이블의 ROW 수: 32--이너 조인한 경우
    select c.cname, p.pname
      from course c, professor p
      where c.pno = p.pno;  --29개      
    --<교수 테이블을 기준으로 출력>
    select c.cname, p.pname
    	from course c, professor p
      where c.pno(+) = p.pno; --교수들 중 과목을 안맡은 부분을 출력 ( 36개)
    --교수에 과목 정보가 없다
    
    select c.cname, p.pname
    	from course c RIGHT JOIN professor p
      ON c.pno = p.pno;
    
     
    --<과목 테이블을 기준으로 출력>
    select c.cname, p.pname
      from course c, professor p
     where c.pno = p.pno(+); --과목들 중 교수가 없는 부분을 출력 ( 32개)
    --과목에 교수 정보가 없다
    
    select c.cname, p.pname
    	from course c LEFT JOIN professor p
       ON c.pno = p.pno;
    外部結合の概念自体は簡単です.いずれにしても、内部結合は、欠落する可能性のある情報をnullの一方の部分に問い合わせることができます.
    に注意
  • ここでは、授業表は教授表のpkをfkとしているので、授業表の情報を調べればpno値がnullであることがわかりますが、教授の立場から見ると、科目を教えずに遊んでいる教授はコートジョイントでしか確認できません.
  • 3) FULL OUTER JOIN


    左右のテーブルのすべてのデータを読み込み、チェックインし、繰り返したデータを削除します.(性能に問題がある可能性があるので、LEFT、RIGHT OUTER JOINのうちの1つのみを使用することが望ましい)
    select c.cname, p.pname
      from course c full join professor p
      on c.pno = p.pno;

    cf. CROSS JOIN


    CROSS JOINは、可能なすべての組み合わせ(CATSIAN PRODUCT)を2つのテーブルで検索します.両テーブル間には何らかの関連関係(FK)を媒介するJOINではないため,CROSS JOINの結果には実際には存在し得ないゴミ値が多く含まれている.
    select c.cname, p.pname
      from course c cross join professor p
    がいぶけつごうもんだい
  • --Oracle
    SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
      FROM EMP E, DEPT D
     WHERE E.DEPTNO(+) = D.DEPTNO
    ORDER BY D.DEPTNO, E.ENAME;
    
    --ANSI JOIN
    SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.JOB, E.SAL
      FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
    ORDER BY D.DEPTNO, E.ENAME;
  • --Oracle
    SELECT D.DEPTNO, D.DNAME,
           E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
           S.LOSAL, S.HISAL, S.GRADE,
           E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
      FROM EMP E, DEPT D, SALGRADE S, EMP E2
     WHERE E.DEPTNO(+) = D.DEPTNO
       AND E.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
       AND E.MGR = E2.EMPNO(+)
    ORDER BY D.DEPTNO, E.EMPNO; 
    
    --ANSI JOIN
    SELECT D.DEPTNO, D.DNAME,
           E.EMPNO, E.ENAME, E.MGR, E.SAL, E.DEPTNO,
           S.LOSAL, S.HISAL, S.GRADE,
           E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
      FROM EMP E RIGHT OUTER JOIN DEPT D
                    ON (E.DEPTNO = D.DEPTNO)
                  LEFT OUTER JOIN SALGRADE S
                    ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
                  LEFT OUTER JOIN EMP E2
                    ON (E.MGR = E2.EMPNO)
    ORDER BY D.DEPTNO, E.EMPNO; 

    しぜんせつぞく


    特殊なWHEREまたはON条件を必要とせず、自動的に同列を全てJOIN標準列に設定して連結する.
    内部結合とは異なり、結合オブジェクトの2つのテーブルに自然に結合され、同じ値のカラムを基準に2つのテーブルが結合され、共通のカラムが1つに結合されます.
    SELECT empno, ename, deptno 
         FROM emp NATURAL JOIN dept;

    USING条件セクション


    NATURALJOINの欠点は、すべての同名のコラムが結合になり、USING文で1つの列を選択して結合できることです.
    SELECT e.empno, e.ename, deptno 
         FROM emp e JOIN dept d USING(deptno);
    またINNERJOINの後にUSING節を書くと、USING節で使われている色が共通の色に処理され、1つしか残っていません.