조인에 대해 알아보자 1편 (feat. CROSS JOIN, INNER JOIN, OUTER JOIN)

안녕하세요, 끙정입니다.

 

오늘은 DBMS의 꽃, 조인에 대해서 알아보려고 합니다.

JOIN 또한 서브 쿼리와 같이 많은 연구와 학습이 필요합니다.

출발해보도록 하겠습니다.

 

1. 조인의 정의

DB에 대해서 배우신 분이라면 정규화를 알고 계실 겁니다.

정규화는 엔터티 별로 테이블을 잘게 쪼개 중복을 방지하고 논리적인 구조를 만듭니다.

정규화를 통해 테이블을 여기저기로 찢다 보니

몇몇의 정보를 한 번에 보고 싶을 때 두 개 이상의 테이블을 연속적으로 조회하는 경우가 많아집니다.

서브 쿼리도 좋은 방법이지만 더 일반적인 해결책은 바로 조인입니다.

 

 

위의 두 테이블은 maker라는 필드를 각각 외래 키로 가지고 있습니다.

소나타의 제조사의 공장이 있는 지역을 알고 싶다면,

조인을 통해 두 테이블의 정보를 조합해서 읽어야 합니다.

 

이렇듯 조인은 복수 개의 테이블에서 조건에 맞는 데이터를 조회하는 방법입니다.

방법에 따라 단순 조인 CROSS JOIN, 내부 조인 INNER JOIN, 외부 조인 OUTER JOIN 등으로 분류하며,

외부 조인은 왼쪽 LEFT JOIN, 오른쪽 RIGHT JOIN, 완전 FULL JOIN 등으로 세분화합니다.

하나씩 실습을 해보겠습니다.

 

2. 단순 조인 CROSS JOIN

단순 조인은 두 개 이상의 테이블을 특별한 조건 없이 논리곱으로 조합합니다.

FROM 절에 출력 대상 테이블의 목록을 콤마로 구분하여 나열합니다.

 

SELECT * FROM tCar, tMaker;

SELECT * FROM tCar CROSS JOIN tMaker;

 

 

ANSI 표준은 단순 조인을 크로스 조인으로 정의하여 공식화해두었습니다만 이름만 다를 뿐 같습니다.

콤마 대신에 CROSS JOIN을 넣어도 동일한 결과가 출력됩니다.

 

그런데 이 단순 조인은 논리곱 Cartesian Product라고 하여 Cardinality를 전부 곱한 결과를 내뱉습니다.

tCar의 레코드 4개와 tMaker의 레코드 4개를 곱해서 16개의 레코드가 출력되는 것이지요.

실질적으로 큰 의미가 없는 출력입니다.

그러나 조인의 내부 동작을 살펴보는 과정에서는 도움이 됩니다.

다음 쿼리를 계속해서 살펴보겠습니다.

 

WHERE 조건절을 통해서 tCar의 회사명과 tMaker의 회사명이 일치하는 것만 표시하도록 지정해보겠습니다.

 

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

 

이건 의미가 있습니다-!

 

16개의 레코드 중에서 두 테이블의 회사가 동일한 레코드만 출력됩니다.

이로써 소나타, 티볼리, A8의 제조사에 따른 공장 소재지, 국산차 여부를 한눈에 볼 수 있습니다.

 

그런데 maker 필드가 중복으로 나타나는 문제가 있습니다.

효율적이지 않으니 SELECT 문으로 필드를 지정해보겠습니다.

 

SELECT tCar.car, tCar.price, tMaker.maker, tMaker.factory
FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

 

훨씬 깔끔합니다-!

 

만일 한쪽 테이블의 정보가 아주 많으면 다 출력할 테이블에 대해서만 * 을 써도 됩니다.

아래와 같습니다.

 

SELECT tCar.*, tMaker.factory /* tCar의 모든 필드와 tMaker의 factory 필드 출력 */
FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

 

쿼리가 훨씬 간단합니다-!

 

3. 테이블 별명

여러 테이블을 조인할 때는 tCar.car, tMaker.factory처럼 필드명 앞에 소속 테이블을 붙여야 합니다.

그러나 일일이 적으면 귀찮고 시간이 많이 걸립니다.

만일 소속이 분명하다면 테이블 명을 생략해도 상관은 없습니다.

그러나 여러 테이블에 중복해서 존재하는 필드는 소속 테이블을 선언해줘야 합니다.

 

maker는 두 테이블에 모두 존재하기 때문에 에러가 뜬다!

 

그러나 다중 조인을 쓰거나 필드가 많아지면 어느 필드가 어느 테이블 소속인지 헷갈리기 쉽습니다.

그래서 소속 테이블을 반드시 명시해주는 것이 좋습니다.

그러나 일일이 테이블 명을 적어주는 건 비효율적입니다.

그래서 별명을 사용하는 것이 좋습니다.

 

SELECT C.car, C.price, M.maker, M.factory
FROM tCar C, tMaker M WHERE C.maker = M.maker;
/* tCar를 C로 tMaker를 M으로 */

 

아주 속이 시원합니다.

 

별명을 붙이는 기준은 없지만 기왕이면 간략하면서 테이블을 대표하는 것이 좋습니다.

tCar는 C, tMaker는 M으로 선언하면 기억하기 좋습니다.

 

주의할 것은 SQL Server, MariaDB와는 다르게 오라클은 테이블의 별명에 AS를 허락하지 않습니다.

따라서 호환성을 위해서는 테이블 별명에는 AS를 쓰지 않는 것이 좋습니다.

 

4. 내부 조인 INNER JOIN

내부 조인 INNER JOIN은 각 테이블의 필드 값을 비교하여 조건에 맞는 레코드만 선택적으로 가져옵니다.

FROM 절의 INNER JOIN 양쪽에 조인할 테이블명을 지정하고 ON 다음에 조인 조건을 작성합니다.

 

SELECT 필드 FROM A [INNER] JOIN B ON 조건;

 

조인의 디폴트가 내부 조인이기 때문에 INNER 키워드는 생략해도 괜찮습니다.

 

SELECT C.car, C.price, M.maker, M.factory
FROM tCar C INNER JOIN tMaker M
ON C.maker = M.maker;

 

조인의 테이블 순서는 무관하다.

 

두 테이블에서 maker가 같은 현대, 쌍용, Audi 세 개의 결과를 출력합니다.

조인의 순서는 상관이 없습니다.

위에서 본 단순 조인의 WHERE 조건절과 동일하다는 것을 알 수 있습니다.

단순 조인은 표준에서 권장하지 않아 가급적이면 내부 조인을 쓰는 것이 바람직합니다.

 

조인 조건문인 ON에서 주로 외래 키를 비교하기 때문에 양쪽 테이블의 필드명이 같은 경우가 많습니다.

이 경우 ON 절 대신 USING (조건 필드) 식으로 더 간단히 지정할 수 있습니다.

 

SELECT C.car, C.price, maker, M.factory /* USING(maker) 이기에 maker는 소속을 밝히지 않는다. */
FROM tMaker M INNER JOIN tCar C
USING(maker);

 

MariaDB는 M.maker를 허용하고, SQL Server는 USING 자체를 거부한다.

 

그러나 DBMS마다 USING을 허용하는 기준이 다른데,

오라클은 USING으로 사용한 필드명을 출력할 때는 소속을 밝히지 않습니다.

두 테이블이 동일한 필드이기에 논리적으로 어느 소속인지를 밝힐 필요가 없다는 주장입니다.

그러나 MariaDB는 위와 같이 M.maker라고 선언해도 에러가 뜨지 않습니다.

SQL Server는 USING을 아예 지원하지 않습니다.

 

5. 외부 조인 OUTER JOIN

내부 조인은 ON 조건에 맞는 레코드만 출력하며 그 외의 레코드는 제외합니다.

조인의 결과가 자꾸 3개의 레코드만 나왔던 이유는 각 테이블의 1개 값은 서로의 테이블과 조건에 맞지 않았기 때문입니다.

외부 조인은 내부 조인과는 달리 조건에 맞지 않아도 출력합니다.

먼저 왼쪽 조인 LEFT OUTER JOIN을 살펴보겠습니다.

 

SELECT C.car, C.price, M.maker, M.factory
FROM tCar C LEFT OUTER JOIN tMaker M
ON C.maker = M.maker;

 

내내 보이지 않던 SM5가 나타났다-!

 

왼쪽, 오른쪽 조인 모두 OUTER JOIN 이기 때문에 LEFT OUTER JOIN을 LEFT JOIN으로 써도 무방합니다.

위 결과를 보면 알 수 있듯이,

외부 조인은 주 테이블의 모든 레코드를 보여주고,

조건을 만족하는 부 테이블의 필드를 같이 출력합니다.

SM5는 왼쪽 주 테이블(tCar)에는 존재하면서 오른쪽 부 테이블(tMaker) 에는 없습니다.

일단 출력하되 값이 없는 필드는 NULL로 표시합니다.

 

외부 조인은 어떤 테이블이 주 테이블이 되는가,

즉 모두 보여줄 테이블이 어떤 것인가에 따라

왼쪽 LEFT OUTER JOIN, 오른쪽 RIGHT OUTER JOIN, 완전 조인 FULL OUTER JOIN으로 분류합니다.

오른쪽 조인과 완전 조인도 살펴보겠습니다.

 

/* RIGHT JOIN */
SELECT C.car, C.price, M.maker, M.factory
FROM tCar C RIGHT OUTER JOIN tMaker M
ON C.maker = M.maker;

/* FULL JOIN */
SELECT C.car, C.price, M.maker, M.factory
FROM tCar C FULL OUTER JOIN tMaker M
ON C.maker = M.maker;

 

오른쪽 테이블의 레코드를 모두 보여주는 RIGHT JOIN, 양쪽 테이블의 레코드를 전부 보여주는 FULL JOIN

 

왼쪽, 오른쪽이 있으니 양쪽도 정의되어 있지만 실무적으로 잘 쓰이지 않습니다.

오라클과 SQL Server는 지원하지만 MariaDB는 FULL JOIN을 지원하지 않습니다.

그만큼 쓸 일이 거의 없다는 의미입니다.

굳이 필요하다면 아래와 같이 사용합니다.

 

왼쪽 외부 조인 USING 오른쪽 외부 조인

 

왼쪽, 오른쪽 외부 조인은 방향만 다를 뿐 동작은 사실상 같습니다.

결국 INNER JOIN과 LEFT JOIN을 주로 사용합니다.

 

6. 다중 조인

조인은 두 개의 테이블을 조합합니다.

한꺼번에 3~4개의 테이블을 조인하지 않으며 딱 두 개만 읽습니다.

그러나 조인을 중첩하면 여러 개의 테이블을 조인할 수 있습니다.

이를 다중 조인이라고 합니다.

 

3중 조인을 해보겠습니다.

 

SELECT * FROM tCar c /* tCar 를 기준으로 */
INNER JOIN tMaker M ON C.maker = M.maker /* tMaker와 조인하고 */
INNER JOIN tCity T ON M.factory = T.name; /* tMaker의 공장과 tCity 를 조인 */

 

다행히 일치하는 레코드가 하나 있네요...

 

이를 통해 세 테이블의 정보를 한꺼번에 볼 수 있습니다.

 

외부 조인을 통해서 중첩시켜보겠습니다.

 

SELECT * FROM tCar c /* 주테이블은 tCar */
LEFT OUTER JOIN tMaker M ON C.maker = M.maker
LEFT OUTER JOIN tCity T ON M.factory = T.name;

 

NULL 이 많다.

 

내부 조인과 외부 조인을 혼합해서 쓰는 것도 물론 가능합니다.

외부 조인은 주테이블이 기준이므로 조인 순서에 따라 값이 달라지니 주의해야 합니다.

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24