MSSQL SELECT 결과 합치기 - MSSQL SELECT gyeolgwa habchigi

관계형 데이터베이스에서 공간 및 관리의 효율성과 보완성 등의 이유로 테이블을 여러 개의 영역에 쪼개서 보관을 한다. 따라서 여러 개의 테이블을 이용해서 데이터를 분석하기 위해서는 다양한 테이블에서 공통된 KEY값으로 서로 조인하여 원하는 데이터를 출력하는 SQL문을 잘 아는 것이 좋다.

📌열(Column) 합치기

💡내부 조인(INNER JOIN)

내부조인은 교집합이라 생각하면된다. 내부 조인을 하게 되면 결과로 두 테이블에 공통으로 존재하는 키값이 되는 모든 행을 나타낸다. 내부 조인을 하는 방법은 다음과 같이 두 가지 방법이 있다.

방법

1. FROM/WHERE
2. INNER JOIN

고객주소 테이블 : ADDR

고객휴대폰 테이블: MOBILE

위와 같이 테이블 두 개가 있다고 가정해보자.

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에 각 테이블의 열 이름을 인식시켜주기 위함이다.

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

*추가

조인 조건을 지정하지 않은 채 두 테이블을 조인하면 곱집합이 된다.

이 경우 반환되는 행의 수는 첫 번째 테이블과 두 번째 테이블의 행의 수를 곱한 수가 된다.

💡외부조인

1. LEFT OUTER JOIN

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

왼쪽 테이블을 기준으로 조인하는 방법으로, OUTER를 생략해도 무방하다. 

만약 왼쪽 테이블에는 존재하지만 오른쪽 테이블에는 존재하지 않는 키값이 있다면 NULL값으로 값이 반환된다.

예시)

고객주소 테이블1 : ADDR1

고객휴대폰 테이블1: MOBILE1

--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 결과값

특징

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;

오른쪽 테이블을 기준으로 조인하는 방법으로 , OUTER를 생략해도 무방하다.

만약 오른쪽 테이블에는 존재하지만 왼쪽 테이블에는 존재하지 않는 키값이 있다면 NULL값으로 값이 반환된다.

EX) 예시는 위 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 키워드는 왼쪽과 오른쪽 테이블의 모든 행이 반환된다. 즉 LEFT JOIN과 RIGHT JOIN을 합쳐놓은 형태라고 할 수 있다.

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

추가

1. 테이블을 통합할 때 기준이 되는 테이블을 가장 먼저 나타내면 데이터 분석이 용이하다.

기준이 되는 테이블에 별칭을 주어 그 별칭을 준 테이블을 기준으로 데이터를 조인하면 통합테이블을 쉽게 만들 수 있을 뿐 아니라 데이터 분석 작업도 용이하다.

2. LEFT OUTER JOIN은 WHERE조건절 *= 연산자를 통해 나타낼 수 있고, RIGHT OUTER JOIN은 WHERE 조건절에 =*연산자를 사용해서 나타낼 수 있다.

다음 1번과 2번 문장은 동일한 결과를 나타낸다.

--1번
FROM CUSTOM TMP1 LEFT OUTER JOIN ORDER TMP2
ON TMP1.CUSTOM_ID = TMP2.CUSTOM_ID

--2번
FROM CUSTOM TMP1, ORDER TMP2
WHERE TMP1.CUSTOM_ID *= TMP2.CUSTOM_ID

ORACLE은 LEFT OUTER JOIN을 나타내는 (+)= 연산자를 지원한다. 즉, ORACLE은 다음과 같이 표현할 수 있다.

SELECT TMP1.EMP_ID, TMP1.NM, TMP2.ORDER_ID,
       CASE WHEN TMP2.CUST_ID IS NOT NULL THEN 1 ELSE 0
       END AS SALES_FLG
FROM EMPLOYEE TMP1, ORDERS TMP2
WHERE TMP1.EMP_ID (+)= TMP2.EMP_ID
ORDER BY 1;

3.ACCESS, MYSQL 등 몇몇 DBMS에서는 FULL OUTER JOIN 키워드가 지원되지 않는다.

4.테이블 대신 SELECT 구문을 사용해서 테이블을 만들 수 있다.

테이블 전체를 합칠 수도 있고 SELECT/FROM/WHERE 구문을 사용하여 테이블 일부만 합칠 수도 있다

--1번 테이블 전체를 합치기
SELECT TMP1.EMP_ID, TMP1.NM, TMP2.ORDER_ID, TMP2.CUST_ID
FROM EMPLOYEE TMP1 LEFT OUTER JOIN ORDERS TMP2
ON TMP1.EMP_ID = TMP2.EMP_ID
ORDER BY 1;

--2번 SELECT문을 이용하여 테이블 전체를 합치기
SELECT TMP1.EMP_ID, TMP1.NM, TMP2.ORDER_ID, TMP2.CUST_ID
FROM EMPLOYEE TMP1 LEFT OUTER JOIN (SELECT * FROM ORDERS) TMP2
ON TMP1.EMP_ID = TMP2.EMP_ID
ORDER BY 1;

--3번 SELECT문을 이용하여 테이블 일부를 합치기
SELECT TMP1.EMP_ID, TMP1.NM, TMP2.ORDER_ID, TMP2.CUST_ID
FROM EMPLOYEE TMP1 LEFT OUTER JOIN (SELECT ORDER_ID, CUST_ID FROM ORDERS) TMP2
ON TMP1.EMP_ID = TMP2.EMP_ID
ORDER BY 1;

5.테이블 별칭은 TMP1, TMP2, TMP3 등과 같이 순서 있게 사용하는 것이 좋다.

조인을 사용할 떄는 테이블에 별칭을 사용해야 한다.

이 때, 조인하는 테이블이 많을수록 복잡해지고 SQL문장을 수정하기가 쉽지 않지만 순서가 있는 문자로 테이블 멸칭을 주면 SQL문장이 복잡해지더라도 쉽게 수정할 수 있고 오류도 쉽게 해결할 수 있다.

6.SELECT문에 출력할 열 이름을 적을 때 어느 테이블에서 참조되는 열 이름인지 나타내는 것이 좋다.

SQL문장이 길어질수록 오류를 해결하기 수월해지고 참조해야 하는 테이블이 꼭 필요한 테이블인지 확인할 수도 있기 때문이다.

ORACLE

외부 조인(outer join)은 조건을 만족하지 않는 행도 모두 출력하기 위한 조인 기법입니다.

SELECT 테이블 이름1.열 이름1, 테이블 이름2.열 이름2, ...
FROM   테이블 이름1, 테이블 이름2
WHERE  테이블 이름1.열 이름1 = 테이블 이름2.열 이름2(+);

(+)  ⇒  외부 조인, 데이터가 부족한 쪽에 기술한다.

(+) 기호를 붙이면 데이터 값이 부족한 테이블에 NULL값을 갖는 행이 생성되어 데이터 값이 충분한 테이블의 행들이 NULL행에 조인됩니다. 

📌행(Row) 합치기

💡UNION 연산자

SELECT 열이름1, 열이름2 FROM 테이블명1 WHERE 조건절
집합 연산자 --UNION, UNION ALL, INTERSET, MINUS
SELECT 열이름1, 열이름2 FROM 테이블명2 WHERE 조건절
ORDER BY 1;

두 개 이상의 테이블의 행을 합칠 때 UNION 연산자를 사용한다. 즉 ,SQL의 UNION연산자는 두 개 이상의 SELECT문의 결과값을 합치는 것이다. A와B라는 테이블에서 일부의 데이터를 합칠 수도 있고, A라는 테이블을 WHERE조건절을 사용하여 나눈 후 일부만 합칠 수도 있다.

 UNION연산자를 사용할 때 주의할 점은 다음과 같다.

1. UNION연산자로 합쳐지는 SELECT문의 열의 숫자는 반드시 동일해야 한다.
2. SELECT문의 각 데이터 타입을 일치해야 한다.

UNION연산자는 중복되는 값이 있으면 한 가지만 표시(중복X)한다. 만약 중복된 값을 모두 보여주고 싶다면 UNION 바로 뒤에 ALL연산자를 추가(UNION ALL)하면 된다.

ORDER BY절을 사용하여 데이터를 정렬하고 싶다면 문장의 마지막에 적으면 된다. 문장의 마지막에 적으면 두 번째 SELECT문만 정렬될 것 같지만 데이터가 합쳐진 후 전체에 적용된다.

'SQL > 개념(틀)' 카테고리의 다른 글