1. JOIN

- inner join 또는 left outer join을 왼쪽 테이블에서 특정 조건에 부합하는 레코드를 추출/필터링하는 연산으로 활용할 수 있다.

  • left outer join의 경우, join 연산 수행 후 나타나는 결과 테이블 안에 오른쪽 테이블에서 유래한 컬럼의 값이 null로 돼있는 레코드만 가져오도록 where 조건절을 작성하는 식으로 필터링을 할 수 있다.

- 큰 규모의 테이블 여럿을 JOIN하는 작업이 필요한 쿼리의 경우, JOIN하기 전에 전처리를 하고 JOIN을 하는 방식이 있고 JOIN 전에 전처리를 하지 않고 일단 JOIN한 다음에 후처리를 하는 방식이 있다. JOIN 전 전처리를 하는 방식은 쿼리를 복잡하게 만들 수 있고 이 경우 디버깅에 불리하다는 단점이 있으나, JOIN 후 후처리를 하는 방식은 불필요한 연산을 너무 많이 해야 하는 경우가 발생한다는 문제가 있다.

1) CROSS JOIN

1

SELECT * FROM table1 CROSS JOIN table2;

- Cartesian product를 CROSS JOIN이라고도 한다. CROSS JOIN 예약어를 생략하고 쉼표로 대체하는 것도 가능하다.

2) INNER JOIN

1

SELECT table1.field1 FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2

- FROM 예약어 뒤 두 relation 사이에 INNER JOIN 예약어를 사용하여 inner join 연산을 수행할 수 있다. INNER 예약어는 생략 가능하며, INNER JOIN 예약어를 모두 생략하고 쉼표로 대체하더라도 그 뒤에 ON, WHERE 같은 조건절이 있다면 INNER JOIN 연산을 수행한다.

- inner join 연산을, ‘table1의 레코드들을 table2의 레코드들을 사용해 필터링하는 연산’이라고 생각할 수도 있다.

- inner join 연산을 위한 조건절의 예약어로는 ON과 WHERE 모두 사용 가능하며, inner join에서는 얻는 결과와 성능이 두 예약어 모두 동일하다.

3) NATURAL JOIN

1

SELECT table1.field1 FROM table1 NATURAL JOIN table2

- NATURAL JOIN 예약어를 사용하는 경우, 조건절을 사용하지 않더라도 이름이 같은 컬럼을 스스로 찾아 이를 기준으로 inner join 연산을 수행한다.

4) OUTER JOIN(LEFT JOIN, RIGHT JOIN, FULL JOIN)

- LEFT/RIGHT OUTER JOIN 연산의 경우 왼쪽/오른쪽 테이블의 모든 레코드에 대응되는 오른쪽/왼쪽 테이블의 레코드를 찾아 새로운 레코드를 만든다. 이때, 왼쪽/오른쪽 테이블의 레코드 하나에 대응되는 오른쪽/왼쪽 테이블의 레코드가 여럿일 경우, 왼쪽/오른쪽 테이블의 그 레코드가 여럿 복제되어 오른쪽/왼쪽 테이블의 그 모든 레코드들과 함께 새로운 레코드가 만들어진다. (즉, LEFT/RIGHT OUTER JOIN은 오른쪽/왼쪽 테이블의 일부 레코드는 없을 수 있으나 조건식을 만족하는 레코드들은 빠짐없이 결과 테이블에 추가된다.)

  • LEFT JOIN과 RIGHT JOIN은 기능상 완전히 동일하나, 경우에 따라 선호되는 JOIN이 다르다. 가잠 기본이 되는 테이블을 가장 먼저 쓰고 그 오른쪽/아래에 이를 수정하는 연산을 덧붙이는 방식을 선호되는 상황이 많아 LEFT JOIN이 좀 더 많이 쓰이는 경향이 있으나 반드시 그런 것은 아니다.

- WHERE 키워드를 사용해도 OUTER JOIN한 결과에서 원하는 레코드를 필터링할 수 있으나, 되도록 ON 키워드를 통해 각 테이블의 컬럼 값의 일치 여부를 조사해야 한다. 그래야 OUTER JOIN 연산 전에 두 relation에서 해당 조건을 충족하는 레코드만 추출하고 그 다음에 이로써 OUTER JOIN 연산을 수행하기 때문이다.

* LEFT JOIN을 이용한 예시

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT 
    c.CAR_ID,
    COALESCE(tmp.AVAILABILITY, '대여 가능') AS AVAILABILITY
FROM (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) c
LEFT JOIN (
    SELECT 
        CAR_ID, 
        '대여중' AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE
) tmp
ON c.CAR_ID = tmp.CAR_ID
ORDER BY c.CAR_ID DESC;

- 이 문제는 각 CAR_ID마다 대여(예정)일, 반납(예정)일이 기록돼 있는 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 각 자동차들이 2022-10-16에 대여 가능한지 여부를 출력하는 문제다. DISTINCT 키워드를 활용하여 모든 자동차들의 CAR_ID 목록을, subquery를 이용하여 2022-10-16에 대여 가능한 자동차 목록을 만든 후, 이 둘을 LEFT JOIN하여 왼쪽 테이블에 하나씩 값을 채워넣고 칸이 비는 곳에는 ‘대여 가능’을 출력하도록 한다.

- 다만, 이 문제처럼 기존 테이블의 레코드들을 특정 컬럼의 값들을 기준으로 나눈 group에서 특정 레코드 하나만이라도 조건식을 충족하는지 여부를 아는 것으로 충분하다면, 그룹함수를 사용해 다음과 같이 간소화할 수 있다.

1
2
3
4
5
6

SELECT 
    CAR_ID,
    IF(MAX(DATE '2022-10-16' BETWEEN START_DATE AND END_DATE), '대여중', '대여 가능') AS IS_RENTED
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

* self join

1

SELECT * FROM table1 AS t1 JOIN table1 AS t2 ON t1.field1 = t2.field2

- 자기 자신과 join 연산을 하는 것을 흔히 self join이라 한다. tuple의 어떤 attribute의 값이 다른 tuple의 그와 다른 attribute의 값과 일치하는 경우를 추출하는 등의 용도로 쓸 수 있다. (단, self join을 할 때에는 FROM 뒤에 테이블 이름을 쓸 때 반드시 각각 다른 alias를 지정하고 이를 호출해 써야 한다.)

2. 합집합, 차집합, 교집합

1) UNION, UNION ALL

1
2
3

SELECT * FROM table1
UNION
SELECT * FROM table2

- 서로 다른 두 개의 SELECT 쿼리로 얻은 결과를 두 쿼리 사이에 UNION 연산자를 두어 이들을 결합해 하나의 결과로 만들 수 있다. 이때 UNION을 사용하면 앞의 결과와 뒤의 결과 중 완전히 일치하는 튜플(중복된 튜플)은 하나만 남겨두며, UNION 대신 UNION ALL을 사용하면 중복을 버리지 않고 모두 하나의 결과로 결합시킨다.

- 뒤의 SELECT 쿼리에 나온 속성명들은 무시되며 앞의 SELECT 쿼리에 나온 속성명을 순서대로 그대로 쓰게 된다.

- 앞의 SELECT 쿼리에 나온 속성명의 개수가 뒤의 SELECT 쿼리에 나온 속성명의 개수와 완전히 일치해야(합집합 호환) 에러가 발생하지 않는다.

2) EXCEPT, EXCEPT ALL

1
2
3

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

- EXCEPT는 차집합 연산이다. 이 경우에도 합집합 호환 관계에만 에러가 발생하지 않는다.

- 기본적으로 앞의 relation에 중복 tuple이 있더라도 그것이 뒤의 relation에 포함된다면 모두 제거된 relation이 리턴되나, EXCEPT ALL 연산의 경우 모두 제거되지는 않고 뒤의 relation에 포함된 tuple 개수만큼만 제거되고 나머지 중복 tuple이 있다면 그 tuple은 남아 있는 relation이 리턴된다.

3) INTERSECT, INTERSECT ALL

1
2
3

SELECT * FROM table1
INTERSECT
SELECT * FROM table2

- INTERSECT는 교집합 연산이다. 이 경우에도 합집합 호환 관계에만 에러가 발생하지 않는다.

- 기본적으로 결과 relation에 중복 tuple이 있는 경우 하나만 남기고 결과가 리턴되나, INTERSECT ALL 연산의 경우 중복이 있는 경우 그 모든 중복 tuple을 함께 리턴한다.