조인에 대해 알아보자 2편 (조인의 조건)

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

 

오늘은 조인의 여러 조건에 대해서 알아보도록 하겠습니다.

 

1. 서브쿼리와의 차이점

조인은 서브쿼리와 매우 유사합니다.

그러나 효과만 비슷할 뿐 수준이 다른 기술입니다.

 

tCar와 tMaker 테이블을 통해서 티볼리를 만드는 회사를 찾고,

그 회사의 공장이 어디에 있는지를 출력해야 한다고 가정합시다.

 

SELECT maker FROM tCar WHERE car = '티볼리';
SELECT factory FROM tMaker WHERE maker = '쌍용';

 

 

위의 두 쿼리를 하나로 합치면 서브쿼리가 됩니다.

동시에 조인으로도 풀어보겠습니다.

 

/* 서브쿼리 */
SELECT factory FROM tMaker
WHERE maker = (SELECT maker FROM tCar WHERE car = '티볼리');

/* JOIN */
SELECT M.factory FROM tCar C INNER JOIN tMaker M
ON C.maker = M.maker WHERE C.car = '티볼리';

 

동일한 결과를 쉽게 내뿜습니다.

 

여기까지 살펴보면 서브쿼리와 조인의 큰 차이가 없습니다.

오히려 서브쿼리가 더 쉬워보이기도 합니다.

그러나 요구사항이 복잡해지면 서브쿼리는 점점 더 복잡해지고, 조인은 더 수월합니다.

티볼리의 가격도 같이 확인하고 싶습니다.

 

/* 서브쿼리 */
SELECT factory, (SELECT price FROM tCar WHERE car = '티볼리') AS price
FROM tMaker WHERE maker = (SELECT maker FROM tCar WHERE car = '티볼리');

/* JOIN */
SELECT M.factory, C.price FROM tCar C INNER JOIN tMaker M
ON C.maker = M.maker WHERE C.car = '티볼리';

 

서브쿼리가 복잡해지기 시작했다.

 

조인문은 SELECT 문에 필드를 하나만 추가하면 끝나지만,

서브쿼리는 그렇게 하면 에러가 뜹니다.

결국 서브쿼리를 하나 더 추가해야 합니다.

이렇듯 조건이 추가될수록 서브쿼리는 복잡해지고, 조인은 쉽습니다.

 

또한 서브쿼리와 조인은 중복행이 존재할 때의 대처도 다릅니다.

임시로 티볼리에 대한 레코드를 하나 더 추가하고 동일한 쿼리를 날려보겠습니다.

 

서브쿼리는 에러가 난다.

 

조건절의 = 연산자가 둘 이상의 값을 반환하기 때문에 에러가 뜨게 됩니다.

수정하려면 IN으로 바꿔야 합니다.

그러나 조인은 조건에 맞는 모든 레코드를 조사하는 명령이기에 중복이 문제가 되지 않습니다.

 

2. 중복이 있을 때의 조인

조인은 주테이블 옆에 부테이블의 정보를 같이 보여주는 용도로 주로 사용합니다.

두 테이블을 단순히 곱한 논리곱은 경우의 수가 너무 많아 ON 절에 적절한 조건을 지정하여 필요한 레코드만 골라내야 합니다.

 

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

 

 

위는 주테이블의 모든 레코드를 다 출력하기 위해 외부 조인으로 조합하여

tCar의 모든 필드와 tMaker의 공장, 국산여부 필드를 출력했습니다.

조인은 주테이블을 출력하고 그 옆에 ON절의 조건에 맞는 부테이블의 정보를 추가하는 식입니다.

따라서 ON절에 조건만 제대로 주면 대개의 경우 조인 결과셋은 주테이블보다 크지 않습니다.

 

그러나 조인 결과의 크기가 항상 주테이블 이하인 것은 아닙니다.

PK와 FK가 정확히 연결되어 있고 PK에 중복이 없을 때만 그렇습니다.

테스트를 위해 샘플 테이블을 변경하였습니다.

 

소나타가 하나 더 생기고, 현대 공장이 더 생겼다!

 

이 상태에서 내부 조인을 걸어보겠습니다.

 

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker;

 

레코드가 8개가 나왔다-!

 

tCar에는 차가 5대 박에 없지만 조인 결과셋은 8개로 늘어났습니다.

2종류의 소나타를 3곳의 현대 공장에서 만들고 있으니 그 조합이 6개로 늘어나는 것입니다.

ON절의 조건은 차와 제조사의 공장이 같은 것만 지정할 뿐,

어떤 차인지, 어디에 있는 공장인지는 구체적으로 지정하지 않았습니다.

 

실무에서는 중복 행이 심한 예가 그리 많지는 않지만,

NULL과 값이 없는 공백은 중복 비율이 높습니다.

이 경우 별도의 조건을 추가하지 않으면 조인 결과셋은 기하급수적으로 늘어납니다.

따라서 중복이 흔한 테이블은 조인 조건에 중복을 제거하는 처리가 필수입니다.

 

3. 조인의 조건 처리

조인에 의해 생성한 결과셋도 하나의 테이블이며 따라서 WHERE 절로 출력할 조건을 지정할 수 있습니다.

두 종류의 소나타 중에 2000cc 소나타만 출력해 보겠습니다.

 

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker WHERE C.capacity = 2000;

 

 

조인문 끝에 capacity가 2000 이라는 WHERE 절을 추가하여 배기량이 2000인 차만 출력합니다.

SM5도 배기량이 2000이지만 회사 정보가 없어 내부 조인 결과에서 제외되었습니다.

이 명령은 조건절이 ON, WHERE 2개나 있는데 이 둘은 미세하게 다릅니다.

배기량 조건을 ON 절로 옮겨보겠습니다. WHERE를 AND로 바꿉니다.

 

 

두 쿼리는 동일한 결과를 출력하지만 내부 구조는 다릅니다.

WHERE는 조인한 후에 2000cc만 추려내고,

ON은 2000cc를 대상으로 조인을 합니다.

내부 조인은 조건이 일치하는 행만 보여주기 때문에 ON에 있으나 WHERE에 있으나 효과는 같습니다.

그러나 외부 조인은 다릅니다.

 

/* WHERE 조건 */
SELECT * FROM tCar C LEFT OUTER JOIN tMaker M ON C.maker = M.maker WHERE C.capacity = 2000;

/* ON 조건 */
SELECT * FROM tCar C LEFT OUTER JOIN tMaker M ON C.maker = M.maker AND C.capacity = 2000;

 

외부 조인은 결과가 다르게 나타납니다.

 

WHERE조건과 ON조건은 적용 시점이 다릅니다.

ON 조건은 조인 단계에서 부테이블의 정보를 읽을 대상을 선택하고

WHERE 절은 조인한 결과셋에서 출력할 레코드를 선택합니다.

ON 절의 조건을 먼저 적용하여 결과셋을 생성하고 WHERE 절은 출력하기 전에 적용합니다.

따라서 전자는 LEFT OUTER JOIN을 통해 두 테이블을 합치고 그 결과셋에서 WHERE절로 2000cc만 출력하였습니다.

후자는 LEFT OUTER JOIN의 조건을 주었기 때문에 2000cc만 조인을 시키고 (그러나 OUTER 조인이라 주테이블의 레코드를 전부 출력했지만) 그 결과를 출력하였습니다.

 

물론 ON 조건과 WHERE 조건을 동시에 걸어도 상관없습니다.

 

4. 인라인뷰 조인

지금까지는 tCar, tMaker 테이블 전체에 대해 조인을 걸었는데 일부 레코드만 추려낸 후 조인을 걸 수도 있습니다.

예를 들어 차중에 2000cc 초과만 대상으로 조인을 건다면 tCar 테이블에 조건을 걸어야 합니다.

다음 구문을 살펴봅니다.

 

SELECT * FROM tCar WHERE capacity > 2000 C
LEFT JOIN tMaker M ON C.maker = M.maker;

 

에러가 뜹니다..

 

WHERE 절이 JOIN 테이블 중간에 끼어드는 것은 에러가 뜹니다.

JOIN은 테이블끼리 하는 것이며 양쪽이 모두 테이블이어야 합니다.

따라서 조건까지 포함한 쿼리문을 조인 테이블로 사용하려면 인라인뷰로 정의합니다.

 

/* 인라인뷰로 정의해서 JOIN을 합니다. */
SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C
LEFT JOIN tMaker M ON C.maker = M.maker;

 

잘 출력이 됩니다-!

 

2000cc 초과의 차를 구하는 쿼리문을 괄호로 감싸 인라인뷰로 정의하고 별명 C를 붙인 후에 tMaker를 조인합니다.

별명 C를 tCar에 붙이는 것이 아니라 조건까지 다 지정한 인라인뷰에 붙인다는 것을 유의해야 합니다.

 

사실 위에서 보았던 조인 후에 WHERE 조건을 줘서 출력하는 것과 결과는 같습니다.

그러나 내부 구조는 당연히 다릅니다.

차가 100만대라면 추린 다음 조인을 하는 것과, 조인을 다 한 다음에 결과를 추리는 것은 차이가 있습니다.

 

 

오늘은 그만 알아보겠습니다.

 

 

출처

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

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