본문 바로가기

별걸다하는 IT/데이터베이스 Database

[데이터베이스 SQL] 조인 join 질의법. 서로 다른 테이블에서 컬럼 값이 같은 행 추출하기, 테이블 연결지어 조건 검색하기 (이너조인 innerjoin)

[데이터베이스 SQL 완전정복 목차]

안녕하세요 양햄찌블로그 주인장입니다.

오늘은 요청을 받아, join 질의에 대한 포스팅을 진행해보려고 합니다.

JOIN이 필요한 상황 알기, 조인이란 무엇인가.

지금까지 우리는 하나의 테이블에서 원하는 값을 추출하는 법을 배웠죠~~~

그런데 업무를 하다 보면 연관된 다른 테이블의 값이 필요할 때가 있어요.

 

시나리오 예를 살펴볼게요. 제가 옷가게 사장이라고 해볼까요? (CEO죠아)

오늘 하루 매출 테이블이 있을 거예요. 간간히 같은 상품ID가 눈의 띄는걸 보니 판매가 많이 늘었나봐요.

무슨 상품이 이리 효자상품인지 궁금한대 당일매출 릴레이션에는 상품ID밖에 없네요..?

select 상품명
from 상품릴레이션
where 상품ID = 'A0000123'

그러면,,, 이렇게 상품ID를 일일이 다 기억해놨다가 상품 릴레이션 테이블에서 수기로 검색을 해야할까요?

 

음 저기 상품ID가 마침 두 테이블에 공통 속성으로 있으니, 상품 릴레이션에 대한 레코드들을 당일매출에다가 이어붙일 수는 없을까?

요렇게요! 네 가능합니다. 이걸 조인이라고 해요.

 

정리하자면 JOIN 조인이란,

서로 다른 두 개 이상의 릴레이션에서 연관된 애트리뷰트를 조건으로, 레코드를 결합하는걸 말한다!

조인의 종류 types of joins~

조인도 두 개의 릴레이션을 결합했을 때 어떻게 보여주느냐에 따라 여러 종류로 나뉩니다.

 

1. 이너조인

2. 풀조인

3. 레프트 아웃터 조인

4. 라이트 아웃터 조인.

5. 셀프 조인

6. 네추럴 조인

7. 등가 조인

8. 비등가 조인 등.

 

위에서 예시로 든 조인은 이너조인(inner join)입니다. 가장 흔하고 많이 사용되는 조인이죠.

그래서 일반적으로 조인 하면 이너조인을 지칭합니다.

사진출처: https://www.dofactory.com/sql/join

이너 조인의 특징은 A라는 컬럼 기준으로 연결했을 때, 두 테이블의 A컬럼 값이 일치하는 레코드만 보여주는 거예요. 즉 교집합 부분만 보여준다!!

 

교집합만 보여주는게 무슨 뜻일까요?

위 시나리오 예시를 살짝 변경해볼게요.

당일 매출 릴레이션에 상품ID가 B0001111인 제품이 40000만원 금액으로 거래되었다고 남았어요

그런데 상품ID가 상품릴레이션에 없네요..? 등록이 누락된 상품이 있었나..?

조인 조건으로 사용하는 컬럼이 '상품ID'인데, 두 테이블 간에 '상품ID'가 일치하지 않는 레코드가 있을 경우, 결과에서 제외됩니다.

둘 다 가지고 있을 경우에만! 즉 이너 조인은 교집합이 결과로 표시됩니다.

다른 조인들에 대해서는 차차 이후 포스팅에서 살펴보도록 하고, 

오늘은 먼저, INNER JOIN에 대해 알아볼거예요. 

INNER JOIN문법, 이너조인 쿼리 작성법

여러 방법으로 이너조인을 할 수 있는데요.

1. 컴마연산자와 비교연산자 

2. inner join 키워드와 on 키워드 

요렇게 두 가지 방법을 사용할 수 있습니다.

 

1. 컴마 연산자와 비교 연산자 사용.

제가 많이 쓰는 방법입니다. 

연결지을 릴레이션은 컴마로 나열해주었으며,

table1.column and table2.column2는 조인조건으로 where절에 기술합니다.

두 개의 릴레이션을 연관시키기 위해서는 한 개의 조인 조건이 필요하다~ 

--> 결국 일반적으로 N개의 릴레이션을 조인하려면, n-1개의 조인 조건이 필요합니다.

 

2. inner join키워드와 on 키워드

아무튼,, 다시 2번 문법을 살펴보면,

inner join이라는 키워드를 통해 직접적으로 조인시켜줄 수 있습니다.  

여기서 inner join의 inner는 생략이 가능합니다. table1 join table2 해도 결과는 같아요.

on에다가는 조인 조건을 기술하면 됩니다.

 

첫 번째 방법은 더 짧아서 작성하기 귀찮아 자주 사용하게 되고,,

두 번째 방법의 경우 join 키워드와 on키워드를 직접적으로 명시해주기 때문에

보기에 코드가 좀 더 이해하기 쉬워진다는 장점이 있죠. 뭐 손에 익는 방식을 사용하시면 됩니다.

SAMPLE 데이터 체크

이후부터는, 기본적인 사용 예시를 몇 개 보여드리고, 감 좀 익히시라고 문제를 낼건데요.

데이터베이스 목차 초반에 보시면 다운로드 받았던 EMPLOYEES 샘플 데이터를 사용할 거예요. 

EMPLOYEES 테이블 

EMPLOYEES 테이블에 COMMISION_PCT 커미션과 DEPARTMENT_ID 부서ID 컬럼 존재.

departments 테이블

DEPARTMENTS 테이블에 DEPARTMENT_NAME 부서이름 컬럼과 LOCATION_ID 위치ID 컬럼 존재

LOCATIONS 테이블

LOCATIONS 테이블에 LOCATION_ID(위치ID)와 CITY 도시컬럼이 존재합니다.

사용 예시, EXAMPLES

[기본 이너조인(내부조인) 사용해보기] 

select *
from employees a, departments b
where a.department_id = b.department_id; --조인조건(두 테이블 연결짓는 키)

--또는

select *
from employees a inner join departments b 
on a.department_id = b.department_id;

별칭을 사용해 쿼리문을 더 간단하게 할 수 있어요. 테이블명을 일일이 명시해주긴 번거로우니까요.

짠 잘 연결된 것을 볼 수 있죠??

 

[이너조인, 교집합 개수 비교]

select 'a join b' 결과, count(*) 개수
from locations a, departments b
where a.location_id = b.location_id --조인조건(두 테이블 연결짓는 키)
union 
select 'b join c' 결과, count(*) 개수
from departments b, employees c
where b.department_id = c.department_id --조인조건(두 테이블 연결짓는 키)
union 
select 'a', count(*) 
from locations
union
select 'b', count(*)
from departments
union
select 'c', count(*)
from employees;

위치테이블은 a로, 부서 테이블은 b, 고용자테이블은 c로 별칭했어요.

자 a레코드 개수가 23개, b 레코드 개수가 27개인데 둘이 조인한 결과 개수가 27이므로~~

이는 합집합과 교집합이 같죠. 

그런데 반면 b테이블은 27개 c테이블은 107개인데 b와 c는 106개예요.

여기서 우리는 아, c테이블에 b와 조인조건을 만족하지 못하는 레코드가 하나 있어서, 한개가 빠졌구나 생각할 수 있습니다. 합집합은 107개인데, 교집합은106개인셈이죠. 이너조인이기 때문에 교집합만 결과로 보여집니다.

 

[AND 연산자를 이용해 조건을 추가 검색]

SELECT last_name, e.department_id, d.department_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.last_name = 'Baida' ;

--또는

SELECT last_name, e.department_id, d.department_name 
FROM employees e JOIN  departments d
ON  e.department_id = d.department_id
WHERE e.last_name = 'Baida' ;

inner 조인 결과 중에서도 특정 조건을 만족하는 애들만 필터링 하고 싶을 수 있죠.

그럴 땐 AND 연산으로 추가적 조건을 제시해주면 됩니다. 간단간단.

조인 결과 레코드 수는 106개인데 그 중에서도 이름이 Baida인 레코드는 딱 하나만 있기 때문에 결과로 한 행만 조회되었어요.

당연히 where조건(추가 검색 조건)은 ON으로 걸러진 후의 결과물에 적용됩니다.

 

[세 개 테이블에서 조인해보기]

SELECT e.last_name, d.department_id, l.city 
FROM employees e, departments d , locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id ;

테이블이 세 개니까 조인조건 두 개를 명시해줬습니다. 추가 조건을 걸어줄때에도 AND를 사용하면 됩니다.

연습문제 

※ 모든 사원의 이름(LAST_NAME), 부서번호(DEPARTMENT_ID), 부서 이름(DEPARTMENT_NAME) 을 표시하는 질의를 작성하시오

출력되어야 하는 질의 결과 화면

 커미션(COMMISSION_PCT)을 받는 모든 사원의 이름(LAST_NAME), 부서 이름(DEPARTMENT_NAME) , 위치 ID(LOCATION_ID) 및 도시(CITY)를 표시하는 질의를 작성하십시오

출력되어야 하는 질의 결과 화면

 Toronto에서 근무하는 모든 사원의 이름, 업무(JOB_ID), 부서번호 및 부서이름을 표시하는 질의를 작성하십시오.(join, on 키워드 사용할 것)

출력되어야 하는 질의 결과 화면

다양한 문제를 적어봤어요 ㅎㅎ

이너조인 특징 정리

▶서로 다른 두 개 이상의 릴레이션에서 연관된 애트리뷰트를 조건으로, 레코드를 결합하는걸 말한다!

조인 조건에 사용되는 속성은 외래키이다. (다른테이블과 연결이 되어야 하므로) 

테이블명을 다 써주긴 번거로우므로 별칭을 잘 활용하자.

일반적으로 N개의 릴레이션을 조인하면, n-1개의 조인 조건이 필요하다.

WHERE절에는 다른 조건보다 조인 조건을 먼저 열거하자.

▶조인 조건에 사용되는 각 테이블의 컬럼명이 일치할 필요는 없다. 

조인 조건을 생략하거나 조건이 틀리게 표현됐다면, 두 테이블의 카티션 곱이 생성된다

▶따라서 카티션 곱을 피하기 위해 where절에 항상 유효한 조건을 쓰자!

 

오늘 포스팅은 여기까지입니다.

나름 열심히 작성해봤어요. 도움이 되셨다면 공감 조아요 ♥

오늘하루도 감사합니다. 다음 포스팅에서 봐요. 

 

  • 신입사원 2020.09.03 13:17

    감사합니다 ㅜㅜ
    비정공 신입으로 it처음 들어와서 계속 이해 못하고 혼나기만 하는데 블로그 내용들이 많은 도움이 되고있습니다 질문해주는 것도 이렇게 상세하게 설명해주셔서 감사합니다 정말 감사합니다 !!!
    항상 좋은 일만 가득하세요!!좋은 하루되세요!!:)

  • brandon.lee 2020.12.01 15:30

    대박이네요. 이렇게 설명을 잘하시다니 강사님이신 것 같네요. 저는 웹개발자인데요~ 쿼리문 연습하면서 들리다가 글이 너무 정성스럽고 잘 적어주셔서 ~ 이런 양질의 자료를 무료로 읽을 수 있다는것에 감사하여 글을 남겨요 블로그 주인님 좋은일만 가득하세요!!

  • 집시보이 2021.01.22 14:00

    다른데서는 찾아보기 힘든 좋은 정보를 잘 정리해 주셨네요.
    저도 도움 많이 받고 갑니다. 정말 고맙습니다.

  • potato 2021.02.04 10:04

    저도 비전공 신입으로 it회사 막 입사해서 공부중인데
    정말 깔끔하고 이해하기 쉽게 설명해주시네요 ㅠㅠ
    앞으로도 많은부분 참고하겠습니다. 감사합니다!