SQL#4-JOIN利用



1. INNER JOIN

  • 交差

  • INNER JOIN
    :SELECT*FROM表1,表2 WHERE表1.表2.コラム

  • ANSI SQL(標準)(ORACLE、MYSQL、MSSQL...)
    :SELECT*FROM表1 INNERJOIN表2 ON表1.表2.コラム
  • 1.ロシア語文法

  • SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼 = 테이블2.컬럼;

  • ITEM 1テーブルとORDER 1テーブルをJOINに変換
    :条件はITEM1.ITEMNO = ORDER1.ORDITEM

  • 1.で作成したテーブルとMEMBER 1テーブルをJOINに変換
    : 1.で作成したテーブルをITEMORDER 1に設定します.
    :条件はMEMBER1.USERID = ITEMORDER1.ORDID

  • 2.で作ったテーブルをVIEWにします.
    :VIEW閲覧のみ
    :作成したSQL文を使用した仮想テーブルの作成
    :INSERT、UPDATEなどは使用できません
    : CREATE OR REPLACE VIEW ORDER1VIEW AS
  • -- 1. ITEM1 X ORDER1 = 새로운 테이블
    SELECT ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
        FROM 
            ITEM1, ORDER1 
        WHERE 
            ITEM1.ITEMNO = ORDER1.ORDITEM;
    
    -- 2. 새로운 테이블 X MEMBER1
    SELECT ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
        FROM MEMBER1, (
        SELECT 
            ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
            FROM 
                ITEM1, ORDER1 
            WHERE 
                ITEM1.ITEMNO = ORDER1.ORDITEM) ITEMORDER1
        WHERE MEMBER1.USERID = ITEMORDER1.ORDID;
        
    -- 3. 결과를 가상의 테이블(VIEW)로 만듦
    CREATE OR REPLACE VIEW ORDER1VIEW AS
    SELECT ITEMORDER1.*, MEMBER1.USERNAME, MEMBER1.USERADDR
        FROM MEMBER1, (
        SELECT 
            ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
            FROM 
                ITEM1, ORDER1 
            WHERE 
                ITEM1.ITEMNO = ORDER1.ORDITEM) ITEMORDER1
        WHERE MEMBER1.USERID = ITEMORDER1.ORDID;
        
    -- 4. 생성된 VIEW 조회 (ITEM1, MEMBER1, ORDER1)
    SELECT * FROM ORDER1VIEW;
    
    DELETE FROM ORDER1 WHERE ORDNO = 10021;
    
    -- VIEW는 조회만 가능
    -- 내가 만든 SQL문으로 가상의 테이블을 생성하는 것
    -- INSERT, UPDATE등은 불가

    2.ANSI SQL(標準文法)(ORACLE、MYSQL、MSSQL...)

  • SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;
  • -- 1. 단순조회
    SELECT * FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem;
    
    -- 2. 프로젝션
    SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
        FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem;
        
    -- 3. 결과물 다시 JOIN
    SELECT ITEMORDER1.*, member1.username, member1.useraddr FROM MEMBER1 
        INNER JOIN 
            (SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
                FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem) ITEMORDER1
        ON ITEMORDER1.ORDID = member1.userid;        
    
    -- 4. 3.번의 결과물을 VIEW로 만들기
    CREATE OR REPLACE VIEW ORDERVIEW1 AS
    SELECT ITEMORDER1.*, member1.username, member1.useraddr FROM MEMBER1 
        INNER JOIN 
            (SELECT ORDER1.*, item1.itemname, ITEM1.ITEMPRICE, ITEM1.ITEMQTY
                FROM ITEM1 INNER JOIN ORDER1 ON ITEM1.ITEMNO = order1.orditem) ITEMORDER1
        ON ITEMORDER1.ORDID = member1.userid; 
        
    -- 5. ORDERVIEW1 조회
    SELECT * FROM ORDERVIEW1;

    2. LEFT OUTER JOIN

  • 左全+右全マッチング
  • 1.ロシア語文法

  • SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼 = 테이블2.컬럼(+);
  • SELECT * 
        FROM 
            ITEM1, ORDER1 
        WHERE 
            ITEM1.ITEMNO = ORDER1.ORDITEM(+);

    2.ANSI SQL規格

  • SELECT * FROM 테이블1 LEFT OUTER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;
  • SELECT * FROM ITEM1 LEFT OUTER JOIN ORDER1
        ON item1.itemno = order1.orditem;

    3. RIGHT OUTER JOIN


    1.ロシア語文法

  • SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼(+)=테이블2.컬럼;
  • SELECT * FROM ORDER1, MEMBER1 WHERE order1.ordid(+)=member1.userid;

    2.ANSI SQL規格

  • SELECT * FROM 테이블1 RIGHT OUTER JOIN 테이블2 WHERE 조건;
  • SELECT * FROM ORDER1 RIGHT OUTER JOIN MEMBER1 
        ON order1.ordid=member1.userid;

    4. FULL OUTER JOIN


    1.ANSI SQLのみ

  • SELECT * FROM 테이블1 FULL OUTER JOIN 테이블2 ON 테이블1.컬럼(+)=테이블2.컬럼;
  • SELECT * FROM ORDER1 FULL OUTER JOIN MEMBER1
        ON order1.ordid=member1.userid;

    5.条件


    1.ロシア語文法

    -- 조건
    SELECT 
        ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
    FROM 
        ITEM1, ORDER1 
    WHERE 
        ITEM1.ITEMNO = ORDER1.ORDITEM AND item1.itemprice >= 2;

    2.ANSI SQL規格

    -- ANSI
    SELECT 
        ITEM1.ITEMNAME, ITEM1.ITEMPRICE, ORDER1.* 
    FROM 
        ITEM1 INNER JOIN ORDER1 
    ON 
        ITEM1.ITEMNO = ORDER1.ORDITEM 
    WHERE 
        item1.itemprice >= 2;