4. SQL 고급 문법-2

기본미션: 외부 조인의 활용

선택미션: 내부 조인의 활용

0. 학습 목표

다양한 데이터 형식에 대해 이해
두 테이블을 연결하는 조인을 이해하고 활용
SQL에서 일반 프로그래밍 기능을 구현

0. 요약

정수형은 소수점이 없는 숫자이며 TINYINT, SMALLINT, INT, BIGINT가 있음
문자형은 고정형 문자형인 CHAR과 가변형 문자형인 VARCHAR이 있음
실수형은 소수점 아래 7자리까지 표현되는 FLOAT와 15자리까지 표현되는 DOUBLE가 있음
날짜형은 날짜DATE, 시간TIME, 날짜시간DATETIME이 있음
MySQL에서 제공되는 변수 앞에는 @를 붙임
데이터 형식을 변경하는 형 변환 함수에는 CAST(), CONVERT()가 있음
일대다 관계란 한쪽 테이블에는 하나의 값만, 다른 쪽 테이블에는 여러 개의 값이 존재할 수 있는 관계
조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들내는 것
내부 조인은 두 테이블을 조인할 때 두 테이블에 모두 지정한 열의 데이터가 있어야 함
외부 조인은 두 테이블을 조인할 때 1개의 테이블에만 데이터가 있어도 결과을 얻을 수 있음
상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인
자체 조인은 자신이 자신과 조인된다는 의미로 1개의 테이블만 쓰임

용어	설명
관계	두 테이블이 서로 연관되는 것
기본 키-외래 키 관계	두 테이블이 일대다 관계로 연결되지 위한 조건
별칭	조인에서 테이블의 이름을 짧게 표현하는 방법
DISTINCT	중복된 열의 값을 1개만 표현
LEFT OUTER JOIN	왼쪽 테이블의 모든 값이 출력
RIGHT OUTER JOIN	오른쪽 테이블의 모든 값이 출력
FULL OUTER JOIN	왼쪽 또는 오른쪽 테이블의 모든값이 출력
CREATE TABLE ~ SELECT	SELECT의 결과가 테이블로 생성되는 구문

2. 두 테이블을 묶는 조인

※핵심 키워드: 일대다 관계, 조인, 내부 조인, 외부 조인, 상호 조인, 자체 조인

2-1. 내부 조인

두 테이블을 연결할 때 가장 많이 사용하는 것이 내부 조인이다.

일대다 관계의 이해

두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 한다.

데이터베이스 테이블은 하나로 구성되는 것 보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다. 이 분리된 테이블은 서로 관계(relation)를 맺고 있습니다.

대표 예제로 market_db의 회원 테이블(member)과 구매 테이블(buy)을 살펴보자.

BLK를 기준으로 member에는 1개, buy에는 여러개가 있다.
즉 회원은 1명이지만 구매를 여러 번 할 수 있다.

이에 아래와 같이 mem_id에 대새서는 아래와 같이 설정되어 있다.

다만, 꼭 기본키-외래키 관계가 아니여도 가능한 조인도 있다.(상호 조인 등)

테이블 & 컬럼	설명
member.mem_id	Primary key(기본키)
buy.mem_id	Foreign Key(외래키)

일대다 관계의 이해

일반적으로 조인이라고 부르는 것은 내부 조인(inter join)이다. 조인은 3개 이상의 테이블로도 가능하지만 보통 2개로 조인한다.

내부 조인의 형식은 아래와 같다.

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건]

buy 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 조인해서 이름/주소/연락처 등을 검색해 보자.

USE market_db;
SELECT * 
	FROM buy
    	INNER JOIN member
        ON buy.mem_id = member.mem_id
    WHERE buy.mem_id = 'GRL';
    
    # num, mem_id, prod_name, group_name, price, amount, mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'7', 'GRL', '혼공SQL', '서적', '15', '5', 'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'

조인을 통해 두 개의 행이 결합된 것을 확인할 수 있다.

여기서 주의할 사항은 WHERE 절이 없다면 buy 테이블을 기준으로 조인되게 된다.

SELECT *
	FROM buy
    	INNER JOIN member
        ON buy.mem_id = member.mem_id;
        
 # num, mem_id, prod_name, group_name, price, amount, mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'1', 'BLK', '지갑', NULL, '30', '2', 'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'2', 'BLK', '맥북프로', '디지털', '1000', '1', 'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'3', 'APN', '아이폰', '디지털', '200', '1', 'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'4', 'MMU', '아이폰', '디지털', '200', '5', 'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'5', 'BLK', '청바지', '패션', '50', '3', 'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'6', 'MMU', '에어팟', '디지털', '80', '10', 'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'7', 'GRL', '혼공SQL', '서적', '15', '5', 'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'8', 'APN', '혼공SQL', '서적', '15', '2', 'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'9', 'APN', '청바지', '패션', '50', '1', 'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'10', 'MMU', '지갑', NULL, '30', '1', 'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'11', 'APN', '혼공SQL', '서적', '15', '1', 'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'12', 'MMU', '지갑', NULL, '30', '4', 'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'

내부 조인의 간결한 표현

이번에는 필요한 열만 추출하도록 해보자. mem_id, prod_name, addr, phone1,2를 추출해 보자. 주의할 점은 열 이름이 member, buy 테이블에서 같은 id가 있기 때문에 어느 테이블의 열을 추출할지 명시해야 한다.

SELECT buy.mem_id, member.mem_name, buy.prod_name, member.addr, CONCAT(member.phone1, member.phone2) '연락처'
	FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id;
    
# mem_id, mem_name, prod_name, addr, 연락처
'BLK', '블랙핑크', '지갑', '경남', '05522222222'
'BLK', '블랙핑크', '맥북프로', '경남', '05522222222'
'APN', '에이핑크', '아이폰', '경기', '03177777777'
'MMU', '마마무', '아이폰', '전남', '06199999999'
'BLK', '블랙핑크', '청바지', '경남', '05522222222'
'MMU', '마마무', '에어팟', '전남', '06199999999'
'GRL', '소녀시대', '혼공SQL', '서울', '0244444444'
'APN', '에이핑크', '혼공SQL', '경기', '03177777777'
'APN', '에이핑크', '청바지', '경기', '03177777777'
'MMU', '마마무', '지갑', '전남', '06199999999'
'APN', '에이핑크', '혼공SQL', '경기', '03177777777'
'MMU', '마마무', '지갑', '전남', '06199999999'

물론 겹치지 않는 열 이름은 그냥 입력해도 되지만 명시적으로 입력하는 것이 좋다. 하지만 이는 너무 길어지는 단점이 있다. 이런 경우 별칭을 통해 해결할 수 있다.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
	FROM buy B
    INNER JOIN member M
    ON B.mem_id = M.mem_id;
    
# mem_id, mem_name, prod_name, addr, 연락처
'BLK', '블랙핑크', '지갑', '경남', '05522222222'
'BLK', '블랙핑크', '맥북프로', '경남', '05522222222'
'APN', '에이핑크', '아이폰', '경기', '03177777777'
'MMU', '마마무', '아이폰', '전남', '06199999999'
'BLK', '블랙핑크', '청바지', '경남', '05522222222'
'MMU', '마마무', '에어팟', '전남', '06199999999'
'GRL', '소녀시대', '혼공SQL', '서울', '0244444444'
'APN', '에이핑크', '혼공SQL', '경기', '03177777777'
'APN', '에이핑크', '청바지', '경기', '03177777777'
'MMU', '마마무', '지갑', '전남', '06199999999'
'APN', '에이핑크', '혼공SQL', '경기', '03177777777'
'MMU', '마마무', '지갑', '전남', '06199999999'

내부 조인의 활용

이번에는 전체 회원의 mem_id, prod_name, addr을 출력해 보자. 결과는 보기 좋도록 회원 아이디 순으로 정렬하자.

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
	FROM buy B
    	INNER JOIN member M
        ON B.mem_id = M.mem_id
    ORDER BY M.mem_id;
    
# mem_id, mem_name, prod_name, addr
'APN', '에이핑크', '아이폰', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'APN', '에이핑크', '청바지', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'BLK', '블랙핑크', '지갑', '경남'
'BLK', '블랙핑크', '맥북프로', '경남'
'BLK', '블랙핑크', '청바지', '경남'
'GRL', '소녀시대', '혼공SQL', '서울'
'MMU', '마마무', '아이폰', '전남'
'MMU', '마마무', '에어팟', '전남'
'MMU', '마마무', '지갑', '전남'
'MMU', '마마무', '지갑', '전남'

하지만 우리가 의도한 전체 회원이 아닌 구매한 이력이 있는 회원만 출력되었다.

내부 조인은 두 테이블 모두 있는 내용만 조인되는 방식이다. 만약 양쪽 중에 한 곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다.

하지만 만약 한 번이라도 구매한 이력이 있는 회원만 추출하는 경우는 유용하게 내부 조인이 사용될 수 있다. 특히 DISTINCT를 사용하여 중복된 회원은 제거할 수 있다.

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
	FROM buy B
    	INNER JOIN member M
        ON buy.mem_id = member.mem_id
    ORDER BY mem_id;
    
# mem_id, mem_name, addr
'APN', '에이핑크', '경기'
'BLK', '블랙핑크', '경남'
'GRL', '소녀시대', '서울'
'MMU', '마마무', '전남'

2-2. 외부 조인

외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다

외부 조인의 기본

외부 조인 outer join은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다. 자주 사용되지는 않는다.

외부 조인의 형식은 다음과 같다.

SELECT <열목록>
	FROM <첫 번째 테이블(LEFT)>
    	<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT)>
        ON <조인될 조건>
    [WHERE 조건];

외부 조인을 활용하여 전체 회원의 구매 기록 출력을 해보자.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM member M
    	LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id
    ORDER BY M.mem_id;
    
# mem_id, mem_name, prod_name, addr
'APN', '에이핑크', '아이폰', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'APN', '에이핑크', '청바지', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'BLK', '블랙핑크', '지갑', '경남'
'BLK', '블랙핑크', '맥북프로', '경남'
'BLK', '블랙핑크', '청바지', '경남'
'GRL', '소녀시대', '혼공SQL', '서울'
'ITZ', '잇지', NULL, '경남'
'MMU', '마마무', '아이폰', '전남'
'MMU', '마마무', '에어팟', '전남'
'MMU', '마마무', '지갑', '전남'
'MMU', '마마무', '지갑', '전남'
'OMY', '오마이걸', NULL, '서울'
'RED', '레드벨벳', NULL, '경북'
'SPC', '우주소녀', NULL, '서울'
'TWC', '트와이스', NULL, '서울'
'WMN', '여자친구', NULL, '경기'

LEFT는 왼쪽(member)의 내용은 모두 출력되어야 함을 의미한다. 만약 RIGHT를 기준으로 위와 동일한 결과를 얻으려면 아래와 같이 해주면 된다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM buy B
    	RIGHT OUTER JOIN member M
        ON M.mem_id = B.mem_id
    ORDER BY M.mem_id;

# mem_id, mem_name, prod_name, addr
'APN', '에이핑크', '아이폰', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'APN', '에이핑크', '청바지', '경기'
'APN', '에이핑크', '혼공SQL', '경기'
'BLK', '블랙핑크', '지갑', '경남'
'BLK', '블랙핑크', '맥북프로', '경남'
'BLK', '블랙핑크', '청바지', '경남'
'GRL', '소녀시대', '혼공SQL', '서울'
'ITZ', '잇지', NULL, '경남'
'MMU', '마마무', '아이폰', '전남'
'MMU', '마마무', '에어팟', '전남'
'MMU', '마마무', '지갑', '전남'
'MMU', '마마무', '지갑', '전남'
'OMY', '오마이걸', NULL, '서울'
'RED', '레드벨벳', NULL, '경북'
'SPC', '우주소녀', NULL, '서울'
'TWC', '트와이스', NULL, '서울'
'WMN', '여자친구', NULL, '경기'

외부 조인의 활용

이번에는 회원 가입 후 한 번도 구매한 적이 없는 회원 목록을 추출해 보자. 모든 회원을 기준으로 해야하니 member 테이블을 기준으로 buy 테이블을 조인하되 B.prod_name이 NULL인 것을 추출하면 된다.

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
	FROM member M
    	LEFT OUTER JOIN buy b
        ON M.mem_id = B.mem_id
    WHERE B.prod_name IS NULL
    ORDER BY M.mem_id;
    
# mem_id, prod_name, mem_name, addr
'ITZ', NULL, '잇지', '경남'
'OMY', NULL, '오마이걸', '서울'
'RED', NULL, '레드벨벳', '경북'
'SPC', NULL, '우주소녀', '서울'
'TWC', NULL, '트와이스', '서울'
'WMN', NULL, '여자친구', '경기'

참고로 FULL OUTER JOIN은 한 곳에 데이터가 있으면 합쳐주는 기능을 한다.

2-3. 기타 조인

자주 사용되지는 않지만 가끔 유용하게 사용되는 조인으로 상호 조인과 자체 조인이 있다.

상호 조인

상호 조인(CROSS JOIN)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다. market_db의 member 테이블을 기준으로 buy 테이블을 상호 조인한다고 생각하자.

member 테이블 1~10행은 구매 테이블의 모든 행과 결합된다. 이렇게 member 테이블의 10개의 행과 구매 테이블의 12개의 행이 합해져서 총 120개의 행이 생기게 된다.

SELECT *
	FROM buy
    	CROSS JOIN member;

상호 조인은 다음과 같은 특징을 갖는다.

ON 구문을 사용할 수 없다.
결과의 내용은 의미가 없다, 랜덤으로 조인하기 때문이다.
대용량 테스트 데이터를 생성할 때 주로 사용된다.

샘플 데이터베이스인 sakila의 inventory 테이블(4581)과 world의 city 테이블(4079)를 상호조인 시키면 4581 X 4079 = 18,685,899건이 나올 것이다. COUNT로 확인해 보자.

SELECT COUNT(*) "데이터 개수" FROM sakila.inventory
	CROSS JOIN world.city;
    
# 데이터 개수
'18685899'

위 테이블은 너무 크기 때문에 조금 더 작은 sakila.actor(200)과 world.counrty(239)를 상호 조인하여 테이블을 만들고 5건만 조회해 보자.

CREATE TABLE cross_table
	SELECT *
    	FROM sakila.actor
        	CROSS JOIN world.country;

SELECT * FROM cross_table LIMIT 5;

# actor_id, first_name, last_name, last_update, Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33', 'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'
'2', 'NICK', 'WAHLBERG', '2006-02-15 04:34:33', 'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'
'3', 'ED', 'CHASE', '2006-02-15 04:34:33', 'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'
'4', 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33', 'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'
'5', 'JOHNNY', 'LOLLOBRIGIDA', '2006-02-15 04:34:33', 'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'

자체 조인

자체 조인은 자신이 자신과 조인한다는 의미이다. 조직도 테이블을 예로 한 번 확인해 보자.

위 조직도를 간단하게 직원(기본키), 직송 상관, 사내 연락처로 만들어 보자. 그리고 경리 부장의 직속 상관인 관리 이사의 연락처를 조회한다고 생각해 보자.

USE market_db;
CREATE TABLE emp_table (emp CHAR(4) PRIMARY KEY, manager CHAR(4), phone VARCHAR(8));

INSERT INTO emp_table VALUES ('대표', NULL, '0000');
INSERT INTO emp_table VALUES ('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES ('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES ('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES ('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES ('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES ('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES ('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES ('개발주임', '정보이사', '3333-1-1');

자체 조인 형식은 아래오 같다. 테이블은 1개지만 서로 다른 것처럼 사용하면 된다.

SELECT <열 목록>
	FROM <테이블> 별칭 A
    	INNER JOIN <테이블> 별칭 B
        ON <조인될 조건>
    [WHERE 검색 조건]

자 그러면 경리부장 직속 상관의 연락처를 알아내기 위해 emp_table을 emp_table A, emp_table B로 별칭을 지정하여 각각 별개의 테이블처럼 사용하면 된다.

SELECT A.emp '직원', B.emp '직속상관', B.phone '직속상관 연락처'
	FROM emp_table A
    	INNER JOIN emp_table B
      	ON A.manager = B.emp
    WHERE A.emp = '경리부장';
    
# 직원, 직속상관, 직속상관 연락처
'경리부장', '관리이사', '2222'

'혼자공부하는 SQL' 카테고리의 다른 글

5. 테이블과 뷰-1 (0)	2022.02.03
4. SQL 고급 문법-3 (0)	2022.01.29
4. SQL 고급 문법-1 (0)	2022.01.24
3. SQL 기본 문법-3 (0)	2022.01.21
3. SQL 기본 문법-2 (0)	2022.01.19

IT&보안 지킴이

4. SQL 고급 문법-2

기본미션: 외부 조인의 활용

선택미션: 내부 조인의 활용

0. 학습 목표

0. 요약

2. 두 테이블을 묶는 조인

※핵심 키워드: 일대다 관계, 조인, 내부 조인, 외부 조인, 상호 조인, 자체 조인

2-1. 내부 조인

일대다 관계의 이해

일대다 관계의 이해

내부 조인의 간결한 표현

내부 조인의 활용

2-2. 외부 조인

외부 조인의 기본

외부 조인의 활용

2-3. 기타 조인

상호 조인

자체 조인

'혼자공부하는 SQL' 카테고리의 다른 글

티스토리툴바

4. SQL 고급 문법-2

기본미션: 외부 조인의 활용

선택미션: 내부 조인의 활용

0. 학습 목표

0. 요약

2. 두 테이블을 묶는 조인

※핵심 키워드: 일대다 관계, 조인, 내부 조인, 외부 조인, 상호 조인, 자체 조인

2-1. 내부 조인

일대다 관계의 이해

일대다 관계의 이해

내부 조인의 간결한 표현

내부 조인의 활용

2-2. 외부 조인

외부 조인의 기본

외부 조인의 활용

2-3. 기타 조인

상호 조인

자체 조인

'혼자공부하는 SQL' 카테고리의 다른 글

'혼자공부하는 SQL' Related Articles

티스토리툴바