SQL 테이블 합치기 (조인과 집합)

조인(JOIN): 열(Column) 합치기

내부 조인(INNER JOIN)

내부조인은 `교집합`이라 생각하면됩니다. 내부 조인은 두 개 이상의 테이블에서 관련된 데이터를 결합하여 검색하는데 사용되는 조인 유형입니다. 내부 조인은 조인 조건에 맞는 행만 반환하며, 조인 조건에 맞지 않는 행은 제외됩니다. 내부 조인을 하는 방법은 다음과 같이 두 가지가 있습니다.

 

1. FROM / WHERE

SELECT column1, column2, ...
FROM table1, table2
WHERE table1.column = table2.column;

 

2. INNER JOIN  ⭐ ⭐ ⭐

SELECT column1, column2, ...
FROM table1
(INNER) JOIN table2
ON table1.column = table2.column;

INNER JOIN과 ON 절을 사용하는 방법이 FROM과 WHERE 절을 사용하는 방법보다 가독성이 좋고, 내부 조인을 더욱 명시적으로 작성할 수 있기 때문에 INNER JOIN과 ON절을 사용하는 것을 추천드립니다.

INNER JOIN을 사용할 때 INNER를 생략하고 사용하는 것이 가능합니다. 

---

내부 조인 예제

1-1) FROM/WHERE를 사용

SELECT 테이블명1.열 이름1, 테이블명2.열 이름2
FROM 테이블명1, 테이블명2
WHERE 테이블명1.KEY=테이블명2.KEY;

--예제
SELECT ADDR.CUST_ID, ADDR.HOME_ADDR1, ADDR.HOME_ADDR2, MOBILE.MOBILE_NO
FROM ADDR, MOBILE
WHERE ADDR.CUST_ID = MOBILE.CUST_ID;

 

1-2) FROM/WHERE와 별칭을 사용

SELECT 별칭1.열 이름1, 별칭2.열 이름2
FROM 테이블명1(AS)별칭1, 테이블명2(AS)별칭2
WHERE 별칭1.KEY=별칭2.KEY;

--AS를 사용하여 별칭주기
SELECT TMP1.CUST_ID, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2, TMP2.MOBILE_NO
FROM ADDR AS TMP1, MOBILE AS TMP2
WHERE TMP1.CUST_ID = TMP2.CUST_ID;

--AS를 사용하지 않고 별칭주기
SELECT TMP1.CUST_ID, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2, TMP2.MOBILE_NO
FROM ADDR TMP1, MOBILE TMP2
WHERE TMP1.CUST_ID = TMP2.CUST_ID;

위와 동일하지만 한 가지 차이는 테이블 이름에 새롭게 별칭을 줬다는 점이다.

참고로 오라클을 포함한 몇몇 DBMS는 테이블명 별칭을 줄 때 AS 키워드가 지원되지 않으므로 AS를 빼고 테이블 별칭을 설정해야 한다.

 

2-1) INNER JOIN을 사용

SELECT 테이블명1.열 이름1, 테이블명2.열 이름2
FROM 테이블명1 
INNER JOIN 테이블명2
ON 테이블명1.KEY=테이블명2.KEY;

--별칭을 사용하지 않고 나타내기
SELECT ADDR.*, MOBILE.MOBILE_NO
FROM ADDR 
INNER JOIN MOBILE
ON ADDR.CUST_ID = MOBILE.CUST_ID;

INNER JOIN을 사용하여 두 테이블을 합칠 수 있어, ON 뒤에 두 테이블의 연결고리가 되는 키값을 적으면 된다.

 

2-2) INNER JOIN과 별칭을 사용

SELECT 별칭1.열 이름1, 별칭2.열 이름2
FROM 테이블명1(AS)별칭1 
INNER JOIN 테이블명2(AS)별칭2
ON 별칭1.KEY=별칭2.KEY;

--INNER JOIN을 사용하기
SELECT TMP1.CUST_ID, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2, TMP2.MOBILE_NO
FROM ADDR AS TMP1 
INNER JOIN MOBILE AS TMP2
ON TMP1.CUST_ID = TMP2.CUST_ID;

INNER JOIN을 사용하는 문법과 동일한데 한 가지 차이는 테이블에 별칭을 주었다는 점이다.

마찬가지로 오라클을 포함한 몇몇 DBMS는 테이블명 별칭을 줄 때 AS키워드가 지원되지 않으므로 AS를 빼고 테이블 별칭을 설정해야 한다.

 

키값을 매칭시킬 때 'ON ADDR.CUST_ID=MOBILE.CUST_ID' 혹은 'WHERE TMP1.CUST_ID=TMP2.CUST_ID'와 같이 CUST_ID앞에 각각의 테이블명 혹은 별칭을 붙인 이유는 DBMS에 각 테이블의 열 이름을 인식시켜주기 위함이다.

별칭을 사용하지 않은 방법을 사용하면 타이핑으로 오류가 날 가능성이 많아진다. 따라서 별칭을 주면 이러한 오류도 줄이고 조인하는 테이블이 여러 개일 경우 쉽게 인식할 수 있는 장점이 있다.

 

조인 조건을 지정하지 않은 채 두 테이블을 조인하면 곱집합이 된다.
이 경우 반환되는 행의 수는 첫 번째 테이블과 두 번째 테이블의 행의 수를 곱한 수가 된다.

 

외부 조인(OUTER JOIN)

1. LEFT OUTER JOIN

SELECT 별칭1.열 이름1, 별칭2.열 이름2
FROM 테이블명1 (AS) 별칭1 
LEFT (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

LEFT OUTER JOIN은 왼쪽(첫 번째) 테이블을 기준으로 오른쪽(두 번째) 테이블을 결합하는 조인입니다. LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 포함하며, 오른쪽 테이블의 일치하는 행이 있으면 결합합니다. 일치하는 행이 없는 경우 NULL 값으로 채워집니다.

OUTER는 생략해도 상관 없습니다.

 

LEFT OUTER JOIN 예시

--EX)
SELECT TMP1.CUST_ID AS CUST_ID1, TMP2.HOME_ADDR1, TMP1.HOME_ADDR2
       TMP2.CUST_ID AS CUST_ID2, TMP2.MOBILE_NO
FROM ADDR1 AS TMP1 
LEFT OUTER JOIN MOBILE2 AS TMP2 
ON TMP1.CUST_ID = TMP2.CUST_ID;

 

LEFT OUTER JOIN 결과값

CUST_ID1	HOME_ADDR1	HOME_ADDR2	CUST_ID2	MOBILE_NO
5465	서울시 강남구 역삼동	111-11	5465	010-1111-1111
5410	서울시 중구 서소문동	333-33	5410	010-3333-3333
5410	서울시 중구 서소문동	333-33	5410	010-7777-7777
6511	서울시 송파구 문정동	444-44

 

특징

1. `왼쪽 ADDR1 테이블`의 모든 값을 나타내고, `왼쪽 테이블`을 기준으로 `오른쪽 MOBILE1테이블`이 붙는다.
2. 한 개의 키값에 두 개 이상의 값을 가지고 있으면 모든 값이 붙는다.
   예로 MOBILE1 테이블에 CUST_ID가 5410인 고객의 휴대폰 번호는 2개가 있다. 이때 LEFT OUTER JOIN을 통해 합쳐진 테이블에는 두 개의 번호 모두 나타난다는 것을 알 수 있다.
3. 왼쪽 테이블의 키값을 기준으로 오른쪽 테이블의 키값이 존재하지 않는다면, NULL값으로 처리된다.
   예로 LEFT OUTER JOIN을 통해 합쳐진 테이블에서 CUST_ID가 6511인 고객의 MOBILE_NO는 NULL값으로 처리되었음을 볼 수 있다.

 

2. RIGHT OUTER JOIN

SELECT 별칭1.열 이름1, 별칭2.열 이름2
FROM 테이블명1 (AS) 별칭1 
RIGHT (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY=별칭2.KEY;

RIGHT OUTER JOIN은 오른쪽(두 번째) 테이블을 기준으로 왼쪽(첫 번째) 테이블을 결합하는 조인입니다. RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 포함하며, 왼쪽 테이블의 일치하는 행이 있으면 결합합니다. 일치하는 행이 없는 경우 NULL 값으로 채워집니다.

OUTER는 생략해도 상관 없습니다.

 

LEFT OUTER JOIN을 반대로 생각하면 되기 때문에 예시는 생략하겠습니다.

 

 

3. FULL OUTER JOIN

SELECT 별칭1.열 이름1, 별칭2.열 이름2
FROM 테이블명1 (AS) 별칭1 
FULL (OUTER) JOIN 테이블명2 (AS) 별칭2
ON 별칭1.KEY = 별칭2.KEY;

FULL OUTER JOIN은 왼쪽 테이블과 오른쪽 테이블의 모든 행을 포함하는 조인입니다. FULL OUTER JOIN은 왼쪽과 오른쪽 테이블 간의 일치하는 행을 결합하며, 일치하지 않는 행은 각각 NULL 값으로 채워집니다.

 

예시는 위 LEFT OUTER JOIN과 RIGHT OUTER JOIN 전부 수행된다 생각하면 됩니다.

 

MYSQL 등 몇몇 DBMS에서는 FULL OUTER JOIN 키워드가 지원되지 않습니다.
따라서 MySQL에서 FULL OUTER JOIN을 적용하기 위해서는 UNION 연산자와 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 조합하는 방식을 사용하면 됩니다.

 

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column
WHERE table1.column IS NULL;​​

`UNION` 연산자에 대한 설명은 바로 아래에 하도록 하겠습니다.

 

 

 

집합: 행(Row) 합치기

집합 연산자: UNION, UNION ALL, INTERSECT, MINUS

SELECT 열이름1, 열이름2 
FROM 테이블명1
집합 연산자 --UNION, UNION ALL, INTERSECT, MINUS
SELECT 열이름1, 열이름2 
FROM 테이블명2

• `UNION`
  • 두 개의 SELECT 문의 결과를 합칩니다.
  • 중복된 행은 한 번만 출력됩니다.
  • 합집합
• `UNION ALL`
  • 두 개의 SELECT 문의 결과를 합칩니다.
  • 중복된 행도 그대로 출력됩니다.
  • 합집합
• `INTERSECT`
  • 두 개의 SELECT 문의 결과에서 공통된 행(교집합)을 선택합니다.
  • 결과는 중복된 행이 제거됩니다.
  • 교집합
• `EXCEPT` 또는 `MINUS`
  • 첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과를 제외합니다.
  • 결과는 중복된 행이 제거됩니다.
  • 차집합

 

집합 연산자 주의할 점

1. 열의 개수 & 열의 데이터 유형 일치
   집합 연산자를 사용하여 결합하는 SELECT 문의 결과 집합은 동일한 열의 개수와 데이터 유형을 가져야 합니다. 열의 이름은 첫 번째 SELECT 문의 열 이름을 따릅니다.
   
   
2. 중복된 행 처리  ⭐ ⭐
   `UNION` 연산자는 결과에서 중복된 행을 제거합니다. 중복된 행을 포함하여 모든 결과를 보고 싶다면 `UNION ALL`을 사용해야 합니다. 또한 다른 집합 연산자인 `INTERSECT`와 `EXCEPT`는 중복된 행을 포함하지 않습니다. 필요에 따라 중복된 행을 포함하여 결과를 처리해야 할지 고려해야 합니다.
   
   
3. NULL 값 처리
   집합 연산자를 사용하여 결합하는 SELECT 문의 열이 NULL 값을 포함하는 경우, 결과 집합에서도 NULL 값이 유지됩니다. NULL 값 처리에 주의해야 하며, 필요에 따라 NULL 값 처리를 추가로 수행해야 할 수도 있습니다.
   
   
4. 결과 정렬
   집합 연산자를 사용하여 결합된 결과는 첫 번째 SELECT 문의 결과가 먼저 나타나며, 순서대로 나머지 SELECT 문의 결과가 이어집니다. 정렬을 명시적으로 지정하지 않은 경우, 기본적으로는 첫 번째 SELECT 문의 열의 순서에 따라 결과가 정렬됩니다.

SELECT 열이름1, 열이름2 
FROM 테이블명1
UNION
SELECT 열이름1, 열이름2 
FROM 테이블명2
ORDER BY 1

 

`ORDER BY 절`을 사용하여 데이터를 정렬하고 싶다면 문장의 마지막에 적으면 됩니다.

문장의 마지막에 `ORDER BY 절`을 작성하여 두 번째 SELECT문만 정렬될 것 같지만 결과를 확인하면 데이터가 합쳐진 후 정렬된 것을 확인할 수 있습니다.