SQL활용 #4 - JOIN

1. INNER JOIN

• 교집합

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

• ANSI SQL(표준) (ORACLE, MYSQL, MSSQL...)
  : SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;

1. 오라클 문법

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

1. ITEM1 테이블과 ORDER1 테이블을 JOIN
   : 조건은 ITEM1.ITEMNO = ORDER1.ORDITEM

2. 1.에서 만든 테이블과 MEMBER1 테이블을 JOIN
   : 1.에서 만든 테이블을 ITEMORDER1이라고 설정한다.
   : 조건은 MEMBER1.USERID = ITEMORDER1.ORDID

3. 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;