[SQL]JOIN

Join

1. 내부 join ( = 등가 join)

    1 - 자연 join

    2 - Using join

    3 - ON join

    

    비등가 join ( = on join, using join)

 

내부 join은 크게 등가 join과 비등가 join으로 나누어진다.

등가 join 밑에 자연 join, on join, using join이 있다.

자연 join (Natural join 등가 조인의 업그레이드 버전이다.)

 

2. 외부 join

 

3. 교차 join

 

4. 셀프 join

 

etc. 세미 join, 안티 join

 

 

 

join에 대해서 말해 보라고 하면 위의 4가지를 말하면 되고 etc는 나중에 알아가면 된다.

 

 

영어 버전

1. Inner join ( = Equi join)

    1 - Natural join

    2 - ON join

    3 - Using join

    

    Not Equi join ( = on join, using join)

 

 

2. Outer join

 

3. Cross join

 

4. Self join

 

etc. Semmi join, Anti join

 

---

교차 join (Cross join)

 

먼저 테이블 2개를 생성해주겠다.

create table tab1(
    name    varchar2(20),
    gmo     number,
    sal     number
);

create table tab2(
    gmo     number,
    grade   varchar(20)
);

내부에 테이블 1에는 4개 테이블 2에는 2개의 데이터를 넣어준다.

-- tab1
insert into tab1 values('이순신', 1, 100);
insert into tab1 values('안중근', 2, 200);
insert into tab1 values('세종대', 2, 300);
insert into tab1 values('윤봉길', 3, 400);

-- tab2
insert into tab2 values(1, '호랑이');
insert into tab2 values(2, '코끼리');

교차 join은 테이블 2개를 교차로 모두 출력해주는 것이다.

--tsql 문법
select * from tab11, tab22


--위의 문장과 완전히 동일한 결과가 나온다.
--ansi 문법 (권장코드이지만 위에를 더 많이쓴다.)
select * from tab11 cross join tab22

tab1과 tab2를 교차로 집어넣어준다.

 

---

등가 join

 

where 뒤에 ~~~ = ~~~ 가 있으면 등가 join이다. 단, 공통된 필드가 있어야지만 성립한다.

 

ex 1)

select *
	from tab1, tab2
	where tab1.gmo = tab2.gmo

ex 2)

select * 
	from tab1 t1, tab2 t2
	where t1.gmo = t2.gmo

ex 3)

SELECT t1.name, t1.gmo, t1.sal, t2.gmo, t2.grade
	FROM TAB1 T1, TAB2 T2
	WHERE T1.GMO = T2.GMO;

ex 4)

SELECT name, T1.gmo, sal, grade
	FROM TAB1 T1, TAB2 T2
	WHERE T1.GMO = T2.GMO;

필드명이 중복되지 않으면 t1이나 t2를 생략할 수 있다.

대신 필드명이 중복되면 테이블명을 정의해줘야 한다.

 

ex5)

SELECT name, T1.gmo, sal, grade
	FROM TAB1 T1 inner join TAB2 T2
	ON T1.GMO = T2.GMO;

inner join이라고 쓰면 등가 join이라는 게 명확하게 표시돼서 잘 쓰는 사람들이 많다.

 

두 개의 테이블에서 공통된 필드를 하나의 값으로 만들어준다.

pk(primary key 기본키)와 fk(foregin key 외래키)를 등가로 놓고 문장을 작성하면 등가 join이 된다.

 

where절에 조건식 걸어주기

SELECT name, T1.gmo, sal, grade
	FROM TAB11 T1, TAB22 T2
	WHERE T1.GMO = T2.GMO;

SELECT name, T1.gmo, sal, grade
	FROM TAB1 T1, TAB2 T2
	WHERE T1.GMO = T2.GMO
        AND sal >= 200;

from 뒤에 있는 and는 join 조건이고 where 뒤에 있는 and는 다른 조건이다(?)

출력해 볼 때는 전체를 한번 출력해보고 조건을 걸어줘서 어느 부분이 다른지 확인해보는 것이 좋다.

 

SELECT name, T1.gmo, sal, grade
	FROM TAB11 T1, TAB22 T2
	WHERE T1.GMO = T2.GMO
        AND rownum <= 2

---

비등가 join(아직 정리x)

 

between이 나오면 비등가 join이다.

 

---

셀프 join (자체 join)

 

pk와 fk가 한 테이블에 있는 경우이다.

한 테이블에 2개의 별명을 만들어서 사용한다.

 

지금까지 테이블을 모두 지우고 새로운 테이블을 생성해줄 것이다.

drop table tab01;

create table tab01(
    id      number,
    name    varchar2(20),
    mgr     number
)

필드를 넣어준다.

insert into tab01 values (1, '호랑이 1', null);
insert into tab01 values (2, '호랑이 2', 1);
insert into tab01 values (3, '호랑이 3', 2);
insert into tab01 values (4, '호랑이 4', 3);

호랑이들의 각각의 사수를 출력해줄 것이다.

호랑이 1이 출력되지 않는 이유는 호랑이 1의 사수는 null값이기 때문이다.

select t1.id, t1.name, t1.mgr,
        t2.id as MGR_ID,
        t2.name as MGR_NAME
    from tab01 t1, tab01 t2
    where t1.mgr = t2.id

위의 코드와 똑같은 코드이다. 셀프 join의 코드이다.

select t1.id, t1.name, t1.mgr,
        t2.id as MGR_ID,
        t2.name as MGR_NAME
    from tab01 t1 join tab01 t2
    ON t1.mgr = t2.id

 

---

외부 조인(outer join)

 

걸러진 데이터와 걸러지지 않은 데이터를 같이 보고 싶을 때 사용한다.

싹 지우고 테이블 2개를 생성해준다.

drop table tab01;

create table tab01(
    id      number,
    name    varchar2(20)
);

drop table tab02;

create table tab02(
    id     number,
    age    number
)

필드를 추가해준다.

-- tab01 필드
insert into tab01 values(1, '호랑이 1');
insert into tab01 values(2, '호랑이 2');
insert into tab01 values(3, '호랑이 3');
insert into tab01 values(4, '호랑이 4');
insert into tab01 values(5, '호랑이 5');

--tab02 필드
insert into tab02 values(1, 100);
insert into tab02 values(2, 200);
insert into tab02 values(3, 300);
insert into tab02 values(4, 400);
insert into tab02 values(5, 500);

등가 join 나타내는 법

select *
    from tab01 t1, tab02 t2
    where t1.id = t2.id;

select * 
    from tab01 t1, tab02 t2
    where t1.id = t2.id(+); --왼쪽 테이블(t1) 내용은 전부 출력하고 우측은 조건에 맞는것만 출력    

select *
    from tab01 t1, tab02 t2
    where t1.id(+) = t2.id; --오른쪽 테이블(t2) 내용은 전부 출력하고 좌측은 조건에 맞는것만 출력    


-- using(id)를 사용하기
select *
    from tab01 t1 left join tab02 t2
    using(id);

select *
    from tab01 t1 right join tab02 t2
    using(id);

Full Outer Join 

겹치는 부분을 먼저 출력해주고, 그다음 나머지를 출력해준다.

SELECT *
	FROM TAB01 T1 FULL OUTER JOIN TAB02 T2
	ON T1.ID = T2.ID;

 

A 값만 뽑기

Left Join으로 전체 A값에서 where 조건인 B값을 빼준 것이다.

SELECT *
	FROM TAB01 T1 LEFT JOIN TAB02 T2
	ON T1.ID = T2.ID
	WHERE T2.ID IS NULL; 

 

B 값만 뽑기

 

A값 구하기와 반대이니 Right Join을 써주면 된다.

where 조건도 t1으로 바꿔줘야 한다.

SELECT *
	FROM TAB01 T1 RIGHT JOIN TAB02 T2
	ON T1.ID = T2.ID
	WHERE T1.ID IS NULL;

 

 

전체에서 교집합 부분 빼기(Full Join 사용)

 

Full Outter Join을 사용해 전체를 선택한 후에 where 조건으로 t1과 t2의 공통부분을 제외시켜준다.

SELECT *
	FROM TAB01 T1 FULL OUTER JOIN TAB02 T2
	ON T1.ID = T2.ID
	-------------------------여기까지 A U B
	WHERE T1.ID IS NULL OR T2.ID IS NULL; -- 여기서 교집합을 빼준다.

---

자연 join (Natural join)

 

자연 join은 등가 join의 업그레이드 버전이다. ( 자연 조인 = 등가 조인)

Using은 자연 조인의 업그레이드 버전이다. (Using = 자연 조인)

등가 조인 4가지 = 기본, Using, On, Natural

결국에는 Using을 사용한다.

Using은 등가, 비등가 둘 다 사용 가능하기 때문이다

자연은 등가만 사용한다.

Using을 쓸 거면 Using만 사용하고, On을 쓸 거면 On만 사용해야 한다. (결론은 Using을 쓴다.)

-- 등가 join
select *
	from tab1, tab2
	where tab1.gmo = tab2.gmo
    
    
-- 자연 join
select *
    from tab01 natural join tab02

 

 

---

USING

 

From 조인문 뒤에 Using(원하는 조인 요소) : 인수로 넣은 조인 요소가 겹치는 녀석을 찾아준다.

똑같은 id에 해당하는 교집합을 찾아준다.

SELECT *
	FROM TAB01 T1 JOIN TAB02 T2
	USING(ID);

 

정리 :

1. 일반 등가 조인(귀찮다)

2. NATURAL 조인(편하려고 업그레이드 한 번만 사용 가능)

조인 키가 여러 개일 때 자연 조인을 사용할 수 없다. (조인 키는 겹치는 부분)

 

3. USING (한번 이상 사용이 가능하다 / 얘도 가능한데 그럼 둘의 차이점은(?))

The USING clause: This allows you to specify the join key by name.

컬럼값을 적을 수 있다. (조인 키의 지정이 가능하다.)

 

4. ON (USING보다 더 귀찮은 것 / ON은 위의 세 가지와 달리 비등가도 사용할 수 있다.(?)

얘가 아니라 USING만 비등가인 거 같은데 잘 모르겠음)

The ON clause: This syntax allows you to specify the column names for join keys in both tables.

(양쪽 테이블의 칼럼 값을 조인키로 지정할 수 있다.)

 

LEFT OUTER JOIN 대신 그냥 OUTER JOIN으로 적어도 된다. (RIGHT / FULL JOIN 도 마찬가지)

---

세미 Join(SEMI JOIN) / 안티 Join(ANTI JOIN)

 

SELECT *
	FROM TAB01
	WHERE DEPT IN(20, 20, 20, 30, 30, 30, 40, 40, 40); -- 중복값이 들어감

- 세미 조인의 반대가 안티 조인이다.

- 고윳값을 가지고 있으면 1이라고 한다. (유일하므로)

- IN에는 중복 값이 들어가 있으므로 N이라고 한다. (중복값이 들어갈 수 있으므로)

- 이처럼 1 : N으로 성립될 때 이를 세미 조인이라고 한다.

- 고유번호와 중복값 IN을 이용한 Join / 따라서 IN이 들어가면 세미 조인

 

ex 1) 전교생이 1000명인 우리 학교에서 수강신청 목록은 무엇입니까 할 때 쓰인다.
ex 2) 중국집에 메뉴가 50개가 있을 때 오늘 팔았던 메뉴는 무엇인가 할 때 골라낼 수 있다.
고유 데이터가 들어있는 것들 중에서 사용한 부분만 골라낼 때 쓰인다.

 

-- tab04 생성
create table tab04(
    id      number,
    name    varchar2(20),
    dept     number
);

--tab04에 값을 넣어준다.
insert into tab04 values(1, '호랑이', 1);
insert into tab04 values(2, '강아지', 2);
insert into tab04 values(3, '고양이', 1);
insert into tab04 values(4, '고양이', 2);
insert into tab04 values(5, '고양이', 1);

-- tab05 생성
create table tab05(
    dept     number,
    dname    varchar2(20)
);

-- tab05에 값을 넣어준다.
insert into tab05 values(1, '경영학과');
insert into tab05 values(2, '중국학과');
insert into tab05 values(3, '정치외교');

 

IN안의 문장이 먼저 실행되고

메인 문장이 실행된다.

SELECT * -- 결과적으로는 같다.
	FROM TAB04
	WHERE DEPT IN(2, 2, 2, 3, 3, 3, 4, 4, 4);
    -- TAB01에 2만 존재하므로 2번 강아지와 4번 고양이만 나온다
    
SELECT *
	FROM TAB04
	WHERE DEPT IN(1,2,1,2); -- TAB01에서 1,2에 포함된 것은 전체이다
    
SELECT *
	FROM TAB02
	WHERE TAB02.DEPT IN (SELECT DEPT FROM TAB01); -- 1,2,1,2,1

SELECT *
    FROM TAB05 T2
    WHERE EXISTS(SELECT *
        FROM TAB04 T1
        WHERE T1.DEPT = T2.DEPT
    );

결과적으로 값은 같다.

EXISTS를 실행하는 게 실행결과가 더 빠르다.

- IN 같은 경우 데이터를 이미 걸러서 들어온다.(조인 값) 그 상태에서 필터링을 건다.

- EXISTS 데이터 한 개를 들고 EXISTS 안에서 존재하는지를 바로 찾는다.

- 결론적으로 IN은 쓰이지 않고 EXISTS를 사용한다.

- IN은 결과값으로 Tab05.dept와 계산 / EXISTS는 서브 쿼리 안에서 비교해서 값을 가져와버린다.

 

---

중국집의 5가지 메뉴에서 (1) 오늘 판 메뉴만 골라내는 방법과 (2) 안 팔린 메뉴를 골라내는 방법이다.

 

우선 menu 테이블과 sal 테이블을 만들어서 insert 해준다.

-- menu table 생성
create table menu(
    id      number,
    name    varchar2(20)
);

-- sal table 생성
create table sal(
    sid      number,
    snum    number,
    cost    number,
    name    varchar2(20)
);

-- menu 만들기
insert into menu values(1, '짜장');
insert into menu values(2, '우동');
insert into menu values(3, '냉면');
insert into menu values(4, '짬뽕');
insert into menu values(5, '탕슉');

-- 오늘 팔린 메뉴들
insert into sal values(1, 2, 3000, '우동');
insert into sal values(2, 2, 3000, '냉면');
insert into sal values(3, 2, 3000, '짜장');
insert into sal values(4, 2, 3000, '우동');
insert into sal values(5, 2, 3000, '냉면');
insert into sal values(6, 2, 3000, '짜장');
insert into sal values(7, 2, 3000, '우동');
insert into sal values(8, 2, 3000, '냉면');

 

exists를 사용해서 sal 테이블의 name과 menu 테이블의 name 중 공통된 것만 뽑아낸다.

select * 
    from menu
    where exists(
        select *
            from sal
            where sal.name = menu.name
        );

공통된 메뉴들을 뽑아낼 수 있다. 안 팔린 메뉴를 골라내는 것은 exists 앞에 not만 붙이면 된다.

select * 
    from menu
    where not exists(
        select *
            from sal
            where sal.name = menu.name
    );    

2개의 테이블을 등가 조인, 결과를 distinct 해버리면 그래도 결과가 똑같지 않냐
할 수는 있지만 비용이 많이 든다.