You& Data_Science & Life

SQL_10_Merge

02 Jul 2023 SQL

1. 열 합치기

1) INNER JOIN

(1) FROM / WHERE

-- FROM / WHERE
SELECT table_1.col_1, table_2.col_2,
FROM table_1, table_2  
WHERE table_1.KEY = table_2.KEY;

-- FROM / WHERE + 테이블을 별칭 활용
-- 오라클을 포함한 일부 DBMS는 테이블을 별칭하는 'AS'를 지원하지 않기에 생략해야함.
SELECT ABBV_1.col_1, ABBV_2.col_2
FROM table_1 (AS) ABBV_1, table_2 (AS) ABBV_2
WHERE ABBV_1.KEY = ABBV_2.KEY;

(2) INNER JOIN 활용 (★★★)

-- INNER JOIN 활용
SELECT table_1.col_1, table_2.col_2,
FROM table_1 INNER JOIN table_2  
ON table_1.KEY = table_2.KEY;

-- INNER JOIN + 테이블을 별칭 활용 
SELECT ABBV_1.col_1, ABBV_2.col_2
FROM table_1 (AS) ABBV_1 INNER JOIN table_2 (AS) ABBV_2
ON ABBV_1.KEY = ABBV_2.KEY;

예제

-- 1) 2개 테이블 결합 하기
-- 주문이력이있는 고객ID / 고객 이름 / 주문번호
SELECT CUST.CUST_ID, CUST.CUST_NM, ORD.ORDER_ID
FROM CUSTOMERS AS CUST INNER JOIN ORDERS AS ORD
ON CUST.CUST_ID = ORD.CUST_ID;

-- 2) 3개 테이블 결합 하기
-- 주문이력이있는 고객ID / 고객 이름 / 주문번호 / 판매직원ID / 판매직원 이름
---- (1) FROM / WHERE
SELECT CUST.CUST_ID, CUST.CUST_NM, ORD.ORDER_ID, ORD.EMP_ID, EMP.NM
FROM CUSTOMERS AS CUST, ORDERS AS ORD, EMPLOYEE AS EMP
WHERE
    CUST.CUST_ID = ORD.CUST_ID
    AND ORD.EMP_ID = EMP.EMP_ID

---- (2) INNER JOIN / ON !!!!
SELECT CUST.CUST_ID, CUST.CUST_NM, ORD.ORDER_ID, ORD.EMP_ID, EMP.NM
FROM
    CUSTOMERS AS CUST INNER JOIN ORDERS AS ORD 
    ON CUST.CUST_ID = ORD.CUST_ID
    INNER JOIN EMPLOYEE AS EMP 
    ON ORD.EMP_ID = EMP.EMP_ID

TIP

  • 조인 조건(WHERE or ON)을 지정하지 않고 조인시, 곱집합이 되어, 키가 복제되어 늘어난다.
  • 3개 이상의 테이블을 결합하는 경우, 앞에서부터 테이블이 결합된 이후 새로운 테이블이 추가되는 개념이다.


2) OUTER JOIN

1) LEFT OUTER JOIN

: 왼쪽 테이블 기준 조인. (OUTER를 생략해도 무방함.)

SELECT ABBV_1.col_1, ABBV_2.col_2,
FROM table_1 (AS) ABBV_1 LEFT (OUTER) JOIN table_2 (AS) ABBV_2
WHERE ABBV_1.KEY = ABBV_2.KEY;

2) RIGHT OUTER JOIN

: 오른쪽 테이블 기준 조인. (OUTER를 생략해도 무방함.)

SELECT ABBV_1.col_1, ABBV_2.col_2,
FROM table_1 (AS) ABBV_1 RIGHT (OUTER) JOIN table_2 (AS) ABBV_2
WHERE ABBV_1.KEY = ABBV_2.KEY;

3) FULL OUTER JOIN

: 왼쪽과 오른쪽 테이블의 모든 행이 반환됨. (OUTER를 생략해도 무방함.)

SELECT ABBV_1.col_1, ABBV_2.col_2,
FROM table_1 (AS) ABBV_1 FULL (OUTER) JOIN table_2 (AS) ABBV_2
WHERE ABBV_1.KEY = ABBV_2.KEY;
  • * LEFT or RIGHT, FULL OUTER JOIN시,
    • 기준 테이블에만 값이 있고 JOIN되는 테이블엔 값이 없다면 –> NULL값을 반환함.
    • 1개의 키값에 2개이상의 값이 존재하면 –> 키값이 복제되어 모든 값이 붙는다.

예제

-- 1) EX_1
-- CUSTOMERS와 ORDERS 테이블을 이용하여, [고객ID / 고객 이름 / 주문번호]를 나타내고, 
-- 주문 이력이 있는 고객은 1, 없는 고객은 0으로 표시해보자. 
SELECT CUST.CUST_ID, CUST.CUST_NM, ORD.ORDER_ID
    CASE WHEN ORD.ORDER_ID IS NOT NULL THEN 1
    ELSE 0 END AS ORDER_FLG
FROM CUSTOMERS AS CUST LEFT OUTER JOIN ORDERS AS ORD
ON CUST.CUST_ID = ORD.CUST_ID
ORDER BY CUST.CUST_ID ;

-- 2) EX_2
-- CUSTOMERS와 ORDERS 테이블을 이용하여, 
-- 주문이력이 있는 고객과 없는 고객의 숫자를 알아보자
SELECT 
    CASE WHEN ORD.ORDER_ID IS NOT NULL THEN 1
    ELSE 0 END AS ORDER_FLG,
    COUNT(*) AS CNT
FROM CUSTOMER AS CUST LEFT OUTER JOIN ORDERS AS ORD
ON CUST.CUST_ID = ORD.CUST_ID
GROUP BY ORDER_FLG

-- 3) EX_3
-- EMPLOYEE와 ORDERS 테이블을 이용하여, 
-- 직원아이디, 이름, 주문번호를 나타내고, 
-- 판매실적이 있는 직원은 1, 없는 직원은 0으로 표기후
-- 직원 아이디로 오름차순 정렬
SELECT EMP.EMP_ID, EMP.NM, ORD.ORDER_ID,
    CASE WHEN ORD.ORDER_ID IS NOT NULL THEN 1
    ELSE 0 END AS SALES_FLG 
FROM EMPLOYEE AS EMP LEFT OUTER JOIN ORDERS AS ORD
ON EMP.EMP_ID = ORD.EMP_ID
ORDER BY EMP.EMP_ID;


-- 4) EX_4
-- CUSTOMERS, EMPLOYEE, ORDERS 테이블을 이용하여, 
-- 직원아이디, 이름, 해당 직원이 맡았던 주문번호를 나타내고,
-- 해당 주문번호와 고객 아이디를 매칭시켜 고객이름을 표기하라 (outer join만 활용)
SELECT EMP.EMP_ID, EMP.NM, ORD.ORDER_ID, CUST.CUST_NM
FROM
    EMPLOYEE AS EMP 
    LEFT JOIN ORDERS AS ORD
    ON EMP.EMP_ID = ORD.EMP_ID
    LEFT JOIN CUSTOMERS AS CUST
    ON ORD.CUST_ID = CUST.CUST_ID

TIP

1) 다음과 같이 WHERE 조건절을 활용하여 join을 할 수 있다.
1) LEFT OUTER JOIN –> WHERE 조건절에 *=
2) RIGHT OUTER JOIN –> WHERE 조건절에 =*
2) 테이블 대신, SELECT 구문을 활용하여, (서브 쿼리) 테이블을 만들 수 있다.
3) ACCESS, MY SQL 등 몇몇 DbMS에서는 FULL OUTER JOIN 키워드가 지원되지 않는다.
4) SELECT문에 출력할 열 이름을 적을 땐, 어느 테이블에서 참조되는 열인지 나타내는 좋다.

-- 1) WHERE & `*=` 를 할용한 조인
SELECT EMP.EMP_ID, EMP.NM, ORD.ORDER_ID, 
FROM EMPLOYEE AS EMP, ORDERS AS ORD
WHERE EMP.EMP_ID *= ORD.EMP_ID;

-- 2) 테이블 대신, SELECT 구문을 활용하여, (서브 쿼리) 테이블을 만들 수 있다.
-- SELECT문을 이용하여 테이블 일부만 합치기
SELECT EMP.EMP_ID, EMP.NM, ORD.ORDER_ID, 
FROM EMPLOYEE AS EMP 
    LEFT OUTER JOIN 
        (SELECT ORDER_ID, CUST_ID FROM ORDERS) AS ORD
ON EMP.EMP_ID = ORD.EMP_ID;


2. 행(Row) 합치기

1) UNION

: 2개 이상의 테이블의 행을 합칠 때 사용하며, 이때 중복을 제외함.

SELECT col_1, col_2 FROM table_1 WHERE 조건절
UNION
SELECT col_1, col_2 FROM table_2 WHERE 조건절
ORDER BY 1;

주의사항

  • 1) UNION 연산자로 합쳐지는 SELECT문의 열의 숫자는 반드시 동일해야한다.
  • 2) SELECT문의 각 데이터 타입은 일치해야 한다.
  • UNION 연산자는 중복값이 있는 경우 한가지만 표기하며, 중복값을 모두 출력하고 싶은 경우 UNION ALL연산자를 사용해야 한다.
  • ORDER BY절을 사용하여 데이터를 정렬하고 싶다면, 문장의 마지막에 작성하면 된다.(2번째 SELECT문만 정렬할 것 같지만, 데이터가 합쳐진 후 전체에 ORDER BY가 적용되기 때문이다.)

예제

-- 1) EX_1
-- CUSTOMERS와 EMPLOYEE 테이블을 이용하여, 
-- [직원 이름과 고객이름]을 합친 후
-- 오름차순으로 정렬해보자
SELECT EMP.EMP_NM AS NM FROM EMPLOYEE AS EMP 
UNION
SELECT CUST.CUST_NM AS NM FROM CUSTOMERS AS CUST
ORDER BY 1 ;

-- 위 예제에서,합쳐지는 필드를 동일한 컬럼명으로 변경하지 않았을 경우.
SELECT EMP.EMP_NM FROM EMPLOYEE AS EMP 
UNION
SELECT CUST.CUST_NM FROM CUSTOMERS AS CUST
ORDER BY 1;
-- 첫번째 SELECT문의 "EMP_NM" 이름으로 두번째 SELECT문 컬럼에 적용함.
-- 즉, 결과의 열이름은 첫 번째 문장의 열 이름으로 출력됨.

TIP

1) UNION 연산자 안의 SELECT문에는 같은 열, 식, 집계 합수를 포함해야 한다.
2) 만약 3개의 SELECT 문장을 결합한다면 두개의 UNION 연산자가 필요하다

2) UNION ALL

: 2개 이상의 테이블의 행을 합칠 때 사용하며, 이때 중복을 제외하지 않고 모두 포함.

SELECT col_1, col_2 FROM table_1 WHERE 조건절
UNION ALL
SELECT col_1, col_2 FROM table_2 WHERE 조건절
ORDER BY 1;

예제

-- 1) EX_1
-- CUSTOMERS와 ORDERS 테이블을 이용하여, 
-- 중복값을 허용한채, [고객ID]을 합쳐서 오름차순으로 정렬해보자.
-- 오름차순으로 정렬해보자
SELECT CUST.CUST_ID AS NM FROM CUSTOMERS AS CUST 
UNION ALL
SELECT ORD.CUST_ID AS NM FROM ORDERS AS ORD
ORDER BY 1 ;


+ 연습문제

  • CUST_PARTY 테이블
  • RCPT_ACCT 테이블
SELECT * FROM CUST_PARTY;
+---------------+----------+---------+---------------+---------------+
| SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     |
+---------------+----------+---------+---------------+---------------+
| 5707121111000 | AR KIM   | 5670    | 02-555-6678   | 010-1111-1111 |
| 6508112222333 | JH RYU   | 3574    | 02-6666-4444  | 010-6666-6666 |
| 6912081111222 | SH HONG  | 2357    | 031-4456-9887 | 010-2222-2222 |
| 7105252222000 | JH KIM   | 8988    | 032-333-1111  | 010-4444-4444 |
| 7706302222111 | JH LEE   | 7702    | 033-111-3355  | 010-5555-5555 |
| 8204073333111 | YC JUNG  | 5670    | 02-2222-1111  | 010-7777-7777 |
| 8311221111333 | MK KANG  | 3977    | 051-999-8888  | 010-3333-3333 |
| 8911293333222 | JH JUN   | 6989    | 031-224-2222  | 010-8888-8888 |
| 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9999-9999 |
+---------------+----------+---------+---------------+---------------+

SELECT * FROM RCPT_ACCT;
+---------------+---------+------------+---------+----------+
| SSN           | ACCT_NO | NEW_DT     | CNCL_DT | RCPT_AMT |
+---------------+---------+------------+---------+----------+
| 8204073333111 | 223620  | 2010-09-11 | NULL    |  1000357 |
| 8204073333111 | 275123  | 2013-11-26 | NULL    |   123000 |
| 8204073333111 | 554520  | 2013-09-28 | NULL    |  5678740 |
| 5707121111000 | 578221  | 2012-03-26 | NULL    |   500000 |
| 5707121111000 | 656421  | 2009-11-17 | NULL    |   354210 |
| 6508112222333 | 658720  | 2013-06-08 | NULL    |    41324 |
| 7706302222111 | 668721  | 2010-07-27 | NULL    |   547700 |
+---------------+---------+------------+---------+----------+

-- 1) 고객별 살아있는 계좌정렬
-- CUST_PARTY와 RCPT_ACCT 테이블을 LEFT JOIN하여,
-- 현재 살아있는 계좌를 가지고 있는 고객들 및 휴대전화번호를 찾아보고,
-- 살아있는 계좌 개수를 기준으로 오름차순 정렬로 나열

 ---- (step_1) 유저별 살아있는 계좌 개수 계산 --> 서브 쿼리 테이블
 ---- : 해지일이 없는 유저 카운트 계산
SELECT SSN,
    COUNT(*) AS CNT 
FROM RCPT_ACCT 
WHERE CNCL_DT IS NULL
GROUP BY SSN;
-- +---------------+-----+
-- | SSN           | CNT |
-- +---------------+-----+
-- | 8204073333111 |   3 |
-- | 5707121111000 |   2 |
-- | 6508112222333 |   1 |
-- | 7706302222111 |   1 |
-- +---------------+-----+

 ---- (step_2) 유저정보 테이블과 Merge
SELECT CUST.CUST_ID, CUST.MOBILE_NO, RCPT.CNT
FROM 
    CUST_PARTY AS CUST LEFT OUTER JOIN 
    (SELECT SSN,
        COUNT(*) AS CNT 
        FROM RCPT_ACCT 
        WHERE CNCL_DT IS NULL
        GROUP BY SSN) AS RCPT 
ON CUST.SSN = RCPT.SSN
WHERE RCPT.CNT > 0
ORDER BY RCPT.CNT DESC;
-- +---------+---------------+------+
-- | CUST_ID | MOBILE_NO     | CNT  |
-- +---------+---------------+------+
-- | 5670    | 010-7777-7777 |    3 |
-- | 5670    | 010-1111-1111 |    2 |
-- | 3574    | 010-6666-6666 |    1 |
-- | 7702    | 010-5555-5555 |    1 |
-- +---------+---------------+------+

-- 2) 단순 OUTER JOIN 결과, 왼쪽 테이블의 키값이 우측 테이블의 서로다른 정보값 만큼 복제됨
SELECT CUST.SSN, CUST.MOBILE_NO, RCPT.ACCT_NO
FROM 
    CUST_PARTY AS CUST LEFT OUTER JOIN
    RCPT_ACCT AS RCPT 
ON CUST.SSN = RCPT.SSN;
-- +---------------+---------------+---------+
-- | SSN           | MOBILE_NO     | ACCT_NO |
-- +---------------+---------------+---------+
-- | 5707121111000 | 010-1111-1111 | 656421  |
-- | 5707121111000 | 010-1111-1111 | 578221  |
-- | 6508112222333 | 010-6666-6666 | 658720  |
-- | 6912081111222 | 010-2222-2222 | NULL    |
-- | 7105252222000 | 010-4444-4444 | NULL    |
-- | 7706302222111 | 010-5555-5555 | 668721  |
-- | 8204073333111 | 010-7777-7777 | 554520  |
-- | 8204073333111 | 010-7777-7777 | 275123  |
-- | 8204073333111 | 010-7777-7777 | 223620  |
-- | 8311221111333 | 010-3333-3333 | NULL    |
-- | 8911293333222 | 010-8888-8888 | NULL    |
-- | 9011034444111 | 010-9999-9999 | NULL    |
-- +---------------+---------------+---------+

-- 3) 
-- 계좌 수가 두개 이상이고,
-- 모든 수신잔액의 총합이 50만원 이상인 고객의
-- 주민등록번호, 이름, 휴대전화, 계좌 수, 수신잔액의 총합을 
-- 주민등록번호 기준으로 오름차순 정렬

-- 1번 풀이 : HAVING절 사용
---- (1) sub query 계좌 수가 두개 이상이고, 모든 수신잔액의 총합이 50만원 이상인 고객 테이블 생성
SELECT
    SSN,
    COUNT(ACCT_NO) AS ACCT_CNT,
    SUM(RCPT_AMT) AS BAL_SUM
FROM RCPT_ACCT
GROUP BY SSN
HAVING ACCT_CNT >= 2 AND BAL_SUM > 500000;

---- (2) merge
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO, RCPT.ACCT_CNT, RCPT.BAL_SUM
FROM CUST_PARTY AS CUST INNER JOIN -- LEFT JOIN시, 조건에 미일치 유저들도 출력은 되나 계좌 정보가 NULL.
    (
        SELECT SSN,
            COUNT(ACCT_NO) AS ACCT_CNT,
            SUM(RCPT_AMT) AS BAL_SUM
        FROM RCPT_ACCT
        GROUP BY SSN
        HAVING ACCT_CNT >= 2 AND BAL_SUM > 500000
        ) AS RCPT  
ON CUST.SSN = RCPT.SSN
ORDER BY CUST.SSN ASC;



-- 2번 풀이 : WHERE절 사용
---- (1) sub query 계좌 수 & 모든 수신잔액의 총합 테이블 생성
SELECT
    SSN,
    COUNT(ACCT_NO) AS ACCT_CNT,
    SUM(RCPT_AMT) AS BAL_SUM
FROM RCPT_ACCT
GROUP BY SSN;

---- (2) merge & WHERE 절로 조건문 추가
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO, RCPT.ACCT_CNT, RCPT.BAL_SUM
FROM CUST_PARTY AS CUST LEFT JOIN 
    (
        SELECT SSN,
            COUNT(ACCT_NO) AS ACCT_CNT,
            SUM(RCPT_AMT) AS BAL_SUM
        FROM RCPT_ACCT
        GROUP BY SSN
        ) AS RCPT  
ON CUST.SSN = RCPT.SSN
WHERE ACCT_CNT >= 2 AND BAL_SUM > 500000
ORDER BY RCPT.BAL_SUM DESC;

Reference

  • 칼퇴족 김대리는 알고 나만 모르는 SQL - 책밥

Related posts