[MySQL & PHP] 10장 여러 개의 테이블 이용하기

정리

1. 여러 개의 테이블을 합쳐서 표시하기

1. 여러개의 테이블을 이용하여 레코드 조합하기

1.1 여러개의 테이블을 사용하는 예제 - 준비

다음은 사원정보 테이블 tb1과 매출정보 테이블 tb, 그리고 이번 10장에서 새롭게 등장하는 다른 영업소의 사원정보 테이블 tb2와 사원 출신지 정보 테이블 tb3입니다. 테이블 tb2와 tb3은 미리 준비해 두기 바랍니다.

이 블로그 에서는 각 테이블의 내용이 다음과 같다는 전재하에 설명하겠습니다.

그리고 아래 표를 필기 또는 프린트 하셔서 각 예제에 맞춰가며 학습하시는게 알아보기 쉽습니다.

▶︎ tb(매출정보 테이블)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ tb1(사원정보 테이블)

number	name	age
A101	강신우	40
A102	김기덕	28
A103	김민호	20
A104	문소리	23
A105	박문수	35

▶︎ tb2(다른 영업소의 사원정보 테이블)

number	name	age
A106	권영철	26
A107	김우진	24
A108	남수현	23
A109	박지수	25
A110	서연재	27

▶︎ tb3(사원 출신지 정보 테이블)

number	city
A101	서울
A102	부산
A103	대구
A104	대전
A105	인천

1.2 여러 개의 추출 결과를 함께 표시하기

여러개의 테이블에서 데이터를 추출하고, 이 데이터들을 조합해서 표시해 보겠습니다. 여러 테이블에서 추출한 데이터를 단순하게 하나의 결과로 표시할 때에는 UNION을 사용합니다.

예를 들어, 작년 고객 테이블에서 추출한 고객 데이터와 올해 고객 테이블에서 추출한 고객 데이터를 한꺼번에 표시할 때 사용합니다. 구조가 다른 테이블에서도 데이터를 추출할 수 있습니다.

기본적으로 함께 표시할 칼럼의 자료형은 일치해야 합니다. 단, 실제로 MySQL은 자료형이 달라도 추출할 수 있는 데이터는 모두 추출합니다.

명령문은 간단합니다. 'SELECT 칼럼_이름 FROM 테이블_이름'을 UNION으로 연결하기만 하면 됩니다.

2개의 테이블에서 레코드 추출하기

SELECT 칼럼_이름1 FROM 테이블_이름1 UNION SELECT 칼럼_이름2 FROM  테이블_이름2;

한줄에 모두 작성하면 알아보기 어렵습니다. 다음은 줄바꿈과 들여쓰기를 해서 작성한 예입니다.

2개의 테이블에서 레코드 추출하기(줄바꿈과 들여쓰기)

SELECT

칼럼_이름1

FROM

테이블_이름1

UNION

SELECT

칼럼_이름2

FROM

테이블_이름2;

SELECT ~ FROM ~으로 추출한 2개의 결과가 UNION으로 합쳐집니다.

먼저, 단순하게 칼럼 구조가 같은 테이블 tb1과 tb2의 레코드를 추출해 보겠습니다. 모든 칼럼을 추출하는 명령 SELECT * FROM ~ 을 UNION으로 연결합니다.

다음 명령을 실행한다.

SELECT *

FROM tb1

UNION

SELECT *

FROM tb2;

다음은 명령문을 알아보기 쉽도록 각각의 SELECT ~를 괄호 ()로 감싸서 작성한 예입니다. 이쪽이 훨씬 알아보기 쉽습니다.

(SELECT * FROM tb1) UNION (SELECT * FROM tb2);

1.3 3개 이상의 테이블을 UNION으로 연결하기

앞에서는 2개의 테이블에서 추출한 데이터를 하나로 모아서 표시했습니다. 테이블 수는 몇 개가 되어도 상관없습니다.

다음은 칼럼 number가 A102, A103, A104, A107인 레코드를 하나씩 추출해서 UNION으로 합치는 예입니다. 알아보기 쉽도록 괄호를 사용해서 작성해 보겠습니다.

(SELECT * FROM tb FROM tb WHERE number='A102')

UNION

(SELECT * FROM tb FROM tb WHERE number='A103')

UNION

(SELECT * FROM tb FROM tb WHERE number='A104')

UNION

(SELECT * FROM tb FROM tb WHERE number='A107');

결국, A101 이외의 데이터는 모두 추출되므로 다음 두 가지 명령문을 실행해도 결과는 같습니다.

SELECT * FROM tb WHERE number NOT IN ('A101');

또는,

SELECT * FROM tb WHERE number IN ('A102', 'A103', 'A104', 'A107');

하지만 표시되는 레코드의 내용은 같지만, 표시 순서는 다르니 주의하도록 합니다.

1.4 조건을 설정해서 여러 개의 추출 결과를 하나로 표시하기

UNION을 이용하여 여러 레코드에서 SELECT한 데이터를 연결할 때, 조건을 설정하려면 각 명령의 마지막에 WHERE 조건을 추가하면 됩니다.

다음 조건을 만족하는 데이터를 추출해 보겠습니다. 내용이 조금 복잡하니 집중하기 바랍니다.

① 테이블 tb에서 매출(sales)이 200 이상인 사원번호(number)

② 테이블 tb1에서 나이(age)가 35 이상인 사원번호(number)

D 주식회사의 매출정보 테이블 tb를 이용해서 '매출 성적이 우수한 사원'과 사원정보 테이블 tb1을 이용해서 '베테랑 연장자 사원'을 골라내는 내용입니다.

레코드 수가 적으니 질의를 실행하지 않아도 ①에서는 A101과 A102, ②에서는 A101과 A105가 추출될 것이라고 짐작할 수 있습니다. 그러면 실제 SQL 문으로 실행한 결과와 같은지 확인해 보겠습니다.

테이블 tb에서 sales가 200 이상인 레코드의 number와 테이블 tb1에서 age가 35 이상인 레코드의 number를 합쳐서 표시해 보겠습니다.

다음 명령을 실행한다

(SELECT

number

FROM

tb

WHERE sales>=200)

UNION

(SELECT

number

FROM

tb1

WHERE age>=35);

1.5 여러 개의 추출 결과를 합쳐서 표시하기 - 중복 허용

앞에서는 사원번호와 이름이 한번씩만 표시되었습니다. 이 결과가 당연하다고 생각할 수도 있지만, 실제로는 그렇지 않습니다. 이 결과는 여러개의 테이블에서 레코드를 추출한 것입니다. 즉 ①에서 A101과 A102가 추출되고, ②에서는 A101과 A105가 추출되기 때문에, 원래대로 라면 A101이 중복으로 표시되어야 합니다. 그러나 A101은 한 번만 표시되었습니다. 이는 추출할 때 '중복된 데이터는 제외한다.'라는 명령도 함께 실행되었기 때문입니다.

예제처럼 데이터가 10건도 안 될 때에는 별문제가 없지만, 대량의 데이터를 다룰 때에는 이렇게 중복된 데이터를 제외하는 처리를 하게 되면 무시할 수 없을 정도로 시간이 오래 걸립니다. 따라서, 대량의 데이터를 처리할 때에는 이러한 처리를 하지 않는 편이 효율적 입니다.

이럴 때에는 UNION에서 ALL을 추가로 작성합니다. 다음은 UNION ALL을 사용하는 예입니다.

다음 명령을 실행합니다.

(SELECT number FROM tb WHERE sales>=200)

UNION ALL

(SELECT number FROM tb1 WHERE age>=35);

A101은 양쪽에서 SELECT되어 2건 표시됩니다.

2. 여러 개의 테이블을 내부 조인해서 표시하기

2. 여러개의 테이블 결합하기 - 내부 조인

앞에서 설명한 UNION은 여러 개의 테이블에서 단순하게 레코드를 합치는 명령입니다. 이번에는 내부 결합(조인)을 하는 명령 JOIN을 설명하겠습니다.

2.1 다른 테이블의 데이터 이용하기

여러개의 테이블을 특정 키로 연결하는 것이 '결합'(조인)입니다. 관계형 데이터베이스다운 처리입니다. 실제로 웹상에서 운용되는 데이터베이스는 수 많은 데이터로 구성되어 있습니다. 따라서 하나의 거대한 테이블로만 처리할 수는 없습니다.

지금까지 테이블 tb와 tb1을 이용해서 설명했습니다. 회사의 '매출정보'와 '사원정보'를 분리해서 관리하는 모델입니다. 매출과 관련된 처리를 할 때에는 테이블 tb에서, 직원의 이름이 필요할 때에는 테이블 tb1에서 데이터를 가져올 수 있습니다.

▶︎ tb(매출정보 테이블)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ tb1(사원정보 테이블)

number	name	age
A101	강신우	40
A102	김기덕	28
A103	김민호	20
A104	문소리	23
A105	박문수	35

테이블 tb의 number가 A101인 사원은 나이가 40인 '강신우'입니다. 하지만, 테이블 tb만으로는 사원의 이름을 알 수가 없습니다. 그래서 매출정보 테이블 tb와 사원정보 테이블 tb1을 두 테이블의 공통 칼럼인 number를 키(Key)로 해서 결합해 보겠습니다.

2개의 테이블을 결합할 때에는 JOIN을 사용합니다.

2개의 테이블 결합하기

SELECT 칼럼_이름

FROM 테이블1

JOIN 결합할_테이블2

ON 테이블1의_칼럼=테이블2의_칼럼;

ON ~에 2개의 테이블을 연결하는 키가 되는 칼럼이 같다는 조건을 작성합니다. 예를 들어, 테이블 tb와 tb1의 경우, 칼럼 number가 공통 값이므로 이 값을 키로 설정합니다. 'ON 테이블1의 칼럼 = 테이블2의 칼럼' 부분에서 '◯ ◯ 테이블의 ✕ ✕ 칼럼'은 온점(.)을 사용해서 tb.number라고 표현합니다.

ON tb.number=tb1.number

앞선 표현은 '테이블 tb의 칼럼 number'와 테이블 tb1의 칼럼 number'를 공통 키로 설정하는 예입니다.

그러면 구체적으로 예제를 실행해 보겠습니다. 표시할 칼럼을 선택할 수도 있지만, 일단 이번에는 별표(*)를 사용해서 모든 레코드를 표시해 보겠습니다.

다음 명령을 실행합니다.

SELECT *

FROM tb

JOIN tb1

ON tb.number=tb1.number;

내부 조인

결과를 보면 테이블 tb의 칼럼 number와 일치하는 레코드를 결합해서 표시하고 있습니다(A107의 레코드가 표시되지 않는 이유는 나중에 나오는 예제를 보셔야 됩니다.).

이처럼 일치하는 레코드만 가져오는 결합을 내부 조인(또는 내부 결합)이라고 합니다. 내부 조인을 명시적으로 표시하려면 JOIN을 INNER JOIN이라고 기술하면 됩니다. 다음과 같이 INNER를 붙여도 실행 결과는 같습니다.

SELECT * FROM tb INNER JOIN tb1 ON tb.number=tb1.number;

'내부' 가 있다면 '외부'도 있지 않을까?

내부 조인과 달리, 한쪽 테이블에 일치하지 않는 레코드가 있어도 모두 표시하는 것을 외부 조인이라고 합니다.

2.2 칼럼을 선택해서 표시하기

앞에서는 별표(*)를 사용해서 모든 칼럼을 표시했습니다. 이번에는 표시할 레코드를 선택해 보겠습니다. 하지만 테이블을 결합할 때에는 단순하게 name이나 sales만 기술해서는 어떤 테이블의 칼럼인지 알 수가 없습니다.

이럴 때, 칼럼 이름을 '테이블 이름.칼럼 이름'이라고 기술해야 합니다. 예를 들어, 테이블 tb의 칼럼 sales라면, tb,sales라고 기술하면 됩니다. 또한, 칼럼은 작성한 순서대로 표시됩니다. 같은 칼럼을 몇번이고 입력해도 상관 없습니다. 예를 들어, number와 name, sales만 표시할 때에는 다음과 같이 작성합니다.

SELECT tb.number, tb1.name, tb.sales

FROM tb

JOIN tb1

ON tb.number=tb1.number;

2.3 테이블 이름에 별명 사용하기

별명(8장 에서 1.3 별명 사용하기를 검색)은 테이블 이름에도 설정할 수 있습니다. 방법은 칼럼에 별명을 설정했던 것과 마찬가지로 AS를 사용합니다.

테이블 이름에 별명 설정하기

테이블_이름 AS 별명

다음은 테이블 tb에 x라는 별명을 붙인 예입니다.

SELECT * FROM tb AS x;

테이블 이름에 별명을 설정하면 테이블을 결합할 때 편리합니다. 여기서는 편의상 'tb'와 같이 간단한 테이블 이름을 사용하고 있지만, 실제로는 많은 테이블이 period_sales2013_2와 같은 복잡한 이름으로 되어 있습니다.

여러 개의 테이블을 결합할 때, '정식 테이블 이름.칼럼 이름'이라고 작성해야 합니다. 그러나 period_sales2013_2.sales와 같은 작성 방법이라면 한눈에 알아보기 어렵습니다.

이럴 때 테이블 이름에 별명을 붙이면 '정시 테이블 이름.칼럼 이름' 부분을 '별명.칼럼 이름'으로 간단하게 작성할 수 있습니다. 예를 들어, period_sales2013_2.sales 를 x.sales라고 표현할 수가 있습니다.

그러면 매출정보 테이블 tb의 사원번호와 매출, 사원정보 테이블 tb1의 이름에 별명을 붙여서 표시해 보겠습니다.

테이블 tb의 칼럼 number와 테이블 tb1의 칼럼 number가 일치하는 레코드를 결합해서 tb의 number와 tb1의 name, tb의 sales를 표시해 보겠습니다. 단, 테이블 tb에는 x, 테이블 tb1에는 y라는 별명을 설정하겠습니다.

다음 명령을 실행합니다.

SELECT x.number, y.name, x.sales

FROM tb AS x

JOIN tb1 AS y

ON x.number=y.number;

2.4 USING을 사용해서 ON ~부분을 알기 쉽게 표현하기

지금까지는 결합 조건에 tb.number=tb1.number를 설정했습니다. 지금까지 예에서는 결합에 사용하는 키인 number라는 칼럼 이름이 양쪽에 모두 있었습니다.

하지만 칼럼 이름이 반드시 같아야 할 필요는 없습니다. 키가 될 내용만 같으면 칼럼 이름은 달라도 상관없습니다. 예를 들어, 테이블 tb1의 칼럼 이름 number가 num인 경우에도 키로 설정할 수 있습니다. 이럴 때에는 ON tb.number=tb1.num이라고 기술합니다.

이 예에서는 우연히 number라는 같은 칼럼 이름을 사용하고 있는 것입니다. 이처럼 같은 칼럼 이름을 사용할 때에는 USING(키가 되는 칼럼 이름)을 이용하면 간단하게 표현할 수 있습니다.

이를 사용하면 위쪽의 예도 다음과 같이 표현할 수 있으며, 가독성도 높아집니다.

SELECT tb.number, tb1.name, tb.sales

FROM tb

JOIN tb1

USING(number);

2.5 결합한 테이블에 WHERE 조건 설정하기

결합한 테이블에 WHERE로 조건을 설정해서 해당하는 데이터만 표시해 보겠습니다. 명령문의 맨 마지막에 WHERE로 조건을 설정하기만 하면 됩니다.

단, 결합에서느 단순하게 ~ WHERE sales>=5라고 작성하면 MySQL 입장에서는 어느 테이블에 있는 sales인지 판가름할 수가 없습니다. 칼럼 이름은 반드시 '테이블 이름.칼럼 이름'이라고 작성해야 합니다. 만약, 테이블에 별명이 설정되어 있으면 '별명.칼럼 이름'을 사용해도 됩니다.

매출정보 테이블 tb에서 매출이 100 이상인 우수 매출에 한해서, 사원정보 테이블 tb1에서 이름을 가져와 함께 표시해 봅시다. 그리고 표시할 항목에는 별명을 붙입니다. 즉, 테이블 tb에 테이블 tb1을 결합하고, 테이블 tb의 칼럼 sales의 값이 100 이상인 레코드를 표시합니다. 표시할 항목은 number와 name, sales이며, 각각 '사원번호', '이름', '매출'이라고 별명을 붙입니다.

다음 명령을 실행합니다.

SELECT tb.number AS 사원번호, tb1.name AS 이름, tb.sales AS 매출

FROM tb

JOIN tb1

USING(number)

WHERE tb.sales>=100;

2.6 여러개의 테이블에서 데이터 추출하기

JOIN할 테이블의 수에는 제한이 없습니다. 2개도 3개도 그 이상도 결합할 수 있습니다. SELECT ~ JOIN ~ ON ~에 계속해서 JOIN ~ ON ~을 추가해서 결합합니다. 단, 많은 테이블을 결합하면 그만큼 처리 시간도 오래 걸리고, 명령문도 복잡해집니다.

다음은 2개 이상의 테이블을 내부 조인하는 명령입니다.

여러 개의 테이블을 이용해서 내부 조인하기

SELECT ~ FROM

테이블_이름1

JOIN 테이블_이름2 결합_조건

JOIN 테이블_이름3 결합_조건

...

;

다음과 같은 구조의 테이블 tb3에 D 주식회사 사원의 출신지 정보가 저장되어 있습니다. 테이블 tb3은 다음과 같이 사원번호 number와 출신지를 나타내는 칼럼 city로 구성되어 있습니다. 테이블 tb3을 미리 준비해 두도록 합니다.

▶︎ tb3(사원 출신지 정보 테이블)

number	city
A101	서울
A102	부산
A103	대구
A104	대전
A105	인천

칼럼 이름	number	city
자료형	VARCHAR(10)	VARCHAR(10)

사원번호가 A101인 '강신우'는 '서울'출신입니다.

테이블 tb와 tb1, tb3에는 공통의 값을 가진 칼럼 number가 있습니다. 그러면 칼럼 number를 키로 해서 매출정보 테이블 tb에 사원정보 테이블 tb1과 사원의 출신지 정보 테이블 tb3을 결합해서 사원번호(테이블 tb의 number), 매출(테이블 tb의 sales), 이름(테이블 tb1의 name), 출신지(테이블 tb3의 city)를 표시해 보겠습니다.

다음 명령을 실행합니다.

SELECT

tb.number, tb.sales, tb1.name, tb3.city

FROM

tb

JOIN

tb1

USING(number)

JOIN

tb3

USING(number);

결합 조건에 공통 칼럼 number를 사용해서 USING(number)라고 기술했지만, ON을 사용해서 ON tb.number=tb1.number와 ON tb.number=tb3.number라고 기술할 수도 있습니다. 다음은 이렇게 작성한 예입니다.

SELECT

tb.number, tb.sales, tb1.name, tb3.city

FROM

tb

JOIN

tb1

ON tb.number=tb1.number

JOIN

tb3

ON tb.number=tb3.number;

3. 내부 조인과 외부 조인의 차이

3. 여러 테이블에서 모든 레코드를 가져와 표시하기 - 외부 조인

3.1 외부 조인이란?

테이블 tb와 tb1중심으로 외부 조인에 대해 설명하겠습니다. 테이블의 내용을 다시 한번 확인해 보겠습니다.

▶︎ tb(매출정보 테이블)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

A107 - tb1에는 존재하지 않는다.

▶︎ tb1(사원정보 테이블)

number	name	age
A101	강신우	40
A102	김기덕	28
A103	김민호	20
A104	문소리	23
A105	박문수	35

A105 - tb에는 존재하지 않는다.

사원번호 number를 키로 해서 2개의 테이블을 내부 조인하는 방법에 대해 설명했습니다. 이때, 신경 쓰이는 부분이 하나 있었는데, 사실은 테이블 tb에는 테이블 tb1의 사원정보에 존재하지 않는 number=A107이라는 사원이 한 명 있었습니다.

2.3 테이블 이름에 별명 사용하기의 내용을 다시 한번 확인해 보겠습니다.

SELECT x.number, y.name, x.sales

FROM tb AS x

JOIN tb1 AS y

ON x.number=y.number;

사실 A107은 다른 영업소의 사원이기 때문에 테이블 tb1에는 존재하지 않는 데이터입니다. 그렇기 때문에 매출이 있어도 JOIN으로 결합했을 때, 해당 레코드는 표시되지 않습니다.

또한, 테이블 tb1에는 사원번호가 A105인 박문수의 정보는 있지만, 매출 테이블에는 해당 정보가 없습니다. 즉, 명부에는 기재되어 있지만, 매출이 없어서 A105의 데이터도 표시되지 않는 것입니다.

◼︎ JOIN(또는, INNER JOIN)에 의한 내부 조인은 키가 일치하는 레코드만 추출한다.

그렇기 때문에, 테이블 tb나 tb1에만 존재하는 레코드는 무시됩니다. 하지만 레코드가 존재하는 이상 모두 표시해야 할 때도 있을 것입니다. 그럴 때에는 외부 조인(또는 외부 결합)을 사용합니다. 외부 조인은 다음과 같은 특징이 있습니다.

◼︎ 외부 조인은 일치하지 않더라도, 한쪽 테이블에 있는 모든 레코드를 표시한다.

3.2 외부 조인의 종류

외부 조인에는 결합하는 레코드 중 어느 쪽의 레코드를 모두 표시할 것인가에 따라 다음 두 종류로 나눌 수 있습니다.

LEFT JOIN

'일치하는 레코드'와 '테이블1(왼쪽)의 모든 레코드'를 표시합니다.

RIGHT JOIN

'일치하는 레코드'와 '결합할 테이블2(오른쪽)의 모든 레코드'를 표시합니다.

3.3 LEFT JOIN 사용하기

매출정보 테이블 tb(왼쪽 테이블)에 매출 실적은 있지만, 사원정보 테이블 tb1에는 존재하지 않는 사원 A107의 레코드도 포함해서 표시해 보겠습니다.

명령문은 내부 조인에서 사용했던 JOIN 대신 LEFT JOIN을 사용합니다.

LEFT JOIN

SELECT 칼럼_이름

FROM 테이블1

LEFT JOIN 결합할_테이블2

ON 테이블1의_칼럼=테이블2의 칼럼;

테이블 tb와 tb1에서 칼럼 number를 키로 해서 LEFT JOIN하여, 서로 일치하는 레코드와 테이블 tb의 모든 레코드를 표시해 보겠습니다. 단, 표시할 항목은 테이블 tb의 number와 테이블 tb1의 name입니다.

다음 명령을 실행한다.

SELECT

tb.number, tb1.name

FROM

tb

LEFT JOIN

tb1

USING(number);

테이블 tb와 tb1에서 일치하는 레코드와 왼쪽 테이블 tb의 모든 레코드가 표시되었습니다.

왼쪽 테이블 tb에만 있는 A107의 레코드가 표시되었습니다. 또한, 오른쪽 테이블 tb1에만 있는 A105의 데이터는 표시되지 않았습니다.

참고로, A107의 레코드는 표시되지만, 테이블 tb1의 칼럼 name에는 데이터가 존재하지 않기 때문에 name은 NULL이 됩니다. 즉, 매출이 있는 A107의 데이터는 표시되지만, 사원명부에 존재하지 않기 때문에 이름이 표시되지 않는 것입니다.

3.4 RIGHT JOIN 사용하기

이번에는 반대로 '일치하는 레코드'와 '결합할 오른쪽 테이블의 모든 데이터'를 표시하는 RIGHT JOIN의 예입니다. 단순히 RIGHT JOIN으로 바꾸기만 하면 됩니다.

RIGHT JOIN

SELECT 칼럼_이름

FROM 테이블1

RIGHT JOIN 결합할_테이블2

ON 테이블1의_칼럼=테이블2의 칼럼;

사원정보 테이블 tb1에는 있지만, 매출정보 테이블 tb에는 없는 레코드 즉, 사원이지만 매출이 없는 레코드도 포함해서 표시합니다. 테이블 tb와 tb1에서 칼럼 number를 키로 해서 RIGHT JOIN하여, 서로 일치하는 레코드와 테이블 tb1의 모든 레코드를 표시해 보겠습니다. 단, 표시할 항목은 테이블 tb의 number와 테이블 tb1의 name입니다.

다음 명령을 실행합니다.

SELECT

tb.number, tb1.name

FROM

tb

RIGHT JOIN

tb1

USING(number);

4. 셀프 조인을 하는 방법과 순위 매기기

4. 셀프조인

4.1 셀프 조인이란?

결합에는 자기 자신 즉, 같은 이름의 테이블을 결합하는 방법이 있습니다. 이를 셀프조인(또는 자기 결합)이라 합니다. 같은 이름의 테이블을 결합하는 것이기 때문에 실행하면 같은 칼럼 이름이 2개씩 표시됩니다. 이렇게 되면 칼럼을 구별하기가 어렵기(오류가 발생) 때문에, 셀프 조인을 할 때에는 반드시 별명(8장 1.3 별명 사용하기)을 사용합니다.

셀프 조인

SELECT 칼럼_이름 FROM 테이블_이름 AS 별명1 JOIN 테이블_이름 AS 별명2;

하나의 테이블에 2개의 별명을 설정합니다. 테이블은 하나지만 이름은 둘인 상태입니다. 사원정보 테이블 tb1을 셀프 조인하고 나서 모든 칼럼을 표시해 보겠습니다.

다음 명령을 실행합니다.

SELECT *

FROM tb1

AS a

JOIN tb1

AS b;

자기 자신과 이름만 다른 같은 테이블과 결합하는 것이기 때문에, 자신이 가진 모든 레코드에 자신이 가진 모든 레코드가 더해져서 표시됩니다.

'강신우'레코드에 '강신우'와 '김기덕', '김민호', '문소리', '박문수'가 더해지고, '김기덕'레코드에 '강신우'와 '김기덕', '김민호', '문소리', '박문수'가 더해지면, 레코드 수 ✕ 레코드 수가 되어 엄청나게 많은 수가 결합하게 됩니다. 이 예에서는 레코드가 5개이기 때문에 5✕5=25이지만, 만일 레코드 수가 100을 넘는다고 가정하면 결합하는 레코드 숫자는 엄청나게 늘어납니다.

이런 결과만 보면 셀프 조인은 무모한 방법이라고 생각할지 모르지만, 데이터베이스 세계에서는 이런 방법을 당연하게 사용하고 있습니다.

4.2 순위 정하기 1

테이블에 같은 테이블을 결합하는 이유는 무엇일까요? 셀프 조인을 한 결과에는 적어도 모든 경우의 조합이 존재합니다. 따라서 얻고자 하는 조합이 그 결과 안에 존재한다면, 그 다음에는 조건을 설정해서 필요한 것만 선택하면 됩니다.

이번에는 셀프 조인을 사용해서 순위를 정하는 방법을 소개하겠습니다. 데이터베이스 초보자라면 '순위를 정하는 것 정도는 간단하게 할 수 있지 않아?'라고 생각할지 모르겠습니다. 그러나 데이터베이스에는 Excel의 RANK()와 같은 함수가 없습니다. 데이터베이스에서 순위를 정하려면, ORDER나 GROUP 등을 조합해서 처리해야 합니다.

셀프 조인 확인하기

이번에는 사원정보 테이블 tb1에서 나이 age가 많은 순서부터 순위를 정해 보겠습니다.

위쪽에서 했던 셀프 조인을 한 결과를 다시 한번 확인하겠습니다.

다음 명령을 실행합니다.

SELECT *

FROM tb1

AS a

JOIN tb1

AS b;

예를 들어, 오른쪽의 '강신우 40'의 왼쪽에 있는 '강신우 40', '김기덕 28', '김민호 20', '문소리 23', '박문수 35'라는 데이터에 주목합니다. 이 5행 중에서 오른쪽의 '강신우 40'의 40 이상인 숫자를 왼쪽에서 찾아보면 40밖에 없습니다. '40' 이상'이란 40도 포함되므로 강신우의 순위가 1번 입니다.

마찬가지로, 오른쪽의 '김기덕 28'의 왼쪽에 있는 '강신우 40', '김기덕 28',... '박문수 35'중에서 오른쪽의 '김기덕 28'의 28 이상인 왼쪽의 숫자는 40, 35, 28입니다. 그 중 김기덕의 순위는 3번 입니다.

이처럼 오른쪽이 같은 값으로 구성된 5개의 레코드 중에서 오른쪽 age 이상인 왼쪽 age의 개수가 순위가 됩니다.

즉, 셀프 조인을 하고 나서 왼쪽의 age가 오른쪽의 age 이상인 레코드의 수를 number의 그룹별로 개수를 조사하면 됩니다. 개수는 COUNT(*)로 계산합니다. 이것을 힌트로 SQL 문을 생각해 봅시다.

기본은 위에 배운 SELECT FROM tb1 AS a JOIN tb1 AS b;입니다. 해답을 보기 전에 스스로 한번 생각해 보기 바랍니다.

그러면 테이블 tb1의 age를 값이 큰 순서대로 순위를 정하고, 칼럼 name, age, 순위를 표시해 보겠습니다.

다음 명령을 실행합니다.

SELECT a.name, a.age, COUNT(*)

FROM tb1 AS a

JOIN tb1 AS b

WHERE a.age<=b.age

GROUP BY a.number

먼저, 셀프 조인이기 때문에 기본 명령문은 SELECT * FROM tb1 AS a JOIN tb1 AS b;입니다.

여기에 WHERE로 조건 a.age<=b.age를 설정합니다. 그러면, a.number의 그룹별로 b.age가 a.age 이상인 레코드가 추출되는데, 이렇게 추출된 레코드의 개수를 COUNT(*)로 계산한 값이 순위가 됩니다.

5. 하위 질의를 사용하여 2단계로 추출하기

5. SELECT한 레코드에서 또 SELECT하기 - 하위 질의

5.1 하위 질의를 사용할 수 있는 버전

하위 질의를 설명하기 전에 중요한 사항이 있는데, 하위 질의를 사용할 수 있는 MySQL의 버전은 4.1 이상입니다. 이전 버전을 사용할 때는 여기서 소개하는 예제가 실행되지 않습니다.

5.2 하위 질의란?

하위질의는 서브 쿼리라고도 합니다. 하위 질의를 사용하면, 질의를 실행해서 추출한 데이터를 이용해서 다시 질의를 실행하는 2단계의 처리를 할 수 있습니다. 예를 들어, 1단계에서 매출 테이블에서 매출이 200 이상인 사원번호를 추출하고, 2단계에서 추출한 사원 번호에 해당하는 이름을 추출한다고 했을 때, 1단계에서 사용하는 질의를 하위 질의라고 합니다.

1단계에서 실행하는 하위 질의 결과는 값이나 칼럼, 레코드 등 다양한 형태의 데이터입니다.

하위 질의를 사용하는 처리 대부분은 내부 조인 등으로 대체할 수가 있습니다. 하지만, 단계별로 처리하는 하위 질의를 사용하는 것이 훨씬 효율적입니다.

5.3 최댓값 표시하기 - 값을 반환하는 하위 질의 1

다음과 같은 문제는 과연 어떻게 처리하면 좋을까요?

◼︎ 테이블 tb에서 칼럼 sales의 최대값이 있는 레코드를 표시한다.

'매출정보 테이블 tb에서 매출이 가장 높은(sales가 최대) 사원의 데이터를 표시하시오.' 라는 문제입니다. 칼럼의 최댓값을 구할 때에는 MAX() 함수를 사용합니다. 다음 테이블 tb의 구조를 보고 잠깐 생각해 보겠습니다.

▶︎ tb(매출정보 테이블)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

테이블의 주고도 확인했고, 최댓값을 구하는 함수도 알았으니 최댓값은 간단하게 구할 수 있다고 생각하셨나요? 아마도 많은 분들이 다음과 같은 명령문을 생각했을 것입니다.

SELECT * FROM tb WHERE sales=MAX(sales);

하지만, 이 명령문을 실행하면 오류가 발생합니다.

단순하게 MAX(sales)라고 작성해서는 sales의 최댓값을 계산할 수 없습니다. 만약, MAX(sales)를 계산하려면, 먼저 다음 명령을 실행해야 합니다.

SELECT MAX (sales) FROM tb;

최댓값에 해당하는 레코드를 추출하려면, 먼저 이 명령으로 최댓값을 구하고 그 값을 사용해서 테이블 tb에서 다시 값을 추출해야 합니다.

여기에서, 하위 질의가 활약하게 됩니다. 먼저, 1단계에서 해당 질의로 MAX(sales)의 값을 구합니다. 그러고 나서 2단계에서 MAX(sales)로 구한 최댓값을 포함한 칼럼 sales의 레코드를 SELECT합니다.

SELECT * FROM tb WHERE sales IN (1단계에서 처리한 결과)

'~을 포함한다'라는 조건 WHERE ~ IN을 사용하는 방법은 8장 3.3 비교 연산자를 참고합니다. 1단계의 하위 질의는 괄호() 안에 기술합니다. 괄호를 사용하지 않으면 오류가 발생하니 주의하도록 합니다.

그러면 실제로 예를 들어 설명하겠습니다. 테이블 tb에서 칼럼 sales의 값이 가장 큰 레코드를 표시합니다.

다음 명령을 실행합니다.

SELECT *

FROM tb

WHERE sales

IN (SELECT MAX(sales) FROM tb);

참고로, 평균 나이는 (40+28+20+23+35)/5의 결과값인 29.2입니다. 따라서 29.2 이상인 40세의 강신우, 35세의 박문수가 표시됩니다.

5.5 IN 사용하기 - 칼럼을 반환하는 하위 질의

이번에는 하위 질의가 칼럼을 반환하는 예입니다. 1단계의 하위 질의에서 조건에 일치하는 칼럼을 반환하고 2단계에서 그 칼러을 포함한 값을 추출합니다. 이 때에는 다음과 같이 IN을 사용합니다.

IN을 사용한 하위 질의

SELECT 표시할_칼럼 FROM 테이블_이름

WHERE 칼럼_이름 IN (SELECT를_이용한_하위_질의로_추출한_칼럼);

1단계의 질의(하위 질의)는 반드시 괄호 안에 기술해야 합니다. 1단계에서 추출한 칼럼에 포함된다는(IN) 조건으로 2단계 질의를 실행합니다. 여기에서는 다음 작업을 실행합니다.

◼︎ 매출이 200 이상인 사원 이름을 표시한다.

이 경우 구체적인 처리는 다음과 같습니다.

◼︎ 매출정보 테이블 tb에서 매출(sales)이 200 이상인 사원번호( number)를 추출하고, 사원정보 테이블 tb1에서 해당 사원 이름(name)을 표시한다.

즉, 1단계에서 실행할 하위 질의는 테이블 tb에서 sales>=200의 조건에 일치하는 사원번호 number를 추출하는 것입니다.

SELECT number FROM tb WHERE sales>=200;

그리고 2단계에서는 1단계에서 추출한 number를 포함하는 레코드를 tb1에서 추출합니다.

SELECT * FROM tb1 WHERE 조건

이 '조건'부분에 1단계에서 추출한 A101과 A102를 포함한다는 내용을 기술합니다. 이때, WHERE ~ IN ~을 사용합니다.

하위 질의를 사용해서 테이블 tb에서 매출(sales)이 200 이상인 number를 추출하고, 테이블 tb1에서 해당하는 레코드를 표시해 보겠습니다.

다음 명령을 실행합니다.

SELECT *

FROM tb1

WHERE number

IN (SELECT number FROM tb WHERE sales>=200);

예상대로 A101과 A102가 표시되었습니다.

하위 질의와 내부 조인의 실행 결과 - 비슷한 듯 다르다!

하위 질의와 내부 조인은 많이 비슷합니다. 예를 들어, 매출정보 테이블 tb에 존재하는 사원번호 number와 일치하는 사원정보를 테이블 tb1에서 찾아서 사원번호(number)와 이름(name)을 표시하는 경우, 하위 질의와 내부 조인으로 각각 표현하면 다음과 같습니다.

하위 질의

SELECT number, name

FROM tb1

WHERE number

IN (SELECT number FROM tb);

내부 조인

SELECT tb1.number, tb1.name

FROM tb1

JOIN tb

ON tb1.number=tb.number;

실행 결과는 다릅니다. 하위 질의를 사용하면 테이블 tb에 존재하는 number를 먼저 검색하고, 테이블 tb1에서 일치하는 레코드만 표시합니다. 이에 비해 내부 조인은 테이블 tb에 있는 모든 레코드를 표시합니다.

5.6 IN 대신에 등호를 사용하면 오류가 발생한다?

앞에서 소개한 IN을 사용하지 않고 다음과 같이 등호(=)를 사용해도 되지 않을까 하고 생각한 분도 있을 것입니다.

SELECT *

FROM tb1

WHERE number = (SELECT number FROM tb WHERE sales>=200);

앞의 명령문을 실행하면 'Subquery returns more then 1 row'(하위 질의가 1건 이상 검색되었다!)라는 오류가 표시됩니다. 'number가 ✕ ✕와 정확하게 일치한다.'라는 조건이라면 등호를 사용해도 상관 없지만, 이 예에서는 여러 개의 레코드가 추출되기 때문에, '...중에 어느 것'이라는 의미의 IN을 사용해야 합니다.

물론, 해당 레코드가 1건밖에 없을 때에는 등호를 사용해도 오류가 발생하지는 않습니다. 예를 들어, 다음은 LIMIT를 사용해서 1건만 추출하는 예입니다. 이렇게 하면, 적어도 오류는 피할 수 있습니다.

SELECT *

FROM tb

WHERE number

= (SELECT number FROM tb1 WHERE sales>=200 LIMIT 1);

그러나 이렇게 하면 안타깝게도 A101이 추출될지 A102가 추출될지 알 수 엇어서 LIMIT는 도움이 되지 않습니다.

값이 큰 순서로 정렬해서 맨 앞의 1개만 표시할 수는 있습니다. 다음은 ORDER BY를 사용해서 '매출이 가장 높은 사원'이라는 조건으로 데이터를 추출하는 예입니다. 1단계에서는 테이블 tb에서 sales의 최댓값에 해당하는 number를 검색하고, 2단계에서는 테이블 tb1에서 1단계에서 추출한 값에 해당하는 레코드를 추출하고 있습니다.

SELECT *

FROM tb1

WHERE number

= (SELECT number FROM tb ORDER BY sales DESC LIMIT 1);

LIMIT로 1건만 추출했기 때문에 등호를 사용해도 오류가 발생하지 않습니다.

5.7 EXISTS를 사용해서 존재하는 레코드를 대상으로 검색하기

위에서 설명한 하위 질의는 WHERE ~ IN (SELECT ...) 형태였습니다. 이 형태는 1단계의 하위 질의가 해당하는 칼럼 데이터를 반환하는 것이었습니다. 예를 들면, 위의 예에서는 'A101, A102의 칼럼'이라는 칼럼 데이터가 반환되고, 그 값을 포함하는 레코드가 추출 대상이었습니다.

이에 비해, 특정 칼럼이 아닌 '대상이 되는 레코드가 존재한다.'라는 정보를 반환하는 것이 EXISTS입니다.

실제 예를 들어 설명하겠습니다. 사원정보 테이블 tb1에는 매출정보 테이블 tb에 존재하지 않는 사원번호를 가진 레코드도 포함되어 있습니다. 바로 A105입니다. 즉, A105 사원은 매출이 없다는 뜻입니다.

그러면 사원정보 테이블에서 매출이 있는 사원의 레코드를 하위 질의로 표현해 보겠습니다. 구체적으로는 다음과 같습니다.

◼︎ 테이블 tb에 존재하는 레코드를 추출하고, 테이블 tb1에서 해당하는 레코드를 표시한다.

먼저, 사원정보 테이블 tb1의 레코드를 표시합니다.

SELECT * FROM tb1

그리고, '매출이 있는 사원만'이라는 조건은 사원번호 number가 테이블 tb와 tb1 양쪽에 모두 존재한다는 의미이므로 다음과 같이 표현합니다.

WHERE tb.number=tb1.number

1단계에서는 테이블 tb에서 이 조건에 일치하는 레코드를 검색합니다.

SELECT * FROM tb WHERE tb.number=tb.number

이 명령은 특정 칼럼이 아니라 조건에 맞는 레코드를 추출하는 것입니다. 그리고 2단계에서는 EXISTS를 사용해서 테이블 tb1에 존재하는 해당 헤코드만 표시합니다.

다음 명령을 실행합니다.

SELECT *

FROM tb1

WHERE EXISTS

(SELECT * FROM tb WHERE tb.number=tb1.number);

테이블 tb에는 A105의 number가 존재하지 하지 않기 때문에 표시되지 않습니다.

5.8 NOT EXISTS 사용하기

반대로 NOT EXISTS는 하위 질의로 추출되지 않는 레코드를 대상으로 합니다. 이번에는 '매출이 없는 사원'의 레코드 만을 대상으로 하위 질의를 실행해 보겠습니다.

위에 EXISTS를 NOT EXISTS로 바꾸기만 하면 됩니다. NOT EXISTS를 사용해서 테이블 tb에 존재하지 않는 레코드의 number만 추출하고, 테이블 tb1에서 해당하는 레코드를 표시하겠습니다.

다음 명령을 실행합니다.

SELECT *

FROM tb1

WHERE NOT EXISTS

(SELECT * FROM tb WHERE tb.number=tb1.number);

실행 결과를 통해 매출이 없는 사원은 사원번호 A105, 나이가 35세인 박문수라는 것을 확인했습니다.

5.9 순위 정하기 2

표를 계산할 수 있는 소프트웨어라면 순위를 정하는 것은 간단합니다. 그러나 RDMBS에서 순위를 입력하는 것은 꽤 복잡한 처리라고 설명한 바 있습니다.

그런데 하위 질의를 사용하면 다양한 방법으로 순위를 매길 수 있습니다. 게다가, 셀프 조인보다 쉽게 처리할 수 있습니다. 이번에는 하위 질의를 사용해서 순위를 정하는 방법을 한 가지 소개하겠습니다.

매출정보 테이블 tb를 이용하여 매출 순위를 정해 보겠습니다. 이번에는 다른 테이블을 준비해서 칼럼에 순위를 입력해 보겠습니다. 조금 복잡하긴 하지만 차근차근 따라 해보기 바랍니다. 다음과 같은 순서로 진행하겠습니다.

연속 번호 기능을 설정한 테이블에 sales의 순서대로 정렬한 레코드를 삽입한다.

⬇︎

자동으로 입력되는 연속 번호가 순위가 된다.

구체적으로는 다음과 같은 처리를 합니다.

테이블 tb와 같은 구조의 테이블 tb_rank를 생성한다.

⬇︎

테이블 tb_rank에 연속 번호 기능을 설정한 칼럼 rank를 추가한다.

⬇︎

테이블 tb를 대상으로 sales의 순서대로 SELECT하는 하위 질의를 실행한다.

⬇︎

하위 질의의 결과를 테이블 tb_rank에 INSERT한다.

이렇게 하면 테이블 tb_rank의 칼럼 rank에 입력된 연속 번호 순위가 됩니다.

자, 그럼 순위를 입력하는 질의는 구체적으로 어떻게 작성하면 될까요? 연속 번호 기능과 하위 질의를 사용하는 방법을 다시 한번 떠올려 보기 바랍니다.

테이블 tb의 칼럼의 number와 sales, month로 구성되어 있습니다. 여기에 칼럼 rank를 추가한 테이블 tb_rank를 만들고, 테이블 tb의 레코드를 복사합니다. 그러고 나서 칼ㄹ럼 rank에 sales의 순위를 입력해 보겠습니다.

① 다음 명령을 실행합니다.

CREATE TABLE tb_rank LIKE tb;

② 다음 명령을 실행합니다.

ALTER TABLE tb_rank ADD rank INT AUTO_INCREMENT PRIMARY KEY;

③ 다음 명령을 실행합니다.

INSERT INTO tb_rank

(number, sales, month)

(SELECT

number, sales, month

FROM tb

ORDER BY sales DESC);

앞의 ① ~ ③은 각각 다음과 같은 처리를 합니다.

① 테이블 tb의 칼럼 구조만 복사해서 테이블 tb_rank를 생성한다.

② 테이블 tb_rank에 연속 번호 기능을 설정한 칼럼 rank를 추가한다.

③ 하위 질의를 사용해서 테이블 tb를 칼럼 sales의 내림차순으로 정렬하고, number와 sales, month를 테이블 tb_rank에 추가한다.

③에서 칼럼 rank 에 연속 번호가 자동으로 입력되는데 이 번호가 순위가 됩니다.

다음 명령을 실행합니다.

SELECT * FROM tb_rank;

체크!

★ UNION을 사용해서 여러 개의 테이블로부터 데이터를 SELECT할 수 있다.

★ 키를 지정해서 내부 조인을 한 여러 개의 테이블로부터 데이터를 SELECT할 수 있다.

★ LEFT JOIN이나 RIGHT JOIN을 사용해서 여러 개의 테이블로부터 필요한 데이터를 SELECT 할 수 있다.

★ 하위 질의의 의미를 이해하고 있다.

★ 하위 질의로 얻은 값을 조건으로 원하는 데이터를 추출할 수 있다.