데이터베이스(DBMS)_조인(Join)

▶조인(Join)

 

두개의 테이블을 서로 연결하여 서로가 가지고 있는 정보를 서로의 테이블에서 확인을 할 수 있도록 연결하여 정보를 상호보완하기위해 사용을 하며 각자의 테이블을 연결해주는 어떠한 연결고리를 사용하여 데이터를 조회해올 것 인데 그 작업을 조인이라고 한다.

 

▶조인의 기본사용방법

 

두개의 테이블에 같은 컬럼(연결고리)이 최소 하나는 존재해야하며 두 컬럼의 값은 공유되어야한다

사원과 자격증에 대한 정보가 있는 각각의 테이블이 있다.

이 두 테이블의 공통된 카테고리(컬럼)은 사번(SABUN)을 확인할 수 있다.

즉, 두 테이블을 연결시켜주는 연결고리는 사번이다.

 

 

보통 조인을 위해 기본키(primary key)와 외래키(foreign key)를 활용한다.

 

▶조인의 종류: Inner join

 

각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.

그래서 Inner Join은 교집합이라고 말할 수 있다.

 

A에도 있고 B에도 있는 파란색 영역의 데이터만을 가져오는 것이 INNER JOIN이다.

그래서 위의 SAWON과 LICENCSE 테이블을 INNER JOIN을 한다면 서로의 테이블에 데이터가 존재하는 

 

동일한 사번을 통해서 정영훈, 정영훈3, 정보처리기사, 정보관리기술사라는 데이터만 남아있게될 것 이다.

LICENSE에만 기록이 있는 정보처리산업기사는 퇴사한사람의 라이센스일 경우가 높다.

 

▶Inner Join 의 예시

 

 
SELECT DEPARTMENT_ID 
FROM EMPLOYEES e 
;
 
SELECT *
FROM DEPARTMENTS d 
;
 

 

위의 두 테이블을 inner Join 을 원하면 위처럼 진행하면 된다. 

INNER JOIN 이라는 키워드를 통해 EMPLOYEES 와 DEPARTMENT를 이어주었고 연결고리인 DEPARTMENT ID를 ON과 = 키워드로 연결시켰다.

근데 테이블 하나가 아닌 두개, 세개 이상의 데이터를 연결하여 조회하고 싶다면 어떻게할까?

 

 

위는 employees의 알리아스로  employees < department Id < location id < country Id< region id 이렇게 서로 이어져있는 것을 볼 수 있다. 이 테이블들을 연결해보자.

서로가 연결되어있는 연결고리를 기점으로 쿼리문을 작성할 것이다.

 

SELECT e.EMPLOYEE_ID , e.FIRST_NAME , d.DEPARTMENT_ID , d.DEPARTMENT_NAME ,
       l.LOCATION_ID , l.CITY 
FROM EMPLOYEES e 
   INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
   INNER JOIN LOCATIONS l   ON d.LOCATION_ID = l.LOCATION_ID 
;

 

이 쿼리문은 employees - department를 department ID를 통해 연결하고 

                 department - loation 을 location ID로 서로 연결하였다. 

 

▶조인의 종류: Outer Join

 

조인 조건에 일치하는 데이터 및 일치하지 않는 데이터를 모두 select하며 조인 조건에 일치하는 데이터가 없다면 NULL로 가져온다. 그래서 Outer Join은 Inner Join과는 다르게 주(main) 테이블이 어떤 테이블인지가 중요하다. 

이는 어떠한 테이블이 중심이 되느냐에 따라 다시 Left Outer Join, Right Outer Join이 될 것이고 만약 두 테이블의 모든 데이터를 모두 가져온다면 Full Outer Join으로 세분류할 수 있다.

 

즉, Left Outer join은 왼쪽에 있는 테이블이, Right Outer Join은 오른쪽에 있는 테이블이, Full Outer Join은 양쪽 테이블 모두가 중심이라는 뜻이다.

 

 

그런데 만약 A와 B위 위치를 바꾸는 것이 자유롭다면 Left와 Right를 서로 나누는 것이 의미가 있을까?

 

사실 SQL 쿼리문의 문법 자체로는 큰 의미는 없고 보통 Left Outer Join을 메인으로 사용한다. 

개발을 하면서 Left와 Right를 동시에 사용을 하는 경우는 몹시 드물다. 

또한 우리는 데이터베이스로 어떠한 데이터를 select하고 정보를 세분화하고 깊게 들어가 데이터를 가지고 오게되는데 full 데이터를 가져오는 것은 우리의 데이터베이스를 사용하는 목적을 비교하면 큰 의미가 없다.

 

즉, 우리는 Left Outer Join을 중점으로 두고 공부를 하면 된다.

 

▶조인의 종류: Left Outer Join

 

 

왼쪽에 있는 테이블이 기준이 되어 데이터를 받아오는 것으로 조인 조건에 부합하는 데이터가 조인당하는 테이블(오르쪽)에 있으면 해당 데이터를 가져오고 그 데이터가 부재하면 Null 로 select가 된다.

 

아까의 테이블을 다시 가져왔다.

SAWON 테이블이 메인이고 SAWON테이블을 기점으로 하여 데이터를 LICENSE 테이블에서 데이터를 가져올 것이다.

 

정영훈, 정영훈3을 제외하곤 다른사원들은 자격증이 없기에 

 

해당 데이터가 부재하여 Null로 select가 된다.

 

select *
from sawon a 
       left outer join license b

 

 그리고 SAWON테이블에서 LICENSE를 조인하고 싶다면 위와 같이 쿼리문을 작성한다.

 

▶조인의 종류: Right Outer Join

 

 

오른쪽테이블이 기준이 되며 조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽)에 있으면 해당 데이터를, 부재하면 Null로 Select된다.

그렇게 LICENSE테이블이 기준이 된다면 select가 위와 같이 나올 것 이다.

 

select *
from sawon a
       right outer join license b

 

▶조인의 종류: Full Outer Join

 

양쪽 테이블 모두가 기준이 된다. 

Join 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽 or 오른쪽)에 있으면 해당 데이터를, 부재하면 Null 로 select 된다.

오른쪽 왼쪽의 기준으로 full 데이터를 가져오는 것이기에 모든 데이터가 select됨을 확인할 수 있다.

 

select *
from sawon a
       full outer join license b

 

▶적합한 사용 예시: Inner Join & Left Outer Join

 

그럼 우리회사 전체사원 정보를 가져올 껀데 그 정보 중에서 자격증 정보를 포함해서 가지고와라면?

Left Outer Join 사용

 

우리회사 사원 전체를 조회를 해야하고 사원에 대한 정보도 출력을 하는데 그 중에서 자격증이 있는 사람의 정보까지 출력을 해주세요 라고 했다.

 

왼쪽 테이블을 조회하는데 추가적으로 오른쪽에 테이블에 대한 정보를 가져와야 할 경우 Left Outer Join을 사용한다.

 

만약, 회사에서 자격증을 소지하는 사원의 수와 리스트를 달라 라고 할 경우엔?

Inner Join 사용

 

자격증이 있는 사원만 필요한 경우이며 SAWON 테이블의 모든 데이터가 필요가 없다.

사원과 자격증 테이블의 조건을 둘 다 만족하는 사람의 정보를 가져와야할 경우 inner Join을 사용한다.

 

즉, 두 테이블간에 반드시 존재하는 데이터만 뽑아올 땐  Inner Join 사용 

전체 정보를 가져오고 어떠한 특정 데이터까지 가져와야할 땐 Left Outer Join 을 사용한다.

 

Inner Join 

SELECT count(*)
FROM EMPLOYEES e  
     INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
;

Left Outer Join

SELECT count(*)
FROM EMPLOYEES e  
     LEFT outer JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
;

 

Inner Join과 Left Outer Join은 데이터를 조회하는 기준점도 다르며 Inner Join 은 Null값이 포함되지않는다는 특징때문에 결과값이 다르게 나온다. 

그래서 위의 쿼리문의 결과값( Inner Join: 106 / Left Outer Join: 107)이 다른 이유도 inner Join이 가져온 데이터에는 Null값이 없기 때문이다.

 

 

 

 

NULL값이 누군가 하고 보니 Kimberely Grant이다. 이 사람은 현재 부서가 정해지지 않았던지 퇴사를 했던지 어떠한 모종의 이유로 부서값이 입력이 안되어있는 상태라서 부서가 Null값으로 되어있다.

부서가 Null값인거지 결국 전체사원에는 포함이 되어야하기에 만약 상사가 '전체인원 중'이라는 단어를 사용한다면 Inner Join이 아닌 Left Outer Join을 사용을 해야만한다.

 

이것이 실무에서 우리가 이 두가지 방법을 명확하게 구분하여 사용을 해야만하는 이유이다.

 

 

▶ANSI JOIN vs ORACLE JOIN

 

SQL은 데이터베이스를 관리하기 위해 만들어진 프로그래밍 언어이며, 데이터베이스를 관리해주는 대부분의 Management System(DBMS: 오라클, MySQL, Mariadb, ..)들은 SQL을 사용한다. 각자의 DBMS들은 개별의 SQL문을 가지고 있다. 이런 상황에 대한 불편함을 우리는 명확하게 알 수 있다.

 

예를들어 우리가 하나의 제품을 만든다고 하자. A라고 하는 고객사는 오라클 DB를 사용한다고 하고 B라는 업체는 갑자기 오라클은 유로서비스여서 MariaDB를 사용한다고 한다. 그럼 우리는 어떤 SQL문을 선택해야할까? 분명히 오라클에서 사용했던 쿼리문을 마리아에서 돌리면 에러가 날 것이다. 

 

이런 상황을 막기위해 미국 국립 표준 협회(American National Stadards Institute, ANSI)는 적어도 JOIN문에 대해서는 보편적인 문법을 제시하고 있는데, 그것이 바로 ANSI Query이다.

 

즉, DMBS 자체의 특수성때문에 SQL의 사용법이 조금씩 다르기도 하지만, 큰 틀에서 보면 나름대로의 보편성을 가지고 있다는 것이다. 

 

지금까지 사용했던 Left Outer Join 문은 모두 ANSI JOIN 형식으로 작성된것이다.

 

https://gent.tistory.com/469

 

보통 Join문은 ANSI 문법을 사용하는 경우가 대부분이지만 가끔  아주 가끔 오라클 조인을 사용하는 곳도 있으니 적어도 오라클 조인과 안시조인의 차이가 무엇인지 알고있도록하자.