SQL Cook: JOIN에 대해

Chappie: 코딩 좋아하는 사람·2022년 7월 9일
MYSQL JOINmaria join
0

SQL_COOK

목록 보기
13/35
post-thumbnail

OUTER JOIN?    LEFT/RIGHT JOIN?

이미지 출처: dsin.wordpress.com
JOIN의 여러 종류가 본격적으로 등장하기 시작했다.
미리 여러 가지 JOIN의 종류를 알아두는 것이 좋을 것이다.

JOIN

  • 이전 레시피까지 사용했던JOIN은 INNER JOIN과 동일하다고 설명되어 있었다
    JOIN은 사진의 가운데처럼 두 테이블의 값이 일치하는 부분을 반환한다

실험을 여러 가지로 해보자

select * from dept join emp;
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname      | loc      | empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
|     10 | ACCOUNTING | NEW YORK |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     40 | OPERATIONS | BOSTON   |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     20 | RESEARCH   | DALLAS   |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     40 | OPERATIONS | BOSTON   |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     10 | ACCOUNTING | NEW YORK |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     20 | RESEARCH   | DALLAS   |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     40 | OPERATIONS | BOSTON   |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     10 | ACCOUNTING | NEW YORK |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     40 | OPERATIONS | BOSTON   |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     20 | RESEARCH   | DALLAS   |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     40 | OPERATIONS | BOSTON   |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     10 | ACCOUNTING | NEW YORK |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     20 | RESEARCH   | DALLAS   |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     40 | OPERATIONS | BOSTON   |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     10 | ACCOUNTING | NEW YORK |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     20 | RESEARCH   | DALLAS   |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     30 | SALES      | CHICAGO  |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     40 | OPERATIONS | BOSTON   |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     30 | SALES      | CHICAGO  |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     40 | OPERATIONS | BOSTON   |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     40 | OPERATIONS | BOSTON   |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     30 | SALES      | CHICAGO  |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     40 | OPERATIONS | BOSTON   |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     20 | RESEARCH   | DALLAS   |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     40 | OPERATIONS | BOSTON   |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     10 | ACCOUNTING | NEW YORK |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     40 | OPERATIONS | BOSTON   |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     20 | RESEARCH   | DALLAS   |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     40 | OPERATIONS | BOSTON   |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     10 | ACCOUNTING | NEW YORK |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     40 | OPERATIONS | BOSTON   |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
|     30 | SALES      | CHICAGO  |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
|     40 | OPERATIONS | BOSTON   |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
60 rows in set (0.002 sec)

왜 이렇게 나왔을까?
말그대로 그냥 합치라고 했기 때문이다.
그 어떠한 조건도 주지 않았기 때문에 '가능한 한 모든 조합'에 대한 조회 결과가 나왔다.

그렇다면 특정 칼럼에 대한 조건을 걸어주면 어떻게 나올까?

select * from dept a 
	join emp b 
    on a.deptno = b.deptno;
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname      | loc      | empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     10 | ACCOUNTING | NEW YORK |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.001 sec)

'DEPT' 테이블의 'DEPTNO' 칼럼이 가진 값과 'EMP' 테이블의 'DEPTNO' 칼럼이 가진 값이 일치하는 행을 찾아서 붙여준 걸 볼 수 있다.

  • 예를 들어, 'EMP' 테이블의 'DETPNO'의 값이 20인 행이 있다면, 여기에는 'DEPT' 테이블의 'DETPNO'의 값이 20인 행이 연결된다
    나머지 값들도 그렇게 연결된다

  • 'DEPT' 테이블은 4행짜리고 'EMP' 테이블은 15행짜리인데 어떻게 연결이 되느냐?
    이어준다고 했다. 근데 1대1로 이어주겠다는 말이 아니다.
    조건은 그저 해당 칼럼이 가진 값이 같을 때 같은 행을 잇겠다고 했다.
    1대1로 대응시키는 게 아니라, 값이 같을 때 그 내용을 붙여주는 것이다.

이제 위 쿼리에 칼럼값까지 정확하게 조건을 부여해보겠다.

select * from dept a 
	join emp b 
    on a.deptno = b.deptno
    where a.deptno = 20
>>
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
| deptno | dname    | loc    | empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
|     20 | RESEARCH | DALLAS |  7369 | SMITH | CLERK   | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     20 | RESEARCH | DALLAS |  7566 | JONES | MANAGER | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     20 | RESEARCH | DALLAS |  7788 | SCOTT | ANALYST | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     20 | RESEARCH | DALLAS |  7876 | ADAMS | CLERK   | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     20 | RESEARCH | DALLAS |  7902 | FORD  | ANALYST | 7566 | 2006-12-03 | 3000 | NULL |     20 |
+--------+----------+--------+-------+-------+---------+------+------------+------+------+--------+
5 rows in set (0.002 sec)

값이 20인 행만 모두 반환된 것을 볼 수 있다.

  1. 양 테이블에서 'DETPNO'의 값이 일치하는 자료들을 가져와서 붙인다.
  2. 그중 'DEPTNO'의 값이 20인 자료들만 남겼다

이렇듯 JOIN (INNER JOIN)은 공통된 값을 가진 데이터를 반환한다. 사진에서 보이는 교집합이라 생각할 수 있겠다.


OUTER JOIN: LEFT/RIGHT JOIN

  • 'OUTER JOIN'은 LEFT JOIN 또는 RIGHT JOIN을 뜻한다
    'OUTER'라는 키워드는 존재하지 않는다
    LEFT(RIGHT) JOIN은 LEFT(RIGHT) OUTER JOIN으로 쓸 수 있을 뿐이다
    INNER JOIN이 JOIN과 동일하고 'INNER'라는 키워드는 없는 것과 동일한 이치다

  • 그렇다면 이 형태의 JOIN은 어떻게 다를까?

    • 일반적인 INNER JOIN은 FROM절에서 A테이블에 B테이블을 JOIN하는 것과 B테이블에 A테이블을 JOIN하는 것에 차이가 없다
      공통된 부분만 조회하기 때문이다

    • 하지만 LEFT JOIN은 기준이 되는 테이블의 모든 행을 반환한다
      RIGHT JOIN은 JOIN하여 가져올 테이블의 모든 행을 반환한다

select * from dept a
	left join emp b
    on a.deptno = b.deptno
>>
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname      | loc      | empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
|     10 | ACCOUNTING | NEW YORK |  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |
|     40 | OPERATIONS | BOSTON   |  NULL | NULL   | NULL      | NULL | NULL       | NULL | NULL |   NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
16 rows in set (0.001 sec)

'DEPT' 테이블의 행은 겨우 네 줄이지만, 결과는 16줄이 나온 것을 볼 수 있다.

위의 LEFT/RIGHT를 붙이지 않은 INNER JOIN에서는 15줄이 나왔다.
'EMP' 테이블에 'DETPNO' 칼럼의 값이 40인 자료는 없기 때문에, 공통 부분을 찾을 수 없었기 때문이다.

하지만 OUTTER JOIN에서는 기준 테이블 또는 JOIN을 위해 가져올 테이블 둘 중 하나를 반드시 모두 반환한다.
칼럼의 값이 비어 있다면 NULL값으로 대체된다.

FULL JOIN은 아직 경험해본 적이 없다.
진행하다가 만날 때 다시 정리하기.

profile
Chappie: 코딩 좋아하는 사람
Can do this all day
이전 포스트

SQL Cook: 3. 5 다른 테이블 행과 일치하지 않는 행 검색하기

다음 포스트

SQL Cook: 3. 6장 다른 JOIN을 방해하지 않고 쿼리에 JOIN 추가하기

0개의 댓글