MySQL) JOIN 예제

테이블을 합칠때는 JOIN을 쓰지만 한테이블에서는 주로 subquery를 사용할 수 있다. 
서브쿼리 공부에 필요한 MySQL 예제와 실습 코드 첨부 

JOIN
   : 한번의 SELECT문장으로 2개 이상의 테이블에 있는 컬럼의 정보를 검색하고 싶을 때 사용한다.
   : JOIN의 종류
     1) INNER JOIN
         - EQUI JOIN = 동등조인 = NATURAL JOIN
         - NON EQUI JOIN : 조인 대상 테이블의 어떤 컬럼의 값도 일치하지 않을 때 사용
                          EX) BETWEEN AND , IS NULL, IS NOT NULL, IN, > , < 등의  조건문을 사용할때 쓴다.
        
      2) OUTER JOIN
           : 기본 EQUI JOIN을 하면서 별도의 테이블의 모든 정보를 검색하고 싶을때 사용한다.
              - LEFT OUTER JOIN
              - RIGHT OUTER JOIN
              - FULL OUTER JOIN
    
      3) SELF JOIN
           : 자기 자신테이블을 조인하는 것(하나의 테이블을 2개처럼 사용하는 것)
           : 주로 재귀적관계일 때 많이 사용한다. (재귀적관계란 자신자신테이블의 PK를 FK로 참조하는 것)
           
    : JOIN 코딩 방법
      1) SQL JOIN  - FULL OUTER JOIN은 제공하지 않는다. 
      2) ANSI JOIN : 미국국립표준연구소에서 정한 미국의 표준을 기본으로 한다. - 권장

 

Using을 사용하는 이유는 중복을 제거하기 위해서이다. 

CREATE TABLE TEST1(
    ID VARCHAR(10) PRIMARY KEY,
    NAME VARCHAR(30),
    ADDR VARCHAR(50)
);

INSERT INTO TEST1 VALUES('JANG', '희정', '서울');
INSERT INTO TEST1 VALUES('KIM', '도훈', '서울');
INSERT INTO TEST1 VALUES('GYEEB', '경민', '부산');
INSERT INTO TEST1 VALUES('HEE', '홍경', '서울');
INSERT INTO TEST1 VALUES('KING', '소현', '제주도');

SELECT * FROM TEST1;
 
 SELECT * FROM TEST2;
 
 CREATE TABLE TEST2(
    CODE CHAR(3) PRIMARY KEY,
    ID VARCHAR(10) ,  
    JOB VARCHAR(30),
    SAL int  ,
   foreign key(id) REFERENCES TEST1(ID) -- FK - 비식별관계(다른테이블의 PK를 일반속성으로 참조)
);

-- id가 test1을 참조한다. 


INSERT INTO TEST2 VALUES('A01', 'JANG','강사',200);
INSERT INTO TEST2 VALUES('A02', 'JANG','개발자',300);
INSERT INTO TEST2 VALUES('A03', 'HEE','디자이너',250);
INSERT INTO TEST2 VALUES('A04', 'KING','기획자',400);
INSERT INTO TEST2 VALUES('A05', NULL,'백수',500);


SELECT * FROM TEST1;
SELECT * FROM TEST2;

-- ID, NAME, JOB, SAL 검색하고 싶다 -> JOIN한다.
-- 1.코딩방식 (SQL조인)

select * 
from test1, test2
where test1.id = test2.id;

-- 특정 컬럼만 조회 
select test1.id, name, job,sal 
from test1, test2
where test1.id = test2.id;

-- 테이블이름에 별칭추가 
select t1.id, name, job,sal 
from test1 t1, test2 t2
where t1.id = t2.id;


-- 2..코딩방식 (ANSI조인) - 권장
select *
from test1 join test2
on test1.id = test2.id;  -- on은 조인의 조건을 준다. 

-- 원하는 컬럼만 조회 
select  test1.id, name, code, job, sal
from test1 inner join test2 
on test1.id = test2.id;  -- on은 조인의 조건을 준다. 

select  t1.id, name, code, job, sal
from test1 t1 inner join test2 t2
on t1.id = t2.id;  -- on은 조인의 조건을 준다. 

-- USING사용하기 (양쪽 테이블의 조건 컬럼이름이  같은경우)

select  *  -- 중복되는 컬럼(조인대상 컬럼은 한개만 나온다) id가 원래는 2개 나왔는데 한개만 출력
from test1  join test2  
using(id); -- id 컬럼을 기준으로 조인한다. 
 
 -- 특정 컬럼만 조회 
 select ID, NAME, JOB, SAL
from test1  join test2  
using(id); --


-- NATURAL JOIN
select * 
FROM TEST1 natural JOIN TEST2; -- 양쪽의 동일한 컬럼 찾아서 알아서 조인 

 
-- 2) ANSI조인방식 - LEFT
SELECT *
FROM TEST1 LEFT OUTER JOIN TEST2
USING(ID);

SELECT *
FROM TEST1 RIGHT OUTER JOIN TEST2
USING(ID); -- CODE는 다 나왔다. 


SELECT * FROM TEST1;
SELECT * FROM TEST2;
SELECT * FROM TEST3;

-- 2) ANSI조인방식 - RIGHT


-- 2) ANSI조인방식 - FULL


-- 3개의 테이블 조인하기
CREATE TABLE TEST3(
    CODE CHAR(3)      ,
    MANAGER_NAME VARCHAR(30),
    PHONE VARCHAR(30),
   PRIMARY KEY(code) ,
   foreign key(code) REFERENCES TEST2(CODE)  -- PK, FK (식별관계)
);


INSERT INTO TEST3 VALUES('A01', '김','111-1111');
INSERT INTO TEST3 VALUES('A02', '신','222-2222');
INSERT INTO TEST3 VALUES('A03', '오','333-3333');

SELECT * FROM TEST1;
SELECT * FROM TEST2;
SELECT * FROM TEST3;

-- EX) ID, NAME, ADDR, JOB, SAL, MANAGER_NAME, PHONE 검색

-- ANSI 조인방식
SELECT *
FROM TEST1 JOIN TEST2 ON TEST1.ID = TEST2.ID JOIN TEST3 ON
TEST2.CODE = TEST3.CODE;

-- 특정 컬럼 선택하기
SELECT  ID, NAME, JOB, SAL, MANAGER_NAME, PHONE
FROM TEST1 JOIN TEST2 using(ID) JOIN TEST3 USING(CODE);  -- 중복 제거

-- using사용하기
select *
from test1 join test2 using(id) join test3 using(code);


-- 특정 컬럼 선택하기
select id, name, job, sal, manager_name , phone
from test1 join test2 using(id) join test3 using(code);

-- USING사용
-- USING사용 - 특정컬럼만 선택

-- 조인에 조건 넣기 -- SAL가 300이상인 레코드 조인하기
select * -- test1.id, name, job, sal, manager_name , phone
from test1 join test2 on test1.id = test2.id and sal >=300;

select * -- test1.id, name, job, sal, manager_name , phone
from test1 join test2 on test1.id = test2.id  where sal >=300;

 --  ANSI조인방식에 조건 주기

 
 -- 3) USING에 조건 주기 
/*select * 
from test1 join test2 using(id) and sal>=300;*/

select * 
from test1 join test2 using(id) where sal>=300;


-- NON-EQUI JOIN 
-- EMP테이블에서 사원의 정보 + 급여등급을 함께 검색하고 싶다
SELECT * FROM EMP;
SELECT * FROM SALGRADE; -- 급여 등급

select *
from emp join salgrade on sal between losal and hisal order by sal;

select empno, ename, job, sal, grade, losal, hisal
from emp join salgrade on sal between losal and hisal order by sal;

 
-- SELF JOIN - 자기자신 테이블을 2개로 만들어서 조인(재귀적관계)
-- EX) SMITH사원의 관리자는 FORD입니다. 출력

select * from emp;

select e1.empno 사원번호, e1.ename 사원이름 , e2.empno 관리자의사원번호 , e2.ename 관리자이름
from  emp e1 join emp e2  -- e1 사원테이블 , e2는 관리자정보테이블
on e1.mgr = e2.empno ;


/*

   1) 합집합
        UNION ALL - 중복레코드를포함
        UNION - 중복레코드 제외
        
*/

 -- 테이블 복사
CREATE TABLE SET_COPY
AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL > 2000;

SELECT * FROM SET_COPY;

-- 레코드르 3개정도 추가
INSERT INTO SET_COPY VALUES(8000,'HEEJUNG','TEACHER',3000);
INSERT INTO SET_COPY VALUES(8001,'HEE','PROGRAMER',4000);
INSERT INTO SET_COPY VALUES(8002,'KIM','PROGRAMER',2500);

SELECT * FROM SET_COPY; -- 9개의 레코드 

-- 1) 합집합
SELECT EMPNO, ENAME, JOB, SAL FROM EMP -- 14
UNION ALL
SELECT EMPNO, ENAME, JOB, SAL FROM SET_COPY ; -- 9

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
UNION 
SELECT EMPNO, ENAME, JOB , SAL FROM SET_COPY;

 

JOIN 예제 실습