You& Data_Science & Life

SQL_12_DML(Data Manipulataion Language)

30 Aug 2023

2) 데이터 삭제 (DELETE)

(1) 테이블의 모든 행 삭제

DELETE FROM table_nm:
-- WHERE절을 사용하지 않으면 모든 행이 삭제되기에 조심해야한다.

(2) 테이블의 부분 행 삭제

DELETE FROM table_nm
WHERE conditional

예제

Q1. 앞서 삽입한 아래 정보를 삭제해보자

- SSN : 5508151111222
- PARTY_NM : MJ YOO
- CUST_ID : 8828
- TEL_NO : 02-312-0111
- MOBILE_NO : 010-1122-1111

DELETE FROM CUST_PARTY 
WHERE SSN IN ('5508151111222');

TIP

  • SQL 에서느 실행한 내용을 되돌릴 수 있는 기능이 없다. 때문에 DELETE문을 사용할 땐 항상 주의를 기울여야 한다!



3) 데이터 수정 (UPDATE)

(1) 모든 행의 데이터 수정

UPDATE table_nm
SET col_A = 'change_to_value';
-- col_A의 모든값을 'change_to_value'로 변경시키기에, WHERE절이 없는 UPDATE문은 주의가 필요하다.

(2) 특정한 행의 데이터 수정

UPDATE table_nm
SET col_nm = change_to_value
WHERE 조건절;

예제

Q1. CUST_PARTY테이블에서, SSN이 9011034444111인 고객의 전화번호를 010-9988-5555로 수정해보자.

- SSN : 9011034444111
- MOBILE_NO : 010-1122-1111

UPDATE CUST_PARTY
SET MOBILE_NO = '010-9988-5555'
WHERE SSN IN ('9011034444111');
-- +---------------+----------+---------+---------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     |
-- +---------------+----------+---------+---------------+---------------+
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9988-9999  |
-- +---------------+----------+---------+---------------+---------------+

-- +---------------+----------+---------+---------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     |
-- +---------------+----------+---------+---------------+---------------+
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9988-5555  |
-- +---------------+----------+---------+---------------+---------------+



2. 테이블 조작 DDL

: DDL(Data Definition Language)

  • CREATE TABLE 문법은 DBMS에 따라 다르기에 각각의 DBMS설명문 참고가 필요하다.

1) 테이블 생성

(1) 일반적 테이블 생성

: 테이블을 생성하려면, 아래의 정의가 필요하다.

  • 테이블명
  • 열이름
  • 데이터 타입 (형식)
  • 데이터 정의
CREATE TABLE table_nm
(
--  열이름     데이터 형식(크기)   정의
    col_nm_1  CHAR  (10)      PRIMARY KEY
                              REFERENCES table_1 (table_1_key_col_nm) -- 키 값은 NULL이 들어갈 수 없으며, unique해야 하며, 변경하거나 업데이트 될 수 없다.
    col_nm_2  CHAR  (10)      NOT NULL, -- NULL값이 쓰여지면 오류 발생
    col_nm_3  INTEGER  (10)   NOT NULL DEFAULT 1, -- 값을 지정하지 않으면 자동으로 1이 저장됨.
    col_nm_4  VARCHAR  (100)  NULL
);
에시) ORDERS TABLE
CREATE TABLE ORDERS
(
    ORDER_ID  CHAR  (10)      PRIMARY KEY,
    CUST_ID   CHAR  (4)       NOT NULL REFERENCES CUSTOMERS (CUST_ID),
    EMP_ID    CHAR  (5)       NOT NULL,
    ORDER_DT  DATETIME        NOT NULL
);
-- CUST_ID   CHAR  (4)       NOT NULL REFERENCES CUSTOMERS (CUST_ID) 
-- CUST_ID는 CUSTOMERS 테이블의 PRIMARY KEY로 지정되어 있는 값인데,
-- ORDERS 테이블에 CUST_ID를 외래키로 지정하겠다는 의미이고
-- 이는 ORDERS 테이블에서 사용되는 CUST_ID는 반드시 CUSTOMERS테이블에 있는 값이어야 함을 의미한다.

+ 데이터 형식

타입 데이터 형식 설명
문자형 CHARACTER(n)
or CHAR(n)		 고정 길이의 문자 데이터를 4000Byte저장 가능.
고정폭 n-문자열로 필요한 만큼 공백으로 채워진다.
  NATIONAL VARYING(n)
or NCHAR(n)		 CHAR 타입과 기본적으로 같은 공간 관리를 한다.
다양한 언어의 문자값을 저장 및 조회 할 수 있는 기능을 갖고 있음.
  CHARACTER VARYING(n)
or VARCHAR(n)		 N문자의 최대 크기를 가진 가변폭 문자열.
입력되는 문자의 길이가 정의된 공간의 길이보다 적더라도, 나머지 공간을 여백으로 채우지 않고, 필요한 공간만 사용한다.
  NVARCHAR(n) 가변폭 NCHAR문자열이다.
숫자형 BIT 단일 비트값
  NUMERIC(p.s)
or DECIMAL(p.s)		 p : 전체 자리값 (소수점 왼쪽과 오른쪽이 둘 다 포함)
s : 소수점 이하 자릿수
789.123 -> p:6 s:3
  FLOAT 실수값
  INTERGER
or INT		 4Byte의 정수값
날짜 및 시간 DATE 날짜값
(ex. 2023-01-01)
  TIME 시간값
(ex. 13:22:05)
  TIMESTAMP DATE와 TIME이 하나의 변수로 결합된 형태
2023-01-01 13:22:05

※ CHAR VS VARCHAR

img_src : https://www.scaler.com

(2) 하위 쿼리에 의해 검색된 테이블과 동일한 구조로 테이블 생성

: 하위 쿼리와 동일한 구조의 테이블을 생성하는 방법

CREATE TABLE table_nm AS
    SELECT col_nm_1, col_nm_2 FROM subquery_table_nm;

예제

Q1. 아래와 같은 정보의 CUST_PARTY_2 table을 생성해보자.

SSN : CHAR (13), 기본키
PARTY_NM : VARCHAR (20), NULL값 허용 안 됨
CUST_ID : CHAR (4), NULL값 허용 안 됨
TEL_NO : VARCHAR (20), NULL값 허용 됨
MOBILE_NM : VARCHAR (20), NULL값 허용 됨

CREATE TABLE CUST_PARTY_2
(
    SSN CHAR (13)           PRIMARY KEY,
    PARTY_NM VARCHAR (20)   NOT NULL,
    CUST_ID CHAR (4)        NOT NULL,
    TEL_NO VARCHAR (20)     NULL,
    MOBILE_NM VARCHAR (20)  NULL

);

2) 테이블 변경 & 삭제

(1) 테이블 열 추가

ALTER TABLE table_nm_to_update
ADD (col_nm_to_add data_type (size));
--ex)
ALTER TABLE CUSTOMERS
ADD (HOBBY CHAR (20));

(2) 테이블 내 데이터 구조 변경

ALTER TABLE table_nm_to_update
MODIFY (col_nm_to_add data_type_to_change (size));

(3) 테이블명 변경

RENAME table_nm_before TO table_nm_after;

(4) 테이블명 삭제

DROP TABLE table_nm_to_delete;

Reference

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

SQL_13_DML(Data Manipulataion Language)

22 Aug 2023

1. 데이터 조작 DML

: DML(Data Manipulataion Language)

1) 데이터 삽입 (INSERT)

(1) 완전한 행 삽입 (열이름 미지정)

INSERT INTO table_nm
VALUES (val_1, val_2, val_3);

(2) 완전한 행 삽입 (열이름 지정)

: 열 이름을 지정한 후 값을 삽입하기에, 오류를 줄일 수 있음.

INSERT INTO table_nm(col_1, col_2, col_3)
VALUES (val_1, val_2, val_3);

(3) 부분 행 삽입

: 열 이름을 지정하고, 해당 열 이름에 매칭되는 값을 VALUES 키워드 뒤에 순서대로 작성

INSERT INTO table_nm(col_1, col_3)
VALUES (val_1, val_3);

(4) SQL 문장 결과를 삽입

: SQL문장 전체를 삽입하는 경우, VALUES키워드 대신, SELCT 열이름 FROM 테이블명 WHERE 조건절;을 넣어주면 됨.

INSERT INTO table_1(col_1, col_2, col_3)
SELECT col_1, col_2, col_3 FROM table_2 WHERE conditional;

예제

Q1. CUST_PARTY 테이블에 아래의 값들을 삽입해 보자.

- SSN : 5508151111222
- PARTY_NM : MJ YOO
- CUST_ID : 8828
- TEL_NO : 02-312-0111
- MOBILE_NO : 010-1122-1111

INSERT INTO CUST_PARTY(SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO)
VALUES ('5508151111222', 'MJ YOO', '8828', '02-312-0111', '010-1122-1111');

Q2. CUST_PARTY 테이블에 아래의 값들을 삽입해 보자. 단, MOBILE_NO 열은 NULL을 허용하는 열이다.(부분행 삽입)

- SSN : 5508151111223
- PARTY_NM : MJ YOO
- CUST_ID : 8828
- TEL_NO : 02-312-0111

-- 1) (필요시) NULL값 허용 허용
-- 테이블의 특정 컬럼이 NULL값을 허용하게 하는 방법
alter table CUST_PARTY MODIFY COLUMN MOBILE_NO varchar(45);
-- 테이블의 특정 컬럼이 NULL값을 허용하지 않게 하는 방법
alter table CUST_PARTY modify column [칼럼 이름] [칼럼 타입] not null;

-- 2) 부분 행 삽입 
INSERT INTO CUST_PARTY(SSN, PARTY_NM, CUST_ID, TEL_NO)
VALUES ('5508151111223', 'MJ YOO', '8828', '02-312-0111');

TIP

  • 열 이름을 지정하면, 삽입하는 열의 순서가 바뀌어도, 열 이름과 값이 대응하면 결과에 영향이 없다.



2) 데이터 삭제 (DELETE)

(1) 테이블의 모든 행 삭제

DELETE FROM table_nm:
-- WHERE절을 사용하지 않으면 모든 행이 삭제되기에 조심해야한다.

(2) 테이블의 부분 행 삭제

DELETE FROM table_nm
WHERE conditional

예제

Q1. 앞서 삽입한 아래 정보를 삭제해보자

- SSN : 5508151111222
- PARTY_NM : MJ YOO
- CUST_ID : 8828
- TEL_NO : 02-312-0111
- MOBILE_NO : 010-1122-1111

DELETE FROM CUST_PARTY 
WHERE SSN IN ('5508151111222');

TIP

  • SQL 에서느 실행한 내용을 되돌릴 수 있는 기능이 없다. 때문에 DELETE문을 사용할 땐 항상 주의를 기울여야 한다!



3) 데이터 수정 (UPDATE)

(1) 모든 행의 데이터 수정

UPDATE table_nm
SET col_A = 'change_to_value';
-- col_A의 모든값을 'change_to_value'로 변경시키기에, WHERE절이 없는 UPDATE문은 주의가 필요하다.

(2) 특정한 행의 데이터 수정

UPDATE table_nm
SET col_nm = change_to_value
WHERE 조건절;

예제

Q1. CUST_PARTY테이블에서, SSN이 9011034444111인 고객의 전화번호를 010-9988-5555로 수정해보자.

- SSN : 9011034444111
- MOBILE_NO : 010-1122-1111

UPDATE CUST_PARTY
SET MOBILE_NO = '010-9988-5555'
WHERE SSN IN ('9011034444111');
-- +---------------+----------+---------+---------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     |
-- +---------------+----------+---------+---------------+---------------+
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9988-9999  |
-- +---------------+----------+---------+---------------+---------------+

-- +---------------+----------+---------+---------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     |
-- +---------------+----------+---------+---------------+---------------+
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9988-5555  |
-- +---------------+----------+---------+---------------+---------------+


+ 연습문제

예제

-- 1) EX_1
-- EMPLOYEE 테이블에서 KE LEE와 JM CHA직원의 데이터를 삭제하라
DELETE FROM EMPLOYEE
WHERE EMP_ID IN ('5466', '1270');
-- WHERE NM IN ('KE LEE', 'JM CHA'); -- 이렇게 이름으로 삭제할 경우, 동명이인의 이름도 함께 삭제될 수 있음.


-- 2) EX_2
-- EMP_ID가 15501인 직원의 내선번호를 5800으로 변경하시오.
UPDATE EMPLOYEE
SET TEL_NO = 5800
WHERE EMP_ID IN ('15501');
-- WHERE NM IN ('KE LEE', 'JM CHA'); -- 이렇게 이름으로 삭제할 경우, 동명이인의 이름도 함께 삭제될 수 있음.
-- BEFROE
-- +--------+-------+----------+--------+
-- | EMP_ID | GRADE | NM       | TEL_NO |
-- +--------+-------+----------+--------+
-- | 15501  | 8     | YK MO    | 5740   |

-- AFTER
-- +--------+-------+----------+--------+
-- | EMP_ID | GRADE | NM       | TEL_NO |
-- +--------+-------+----------+--------+
-- | 15501  | 8     | YK MO    | 5800   |

Reference

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

SQL_11_SubQuery

20 Aug 2023

1. Window

1) LEAD / LAG

  • LEAD : N줄 밀리기
  • LAG : N줄 당겨오기 
    -- LEAD
LEAD([타겟필드명], [당길 N줄수]) OVER (ORDER BY 컬럼)
LEAD([타겟필드명], [당길 N줄수]) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
-- LAG
LAG([타겟필드명], [밀 N줄수]) OVER (ORDER BY 컬럼)
LAG([타겟필드명], [밀 N줄수]) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

2) RANK / DENSE_RANK / ROW_NUMBER

  • RANK : 동일한 순위일 경우, 동일한 순위를 매기고, 다음 순위는 N개만큼 건너뜀
  • DENSE_RANK : 동일한 순위일 경우, 동일한 순위를 매기고, 다음 순위는 직전순위 + 1
  • ROW_NUMBER : 동일한 순위라도, 서로 다른 순위를 매김
RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
DENSE_RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
ROW_NUMBER() OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

3) FRAME_1 : 이동 평균

  • [n] preceding : 현재 로우를 기준으로 위 n줄 집계 -> 이동평균
  • [n] following : 현재 로우를 기준으로 위 n줄 집계
AVG([타겟필드명]) OVER(ORDER BY [정렬컬럼] ROWS BETWEEN 2 PRECEDING) -- 오늘 포함 이전 3일 평규
AVG([타겟필드명]) OVER(ORDER BY [정렬컬럼] ROWS BETWEEN 2 FOLLOWING) -- 오늘 포함 이후 3일 평균 
AVG([타겟필드명]) OVER(ORDER BY [정렬컬럼] ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING) -- 오늘 포함 이전 1일 ~ 이후  1일 평균

4) FRAME_2 : 누적합

  • [n] unbounded preceding : 현재 로우를 기준으로 이전 모든 줄 집계 -> 누적합
  • [n] unbounded preceding : 현재 로우를 기준으로 위 n줄 집계 -> 이동평균
SUM([타겟필드명]) OVER(ORDER BY [정렬컬럼] ROWS UNBOUNDED PRECEDING) -- 오늘 포함 과거 모든 일자 합계

Reference

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

SQL_11_SubQuery

16 Aug 2023

1. 하위 쿼리

: 하위 쿼리(sub-query)란 SQL문장에 속하는 또 다른 SQL문장으로, 잘 사용하면 복잡한 SQL문장을 간단히 만들며, DBMS의 데이터 처리 속도를 빠르게 향상시킬 수 있다. 실무에서 주로 사용되는 Sub-Query를 From절에 사용시 아래 2가지 용도로 주로 사용하게 된다. - (1) 조건에 맞는 대상자 선정 후 요약 : 이 경우, 테이블의 별칭을 반드시 주어야 한다.(별칭이 없을 시 에러 발생) - (2) 테이블 조인 시

1) FROM절의 하위 쿼리

(1) 조건에 맞는 대상자 선정 후 요약

SELECT COL_1, COL_2
FROM (
    SELECT * 
    FROM TABLE_1_NM
    WHERE CONDITIONAL
    ) AS ABBV
WHERE CONDITIONAL
-- 이 경우, 테이블의 별칭을 반드시 주어야 한다.(별칭이 없을 시 에러 발생)

(2) 테이블 조인 시

SELECT TABLE_1.COL_KEY, TABLE_2.COL_B
FROM TABLE_1 LEFT OUTER JOIN (
    SELECT COL_KEY, COL_B
    FROM TABLE_2
    WHERE CONDITIONAL
    ) AS TABLE_2
ON TABLE_1.COL_KEY = TABLE_2.COL_KEY;

예제

-- 1) 조건에 맞는 데이터 추출 후 요약하기
-- RCPT_ACCT 테이블을 활용하여, 
-- 현재 400000 이상의 잔액이 있으며 & 살아있는 계좌를 보유한 고객과 미보유한 고객의 수를 나타내 보자

---- (step_1) 400000 이상의 잔액이 있으며 & 살아있는 계좌 하위 쿼리 생성
SELECT DISTINCT SSN
        FROM RCPT_ACCT
        WHERE CNCL_DT IS NULL AND 
        RCPT_AMT > 400000;
-- +---------------+
-- | SSN           |
-- +---------------+
-- | 8204073333111 |
-- | 5707121111000 |
-- | 7706302222111 |
-- +---------------+


---- (step_2) 하위 쿼리를 고객 테이블에 LEFT OUTER JOIN
SELECT *
FROM CUST_PARTY AS CUST 
LEFT OUTER JOIN (SELECT DISTINCT SSN
                FROM RCPT_ACCT
                WHERE CNCL_DT IS NULL AND 
                RCPT_AMT > 400000
                ) AS RCPT
ON CUST.SSN = RCPT.SSN;
-- +---------------+----------+---------+---------------+---------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     | SSN           |
-- +---------------+----------+---------+---------------+---------------+---------------+
-- | 5707121111000 | AR KIM   | 5670    | 02-555-6678   | 010-1111-1111 | 5707121111000 |
-- | 6508112222333 | JH RYU   | 3574    | 02-6666-4444  | 010-6666-6666 | NULL          |
-- | 6912081111222 | SH HONG  | 2357    | 031-4456-9887 | 010-2222-2222 | NULL          |
-- | 7105252222000 | JH KIM   | 8988    | 032-333-1111  | 010-4444-4444 | NULL          |
-- | 7706302222111 | JH LEE   | 7702    | 033-111-3355  | 010-5555-5555 | 7706302222111 |
-- | 8204073333111 | YC JUNG  | 5670    | 02-2222-1111  | 010-7777-7777 | 8204073333111 |
-- | 8311221111333 | MK KANG  | 3977    | 051-999-8888  | 010-3333-3333 | NULL          |
-- | 8911293333222 | JH JUN   | 6989    | 031-224-2222  | 010-8888-8888 | NULL          |
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9999-9999 | NULL          |
-- +---------------+----------+---------+---------------+---------------+---------------+

---- (step_3) 조건에 맞는 유저를 카운팅하기 위한 레이블 컬럼 생성
SELECT *,  
    CASE WHEN RCPT.SSN IS NOT NULL THEN 'O'
    ELSE 'X' END AS FLAG
FROM CUST_PARTY AS CUST 
LEFT OUTER JOIN (SELECT DISTINCT SSN
                FROM RCPT_ACCT
                WHERE CNCL_DT IS NULL AND 
                RCPT_AMT > 400000
                ) AS RCPT
ON CUST.SSN = RCPT.SSN;
-- +---------------+----------+---------+---------------+---------------+---------------+------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO        | MOBILE_NO     | SSN           | FLAG |
-- +---------------+----------+---------+---------------+---------------+---------------+------+
-- | 5707121111000 | AR KIM   | 5670    | 02-555-6678   | 010-1111-1111 | 5707121111000 | O    |
-- | 6508112222333 | JH RYU   | 3574    | 02-6666-4444  | 010-6666-6666 | NULL          | X    |
-- | 6912081111222 | SH HONG  | 2357    | 031-4456-9887 | 010-2222-2222 | NULL          | X    |
-- | 7105252222000 | JH KIM   | 8988    | 032-333-1111  | 010-4444-4444 | NULL          | X    |
-- | 7706302222111 | JH LEE   | 7702    | 033-111-3355  | 010-5555-5555 | 7706302222111 | O    |
-- | 8204073333111 | YC JUNG  | 5670    | 02-2222-1111  | 010-7777-7777 | 8204073333111 | O    |
-- | 8311221111333 | MK KANG  | 3977    | 051-999-8888  | 010-3333-3333 | NULL          | X    |
-- | 8911293333222 | JH JUN   | 6989    | 031-224-2222  | 010-8888-8888 | NULL          | X    |
-- | 9011034444111 | SH LEE   | 5570    | 033-333-3333  | 010-9999-9999 | NULL          | X    |
-- +---------------+----------+---------+---------------+---------------+---------------+------+




---- (step_4) 레이블 컬럼의 값을 그룹핑하여, 최종 카운팅
---- (4-1) 조건에 부합하는 유저만 카운팅
SELECT 
    CASE WHEN RCPT.SSN IS NOT NULL THEN 'O'
    ELSE 'X' END AS FLAG,
    SUM(
        CASE WHEN RCPT.SSN IS NOT NULL THEN 1
        ELSE 0 END
        ) AS CNT
FROM CUST_PARTY AS CUST 
LEFT OUTER JOIN (SELECT DISTINCT SSN
                FROM RCPT_ACCT
                WHERE CNCL_DT IS NULL AND 
                RCPT_AMT > 400000
                ) AS RCPT
ON CUST.SSN = RCPT.SSN
GROUP BY 1;
-- +------+------+
-- | FLAG | CNT  |
-- +------+------+
-- | O    |    3 |
-- | X    |    0 |
-- +------+------+
---- (4-2) 조건 부합 여부별 유저 수 카운팅
SELECT   
    CASE WHEN RCPT.SSN IS NOT NULL THEN 'O'
    ELSE 'X' END AS FLAG,
    COUNT(*) 
FROM CUST_PARTY AS CUST 
LEFT OUTER JOIN (SELECT DISTINCT SSN
                FROM RCPT_ACCT
                WHERE CNCL_DT IS NULL AND 
                RCPT_AMT > 400000
                ) AS RCPT
ON CUST.SSN = RCPT.SSN
GROUP BY FLAG;
-- +------+----------+
-- | FLAG | COUNT(*) |
-- +------+----------+
-- | O    |        3 |
-- | X    |        6 |
-- +------+----------+


---- (+) 하위 쿼리 생성시, DISTINCT 미적용시
---- 조건에 해당하는 동일 유저의 2개의 계좌가 모두 카운팅되어, 
---- OUTER JOIN 과정에서, 2개의 계좌를 가진 유저의 행이 하나 복사되고,
---- 그 결과, 카운팅한 결과에서 조건에 해당하는 유저가 총 3명이 아니라 4명이라고 잘못 나오게 됨(4명이나 1명이 중복된 상태)
SELECT 
    CASE WHEN RCPT.SSN IS NOT NULL THEN 'O'
    ELSE 'X' END AS FLAG,
    SUM(
        CASE WHEN RCPT.SSN IS NOT NULL THEN 1
        ELSE 0 END
        ) AS CNT
FROM CUST_PARTY AS CUST 
LEFT OUTER JOIN (SELECT SSN
                FROM RCPT_ACCT
                WHERE CNCL_DT IS NULL AND 
                RCPT_AMT > 400000
                ) AS RCPT
ON CUST.SSN = RCPT.SSN
GROUP BY FLAG;

TIP

  • 데이터 분석 시 키값이 되는 열들은 중복 없이 나열할 것
  • 중복을 제거한 형태의 테이블을 만든 후 조인해야 한다. (중복 미 제거시, 오류가 발생할 확률이 높아짐)



2) WHERE 조건절의 하위 쿼리

(1) IN을 사용한 WHERE 조건절의 하위 쿼리

: WHERE 조건절에서 하위 쿼리는 IN 연산자와 함게 사용된다. 전체 모집단에서 특정 세그먼트만 추출할 때 WHERE 조건절의 하위쿼리가 유용하게 사용된다.

-- 단일 컬럼 서브쿼리 
SELECT COL_1, COL_2
FROM TABLE_1_NM
WHERE COL_NM_TARGET IN (
    SELECT COL_NM_TARGET 
    FROM TABLE_2_NM
    WHERE CONDITIONAL
    );

-- 다중 컬럼 서브쿼리 
SELECT COL_1, COL_2
FROM TABLE_1_NM
WHERE COL_NM_TARGET_1, COL_NM_TARGET_2  IN (
    SELECT COL_NM_TARGET_1, COL_NM_TARGET_2
    FROM TABLE_2_NM
    WHERE CONDITIONAL
    );

예제

-- RCPT_ACCT 테이블과 CUST_PARTY 테이블을 이용하여,
-- 현재 사라있는 신용카드 보유 고객의,
-- 주민등록번호, 이름, 아이디, 자택번호 및 휴대폰 번호를 나타내보자.(WHERE 조건절 하위 쿼리 사용)

---- (step_1)  살아있는 계좌 하위 쿼리 생성
SELECT DISTINCT SSN
FROM RCPT_ACCT
WHERE CNCL_DT IS NULL;
-- +---------------+
-- | SSN           |
-- +---------------+
-- | 8204073333111 |
-- | 5707121111000 |
-- | 6508112222333 |
-- | 7706302222111 |
-- +---------------+

---- (step_2) CUST_PARTY 테이블의 주민등록번호, 이름, 아이디, 자택번호 및 휴대폰 번호 정보 출력
SELECT SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO
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 |
-- +---------------+----------+---------+---------------+---------------+

---- (step_3) CUST_PARTY 테이블에서, WHERE절 하위쿼리를 활용한 조건 추가
SELECT SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO
FROM CUST_PARTY
WHERE SSN IN (
    SELECT DISTINCT SSN
    FROM RCPT_ACCT
    WHERE CNCL_DT IS NULL
);
-- +---------------+----------+---------+--------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO       | MOBILE_NO     |
-- +---------------+----------+---------+--------------+---------------+
-- | 8204073333111 | YC JUNG  | 5670    | 02-2222-1111 | 010-7777-7777 |
-- | 5707121111000 | AR KIM   | 5670    | 02-555-6678  | 010-1111-1111 |
-- | 6508112222333 | JH RYU   | 3574    | 02-6666-4444 | 010-6666-6666 |
-- | 7706302222111 | JH LEE   | 7702    | 033-111-3355 | 010-5555-5555 |
-- +---------------+----------+---------+--------------+---------------+

TIP

---- (+) CUST_PARTY 테이블에서 SSN가 5707121111000인 유저만 출력해보자
---- 이때, 조건문으로 사용된 하위쿼리의 결과물이 단일행이라면, 
---- WHRER절의 IN 대신, '='을 사용할 수도 있다.
SELECT SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO
FROM CUST_PARTY
WHERE SSN = (
    SELECT DISTINCT SSN
    FROM RCPT_ACCT
    WHERE SSN = '5707121111000'
);
-- +---------------+----------+---------+-------------+---------------+
-- | SSN           | PARTY_NM | CUST_ID | TEL_NO      | MOBILE_NO     |
-- +---------------+----------+---------+-------------+---------------+
-- | 5707121111000 | AR KIM   | 5670    | 02-555-6678 | 010-1111-1111 |
-- +---------------+----------+---------+-------------+---------------+


---- (+) 그러나 아래처럼, 조건절의 결과물이 1개이상의 복수행일 때 WHERE 절에 '='을 사용하면 에러가 발생한다.
SELECT SSN, PARTY_NM, CUST_ID, TEL_NO, MOBILE_NO
FROM CUST_PARTY
WHERE SSN = (
    SELECT DISTINCT SSN
    FROM RCPT_ACCT
    WHERE SSN IN ('5707121111000', '7706302222111')
);
-- ERROR 1242 (21000): Subquery returns more than 1 row

3) Correlated Subquery

: 하나의 행에 복수의 행의 정보로 집계된 값을 삽입하는 방법

  • 활용 1) 이동 평균
    계산시 특정 시점을 기준으로 과거 N일치의 값을 불러와야함.
  • 활용 2) 누적 합계
    계산시 특정 시점을 기준으로 과거 모든 일수를 불러옴. 
    -- 이동 평균
SELECT 
t1.date, 
m1.value,
(select avg(value)
 from sample_table t2
 WHERE t2.date BETWEEN DATE_ADD(t1.date, INTERVAL -1 DAY) AND DATE_ADD(t1.date, INTERVAL +1 DAY))
FROM sample_table t1


+ 연습문제

  • CUST_PARTY 테이블
  • RCPT_ACCT 테이블

예제

-- 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 열_1, 열_2 FROM 테이블_1 WHERE 조건절
UNION
SELECT 열_1, 열_2 FROM 테이블_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 열_1, 열_2 FROM 테이블_1 WHERE 조건절
UNION ALL
SELECT 열_1, 열_2 FROM 테이블_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 ;


+ 연습문제

  • DLQ_PARTY
SELECT * FROM DLQ_PARTY;
+---------------+---------+------------+------------+--------------+----------+
| SSN           | ACCT_NO | DLQ_ST     | DLQ_END    | DLQ_DURATION | CURR_DLQ |
+---------------+---------+------------+------------+--------------+----------+
| 6912081111222 | 32110   | 2012-07-30 | 2012-08-15 |           16 | N        |
| 8204073333111 | 88930   | 2012-09-21 | 2012-10-01 |           10 | N        |
| 8204073333111 | 35780   | 2013-01-26 | 2013-01-29 |            3 | N        |
| 7706302222111 | 78320   | 2013-11-01 | NULL       |           31 | Y        |
| 6912081111222 | 87120   | 2013-10-01 | NULL       |           62 | Y        |
| 8204073333111 | 56830   | 2013-11-18 | 2013-11-28 |           10 | N        |
| 8311221111333 | 78720   | 2013-11-14 | NULL       |           18 | Y        |
| 8311221111333 | 98730   | 2013-11-16 | NULL       |           16 | Y        |
| 6508112222333 | 57830   | 2012-12-01 | 2012-12-02 |            1 | N        |
| 6508112222333 | 78770   | 2013-09-19 | NULL       |           74 | Y        |
+---------------+---------+------------+------------+--------------+----------+

-- 1) 연체일이 30일이 안된 고객들에게 문자를 보내고자 할 때
-- 대상이 되는 고객의 주민등록번호, 이름, 휴대폰번호를 나열해보자.
---- (step_1) 연체일이 30일이 안된 고객 명단 추출
SELECT DISTINCT SSN 
FROM DLQ_PARTY
WHERE DLQ_DURATION < 30 AND CURR_DLQ = 'Y';

---- (step_2) 고객정보 테이블과 Merge
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO
FROM CUST_PARTY AS CUST INNER JOIN (
    SELECT DISTINCT SSN 
    FROM DLQ_PARTY
    WHERE DLQ_DURATION < 30 AND CURR_DLQ = 'Y'
    ) AS DLQ
ON CUST.SSN = DLQ.SSN;

-- 2) 현재 연체중이거나, 과거 10일 이상 연체 기록이 있는 고객들을 제외한
-- 대출 가능 고객 리스트를 뽑아보자 (외부 조인 활용)
---- (step_1) 현재 연체중이거나, 과거 10일 이상 연체 기록이 있는 유저 추출
SELECT DISTINCT SSN, 1 AS FLAG
FROM DLQ_PARTY
WHERE CURR_DLQ = 'Y' OR (DLQ_DURATION >= 10 AND CURR_DLQ = 'N');
-- +---------------+------+
-- | SSN           | FLAG |
-- +---------------+------+
-- | 6912081111222 |    1 |
-- | 8204073333111 |    1 |
-- | 7706302222111 |    1 |
-- | 8311221111333 |    1 |
-- | 6508112222333 |    1 |
-- +---------------+------+

---- (step_2) 고객 테이블에 LEFT OUTER MERGE
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO, DLQ.FLAG
FROM CUST_PARTY AS CUST LEFT OUTER JOIN (
    SELECT DISTINCT SSN, 1 AS FLAG
    FROM DLQ_PARTY
    WHERE CURR_DLQ = 'Y' OR (DLQ_DURATION >= 10 AND CURR_DLQ = 'N')
    ) AS DLQ
ON CUST.SSN = DLQ.SSN;
-- +---------------+----------+---------------+------+
-- | SSN           | PARTY_NM | MOBILE_NO     | FLAG |
-- +---------------+----------+---------------+------+
-- | 5707121111000 | AR KIM   | 010-1111-1111 | NULL |
-- | 6508112222333 | JH RYU   | 010-6666-6666 |    1 |
-- | 6912081111222 | SH HONG  | 010-2222-2222 |    1 |
-- | 7105252222000 | JH KIM   | 010-4444-4444 | NULL |
-- | 7706302222111 | JH LEE   | 010-5555-5555 |    1 |
-- | 8204073333111 | YC JUNG  | 010-7777-7777 |    1 |
-- | 8311221111333 | MK KANG  | 010-3333-3333 |    1 |
-- | 8911293333222 | JH JUN   | 010-8888-8888 | NULL |
-- | 9011034444111 | SH LEE   | 010-9999-9999 | NULL |
-- +---------------+----------+---------------+------+

---- (step_3) FLAG 컬럼을 기준으로, 대출 가능 고객만 필터링
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO
FROM CUST_PARTY AS CUST LEFT OUTER JOIN (
    SELECT DISTINCT SSN, 1 AS FLAG
    FROM DLQ_PARTY
    WHERE CURR_DLQ = 'Y' OR (DLQ_DURATION >= 10 AND CURR_DLQ = 'N')
    ) AS DLQ
ON CUST.SSN = DLQ.SSN
WHERE DLQ.FLAG IS NULL ;
-- +---------------+----------+---------------+
-- | SSN           | PARTY_NM | MOBILE_NO     |
-- +---------------+----------+---------------+
-- | 5707121111000 | AR KIM   | 010-1111-1111 |
-- | 7105252222000 | JH KIM   | 010-4444-4444 |
-- | 8911293333222 | JH JUN   | 010-8888-8888 |
-- | 9011034444111 | SH LEE   | 010-9999-9999 |
-- +---------------+----------+---------------+

-- 3) 위 문제를 WHERE 조건절을 활용하여 작성해보자.
SELECT DISTINCT SSN
FROM DLQ_PARTY
WHERE CURR_DLQ = 'Y' OR (DLQ_DURATION >= 10 AND CURR_DLQ = 'N');
-- +---------------+
-- | SSN           |
-- +---------------+
-- | 6912081111222 |
-- | 8204073333111 |
-- | 7706302222111 |
-- | 8311221111333 |
-- | 6508112222333 |
-- +---------------+

---- (step_2) 고객 테이블의 SSN값을 WHERE 조건절 기반으로 필터링
SELECT SSN, PARTY_NM, MOBILE_NO
FROM CUST_PARTY 
WHERE SSN NOT IN (
    SELECT DISTINCT SSN
    FROM DLQ_PARTY
    WHERE CURR_DLQ = 'Y' OR (DLQ_DURATION >= 10 AND CURR_DLQ = 'N')
    );
-- +---------------+----------+---------------+------+
-- | SSN           | PARTY_NM | MOBILE_NO     | FLAG |
-- +---------------+----------+---------------+------+
-- | 5707121111000 | AR KIM   | 010-1111-1111 | NULL |
-- | 6508112222333 | JH RYU   | 010-6666-6666 |    1 |
-- | 6912081111222 | SH HONG  | 010-2222-2222 |    1 |
-- | 7105252222000 | JH KIM   | 010-4444-4444 | NULL |
-- | 7706302222111 | JH LEE   | 010-5555-5555 |    1 |
-- | 8204073333111 | YC JUNG  | 010-7777-7777 |    1 |
-- | 8311221111333 | MK KANG  | 010-3333-3333 |    1 |
-- | 8911293333222 | JH JUN   | 010-8888-8888 | NULL |
-- | 9011034444111 | SH LEE   | 010-9999-9999 | NULL |
-- +---------------+----------+---------------+------+

-- 4) 현재 연체중인 고객의 최대 연체일 수를 산출한 후,
---- "XXX 고객님의 현재 연체일 수는 XX일 입니다."라는 문구를 만들고,
---- 주민등록번호, 이름, 휴대폰번호, 연체일 수와 함께 나열해보자.

---- (step_1) 현재 연체중인 고객의 최대 연체일 수 산출
SELECT SSN,
    MAX(DLQ_DURATION) AS MAX_DUR
FROM DLQ_PARTY
WHERE CURR_DLQ = 'Y' 
GROUP BY SSN;
-- +---------------+---------+
-- | SSN           | MAX_DUR |
-- +---------------+---------+
-- | 7706302222111 |      31 |
-- | 6912081111222 |      62 |
-- | 8311221111333 |      18 |
-- | 6508112222333 |      74 |
-- +---------------+---------+


---- (step_2) "XXX 고객님의 현재 연체일 수는 XX일 입니다."라는 문구 생성
SELECT CONCAT(CUST.PARTY_NM,'고객님의 현재 연체일 수는 ',DLQ.MAX_DUR,'일 입니다.') AS COMMENT
FROM CUST_PARTY AS CUST INNER JOIN (
    SELECT SSN, 
        MAX(DLQ_DURATION) AS MAX_DUR
    FROM DLQ_PARTY
    WHERE CURR_DLQ = 'Y' 
    GROUP BY SSN
    ) AS DLQ
ON CUST.SSN = DLQ.SSN;
-- +--------------------------------------------------------------+
-- | COMMENT                                                      |
-- +--------------------------------------------------------------+
-- | JH LEE고객님의 현재 연체일 수는 31일 입니다.                 |
-- | SH HONG고객님의 현재 연체일 수는 62일 입니다.                |
-- | MK KANG고객님의 현재 연체일 수는 18일 입니다.                |
-- | JH RYU고객님의 현재 연체일 수는 74일 입니다.                 |
-- +--------------------------------------------------------------+

---- (step_3)주민등록번호, 이름, 휴대폰번호, 연체일 수와 함께 나열
SELECT CUST.SSN, CUST.PARTY_NM, CUST.MOBILE_NO, DLQ.MAX_DUR,
    CONCAT(CUST.PARTY_NM,'고객님의 현재 연체일 수는 ',DLQ.MAX_DUR,'일 입니다.') AS COMMENT
FROM CUST_PARTY AS CUST INNER JOIN (
    SELECT SSN, 
        MAX(DLQ_DURATION) AS MAX_DUR
    FROM DLQ_PARTY
    WHERE CURR_DLQ = 'Y' 
    GROUP BY SSN
    ) AS DLQ
ON CUST.SSN = DLQ.SSN;

-- +---------------+----------+---------------+---------+--------------------------------------------------------------+
-- | SSN           | PARTY_NM | MOBILE_NO     | MAX_DUR | COMMENT                                                      |
-- +---------------+----------+---------------+---------+--------------------------------------------------------------+
-- | 7706302222111 | JH LEE   | 010-5555-5555 |      31 | JH LEE고객님의 현재 연체일 수는 31일 입니다.                 |
-- | 6912081111222 | SH HONG  | 010-2222-2222 |      62 | SH HONG고객님의 현재 연체일 수는 62일 입니다.                |
-- | 8311221111333 | MK KANG  | 010-3333-3333 |      18 | MK KANG고객님의 현재 연체일 수는 18일 입니다.                |
-- | 6508112222333 | JH RYU   | 010-6666-6666 |      74 | JH RYU고객님의 현재 연체일 수는 74일 입니다.                 |
-- +---------------+----------+---------------+---------+--------------------------------------------------------------+

Reference

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

SQL_10_Merge

02 Jul 2023

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 - 책밥