[프로그래머스] 5. JOIN

조인을 할 경우에는

:테이블명에 ALIAS를 붙여 같은 칼럼명끼리의 조인이 가능하게 한다.

조인의 방법

① WHERE 조인 -> 테이블1 , 테이블2 / 테이블1 JOIN 테이블2 WHERE 테이블1.칼럼1 = 테이블2.칼럼1

② LEFT, RIGHT 조인 -> 테이블1 LEFT JOIN 테이블2 ON이나 USING 필수

순서 : FROM -> ON -> JOIN -> WHERE

-> 즉, 내부조인에서 조건을 ON절에 명시하나 WHERE절에 명시하나 결과는 같다.

하지만, 외부 조인에서는 결과가 달라진다.

SELECT d.deptno, sum(e.sal)
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno
WHERE e.sal > 2000
GROUP BY d.deptno
ORDER BY d.deptno;


SELECT d.deptno, sum(e.sal)
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno AND e.sal > 2000
GROUP BY d.deptno
ORDER BY d.deptno;

1. WHERE 절에 급여 조건 명시

2. ON절에 급여 조건 명시

-> 결과

1. DEPT로 조인을하고 WHERE 절에서 2000초과하는 직원을 미리 필터링해

DEPT에 존재하지만 EMP에는 존재하지 않는 직원의 SAL 값이 NULL이므로 SAL 조건 필터링에서 지워버린다.

2. DEPT에 존재하고 EMP에 존재하지 않는 결과도 출력

조인의 표현

LEFT OUTER JOIN

:왼쪽 테이블 기준으로 JOIN
왼쪽 테이블 A 의 모든 데이터와 A와 B 테이블의 중복데이터들이 검색

->LEFT JOIN과 LEFT OUTER JOIN은 완전히 동일

select * from A LEFT OUTER JOIN B ON (A.번호 = B.번호)

select * from A ,B WHERE A.번호(+) = B.번호;

즉,

SELECT *
FROM A LEFT OUTER JOIN B
ON (A.HOUR = B.HOUR)

SELECT *
FROM A,B
WHERE A .HOUR = B.HOUR(+)

내부 조인과 외부 조인

: 내부 조인(Inner Join)을 사용할 경우 조인할 대상이 존재하지 않다면, 해당 데이터는 결과에서 누락 된다.

이를 방지하기 위해서는 아우터 조인을 사용해야 한다.

-> 조인할 대상이 있을 때만 조인할 경우 : 내부조인

-> 조인할 대상이 없을 경우에도 조인할 경우 : 외부조인

조인 특징

조인과 DISTINCT의 위치

-> 아우터 조인시 ON 필수로 필요한데, 여러 개 조인시,

COUNT 사용시 DISTINCT : COUNT(DISTINCT(~))

FROM 원 테이블 JOIN 추가 테이블 ON (추가 조건)

SELECT CP.COMPANY_CODE, CP.FOUNDER,
COUNT(DISTINCT(LM.LEAD_MANAGER_CODE)),COUNT(DISTINCT(SM.SENIOR_MANAGER_CODE)),
COUNT(DISTINCT(MGR.MANAGER_CODE)), COUNT(DISTINCT(EMP.EMPLOYEE_CODE))
FROM COMPANY CP
JOIN LEAD_MANAGER LM ON(CP.COMPANY_CODE=LM.COMPANY_CODE)
JOIN SENIOR_MANAGER SM ON(LM.COMPANY_CODE=SM.COMPANY_CODE)
JOIN MANAGER MGR ON(SM.COMPANY_CODE=MGR.COMPANY_CODE)
JOIN EMPLOYEE EMP ON( MGR.COMPANY_CODE=EMP.COMPANY_CODE)
GROUP BY CP.COMPANY_CODE,CP.FOUNDER
ORDER BY CP.COMPANY_CODE;

같은 칼럼 내 다른 값과 비교할 때 즉, X1=Y2 AND X2=Y1.

-> 셀프 조인 이용

내부 조인(Inner Join)을 사용할 경우 조인할 대상이 존재하지 않다면, 해당 데이터는 결과에서 누락 된다.

이를 방지하기 위해서는 아우터 조인을 사용해야 한다.

[LEFT JOIN | RIGHT JOIN] -> 조인의 기본값은 INNER JOIN으로 OUTER JOIN의 기본값은 LEFT OUTER JOIN

SELECT F1.X, F1.Y
FROM FUNCTIONS F1 INNER JOIN FUNCTIONS F2
ON F1.X=F2.Y AND F2.X=F1.Y
GROUP BY F1.X, F1.Y
HAVING COUNT(*) >1 OR F1.X<F1.Y
ORDER BY F1.X;

UNION과 UNION ALL

: UNION은 수학적 합집합으로 정렬작업 수행한다.

: UNION ALL은 정렬작업 수행하지 않고, 중복 발생 가능

NATURAL 조인에서 사용하는 USING 조건절 (선택)

: NATURAL 조인은 조인이 되는 테이블의 성격과 칼럼명이 동일해야 사용 가능하고, USING으로 원하는 칼럼만 선택 가능

-> 같은 이름의 칼럼을 하나로 처리한다.

INNER 조인에서 사용하는 ON 조건절 (필수)

: INNER 조인은 칼럼명을 선택 가능

-> 별개의 칼럼으로 표시한다.

USING 에는 컬럼명이 - 이퀄조인만 해당되며, 두테이블의 동일한 컬럼명이어만 한다.
ON 에는 조건식(a.컬럼 = b.컬럼) - 다양한 조인식이 올 수 있다.

1. 없어진 기록 찾기

/*  입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회 */
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
MINUS
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS

일반적으로 MINUS를 사용한다.

(1) NOT EXISTS

SELECT ANIMAL_ID , NAME
FROM ANIMAL_OUTS OUTS
WHERE NOT EXISTS (SELECT 1 FROM ANIMAL_INS I WHERE OUTS.ANIMAL_ID = I.ANIMAL_ID)
ORDER BY ANIMAL_ID;

#SELECT 1

1은 TRUE의 의미로

WHERE 조건문과 함께 쓰면 해당 조건을 만족하면 1을 반환

다중행 서브 쿼리문에서 WHERE조건절에 (NOT) EXISTS 안의 서브쿼리문에 사용

값의 존재유무를 파악할 때 사용

(2) LEFT JOIN

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
LEFT JOIN
ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID ASC;

-> ID를 기준으로 LEFT JOIN을 하게 되면, OUTS에만 있는 행의 경우 INS에서 NULL로 표시

-> WHERE절에서 NULL로 된 행을 조건으로, 출력

(3) WHERE절 서브쿼리

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
WHERE (SELECT ANIMAL_ID
      FROM ANIMAL_INS INS
      WHERE INS.ANIMAL_ID=OUTS.ANIMAL_ID) IS NULL
ORDER BY ANIMAL_ID ASC;

-> 서브쿼리는 메인쿼리에서 갖다 쓸수 있지만, 메인 쿼리는 서브쿼리에서 갖다 쓸수 없다.

2. 있었는데요 없었습니다.

-- 코드를 입력하세요
/* 일부 동물의 입양일이 잘못 입력되었습니다.
보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요.
이때 결과는 보호 시작일이 빠른 순으로 조회 */

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID=OUTS.ANIMAL_ID AND INS.DATETIME>OUTS.DATETIME
ORDER BY INS.DATETIME;

(1) LEFT JOIN 이용

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS
LEFT JOIN
ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME>OUTS.DATETIME
ORDER BY INS.DATETIME;

(2) TO_CHAR 이용

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE TO_CHAR(INS.DATETIME, 'YYYY-MM-DD HH24') > TO_CHAR(OUTS.DATETIME, 'YYYY-MM-DD HH24')
ORDER BY INS.DATETIME ASC;

#TO_NUMBBER(TO_CHAR ~, ''))

-> 데이터 형식을 넘버로 바꾸기 위해서는 문자로 한번 바꾼 뒤 바꾼다.

3. 오랜 기간 보호한 동물 (1)

(1) 내부조인과 FETCH

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID= OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
FETCH NEXT 3 ROWS ONLY

(2) NOT IN과 FETCH

SELECT NAME,
       DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
FETCH FIRST 3 ROWS ONLY;

(3) FROM 서브쿼리

SELECT NAME, DATETIME
FROM 
(SELECT INS.NAME NAME, INS.DATETIME DATETIME 
FROM ANIMAL_INS INS 
LEFT JOIN 
ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY DATETIME ASC)
WHERE ROWNUM<=3;

(4) RANK 함수 이용

ELECT
    T.NAME AS NAME,
    T.DATETIME AS DATETIME
FROM(
        SELECT
            A.NAME AS NAME,
            A.DATETIME AS DATETIME,
            RANK() OVER (ORDER BY A.DATETIME) AS RANK    
        FROM ANIMAL_INS A
        LEFT OUTER JOIN ANIMAL_OUTS B
        ON A.ANIMAL_ID = B.ANIMAL_ID
        WHERE B.ANIMAL_ID IS NULL
    )T
WHERE T.RANK BETWEEN 1 AND 3

4. 보호소에서 중성화한 동물

(1) 인라인뷰 이용

-- 코드를 입력하세요
--보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
--보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물
--아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM 
(
SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE  INS.ANIMAL_ID=OUTS.ANIMAL_ID AND SEX_UPON_OUTCOME != SEX_UPON_INTAKE 
)
ORDER BY ANIMAL_ID

(2) 외부조인 이용

-- 코드를 입력하세요
--나갈떄 중성화된 동물 아이디와 종, 이름 조회 아이디순
SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_INS INS
RIGHT JOIN
ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID=OUTS.ANIMAL_ID 
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY OUTS.ANIMAL_ID;

(3) FROM 조인 이용

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
        FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
        WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
        AND INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY INS.ANIMAL_ID

(4) LIKE, INSTR 이용

-1. 이전에 Intact 중 -> 이전의 상태가 포함되어있지않은 경우

SELECT DISTINCT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_OUTS O, (
    SELECT * FROM ANIMAL_INS
    WHERE SEX_UPON_INTAKE LIKE 'Intact%'
) I
WHERE O.SEX_UPON_OUTCOME NOT LIKE I.SEX_UPON_INTAKE
AND O.ANIMAL_ID LIKE I.ANIMAL_ID
ORDER BY O.ANIMAL_ID

-2. 이전에 INTACT중에서 이후에 중성화 된 경우

SELECT INS.ANIMAL_ID, INS.ANIMAL_TYPE, INS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID=OUTS.ANIMAL_ID 
AND INS.SEX_UPON_INTAKE LIKE('%Intact%')
AND (OUTS.SEX_UPON_OUTCOME LIKE('%Spayed%')
OR OUTS.SEX_UPON_OUTCOME LIKE ('%Neutered%') )
ORDER BY INS.ANIMAL_ID;

#특정 문자열 포함 여부 확인

LIKE와 INSTR

앞뒤 체크 =%문자열%

SELECT *
  FROM emp
 WHERE ename LIKE '%MI%'

-> LIKE는 T/F 반환

(1) SC로 시작하는 이름 LIKE 'SC%'

(2) ER로 끝나는 이름 LIKE '%ER'

(3) AM을 포함하는 이름 '%AM%'

(4) 'M'과 'I'를 포함하고 있는 이름 '%M%I%'

(1) 대소문자 구분 없이 -> 소문자로 만들어서 -> LOWER() LIKE '문자열%'

(2) SALES 제외하고 -> NOT LIKE 'SALES%'

(3) 중첩 LIKE 문

-> (job LIKE 'SALES%' OR
job LIKE 'MAN%' OR
job LIKE 'ANAL%')

(4) 자리수로 검색 -> LIKE '_____'

(5) 자리수 중 마지막 문자 s-> LIKE '____s'

(6) 자리수 중 셋째자리가 A -> LIKE '__A%'

(7) 특수문자 조회시 ESCAPE -> LIKE '%\_%' ESCAPE '\'

LIKE의 CASE 표현식 활용

SELECT empno
     , ename
     , CASE WHEN ename LIKE '%MI%' THEN 'Y'
            ELSE 'N'
       END AS "포함여부"
  FROM emp
 WHERE JOB = 'CLERK'

INSTR 함수

찾은 위치를 정수형으로 반환 -> 찾으면 1이상, 못찾으면 0

SELECT *
  FROM emp
 WHERE INSTR(ename, 'MI') > 0

-> 정수형 반환 : CASE, DECODE, SUBSTR에 활용가능

CASE 표현식에 활용

SELECT empno
     , ename
     , INSTR(ename, 'MI')
     , CASE WHEN INSTR(ename, 'MI') > 0 THEN 'Y'
            ELSE 'N'
       END AS "포함여부"
  FROM emp
 WHERE JOB = 'CLERK'

SUBSTRING 문자열 자르기

SELECT empno
     , ename
     , INSTR(ename, 'M')
     , SUBSTR(ename, INSTR(ename, 'M'), 2)
  FROM emp
 WHERE JOB = 'CLERK'

-> 이름에서 M위치를 찾아서 해당 위치에서 2자리 문자 자르기

# CASE 문을 활용해, 현재 중성화 여부, 보호전 중성화 여부, 보호후 중성화 여부 체크

animal_id	animal_type	name	이전 중성화 여부	이후 중성화 여부	나중에 중성화 여부
A349996	Cat	Sugar	Y	Y	N
A350276	Cat	Jewel	Y	Y	N
A350375	Cat	Meo	Y	Y	N
A352555	Dog	Harley	Y	Y	N
A352713	Cat	Gia	Y	Y	N
A352872	Dog	Peanutbutter	Y	Y	N
A353259	Dog	Bj	Y	Y	N
A354540	Cat	Tux	Y	Y	N
A354597	Cat	Ariel	Y	Y	N
A354725	Dog	Kia	Y	Y	N
A354753	Dog	Sammy	Y	Y	N
A355519	Dog	Faith	Y	Y	N
A355688	Dog	Shadow	Y	Y	N
A355753	Dog	Elijah	Y	Y	N
A357021	Dog	Queens	Y	Y	N
A357444	Dog	Puppy	Y	Y	N
A357846	Dog	Happy	Y	Y	N
A358697	Dog	Fuzzo	Y	Y	N
A358879	Dog	Simba	Y	Y	N
A361391	Dog	Baby Bear	Y	Y	N
A362103	Dog	Stitch	Y	Y	N
A362383	Dog	*Morado	Y	Y	N
A362707	Dog	Girly Girl	Y	Y	N
A362967	Dog	Honey	Y	Y	N
A363653	Dog	Goofy	Y	Y	N
A364429	Dog	Hugo	Y	Y	N
A365172	Dog	Diablo	Y	Y	N
A365302	Dog	Minnie	Y	Y	N
A367012	Dog	Miller	Y	Y	N
A367438	Dog	Cookie	Y	Y	N
A367747	Dog	Woody	Y	Y	N
A368742	Dog	Stormy	Y	Y	N
A368930	Dog		Y	Y	N
A370439	Dog	Sniket	Y	Y	N
A370507	Cat	Emily	Y	Y	N
A370852	Dog	Katie	Y	Y	N
A371000	Cat	Greg	Y	Y	N
A371102	Dog	Ceballo	Y	Y	N
A371344	Dog	Sailor	Y	Y	N
A371534	Dog	April	Y	Y	N
A373219	Cat	Ella	Y	Y	N
A373687	Dog	Rosie	Y	Y	N
A375393	Dog	Dash	Y	Y	N
A376322	Dog	Mama Dog	Y	Y	N
A376459	Dog	Dora	Y	Y	N
A377750	Dog	Lucy	Y	Y	N
A378348	Dog	Frijolito	Y	Y	N
A378353	Dog	Lyla	N	N	N
A378818	Dog	Zoe	Y	Y	N
A378946	Dog	Mercedes	Y	Y	N
A379998	Dog	Disciple	N	N	N
A380009	Dog	Pickle	Y	Y	N
A380320	Dog	Scooby	Y	Y	N
A380420	Dog	Laika	Y	Y	N
A380506	Dog	Ruby	Y	Y	N
A381173	Dog	Pepper	Y	Y	N
A381217	Dog	Cherokee	Y	Y	N
A382192	Dog	Maxwell 2	N	Y	Y
A382251	Dog	Princess	Y	Y	N
A383036	Cat	Oreo	Y	Y	N
A383964	Dog	Finney	Y	Y	N
A384360	Cat	Jj	Y	Y	N
A384568	Cat	Jedi	Y	Y	N
A385442	Dog	Clyde	Y	Y	N
A386005	Dog	Giovanni	Y	Y	N
A386276	Cat	Tiko	Y	Y	N
A386688	Dog	Punch	Y	Y	N
A387083	Dog	Goldie	Y	Y	N
A387965	Dog	Dakota	Y	Y	N
A388360	Dog	Spider	Y	Y	N
A388691	Dog	Blaze	Y	Y	N
A390222	Dog	Holly	Y	Y	N
A391512	Dog	Rome	Y	Y	N
A391858	Dog	Nellie	Y	Y	N
A392027	Dog	Penny	Y	Y	N
A392075	Dog	Skips	Y	Y	N
A392615	Dog	Chip	Y	Y	N
A394547	Dog	Snickerdoodl	Y	Y	N
A395451	Dog	Logan	Y	Y	N
A396810	Dog	Raven	Y	Y	N
A397882	Dog	Charlie	Y	Y	N
A399421	Dog	Lucy	Y	Y	N
A399552	Dog	Jack	Y	Y	N
A400498	Dog	Reggie	Y	Y	N
A400680	Dog	Lucy	Y	Y	N
A403564	Dog	Anna	Y	Y	N
A405494	Dog	Kaila	Y	Y	N
A406756	Dog	Sabrina	Y	Y	N
A407156	Dog	Jake	Y	Y	N
A408035	Dog	Lizzie	Y	Y	N
A409637	Dog	Stanley	Y	Y	N
A410330	Dog	Chewy	N	Y	Y
A410668	Cat	Raven	Y	Y	N
A410684	Cat	Mitty	Y	Y	N
A412173	Dog	Jimminee	Y	Y	N
A412626	Dog	*Sam	Y	Y	N

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME,

CASE WHEN INS.SEX_UPON_INTAKE LIKE '%Intact%' THEN 'N'
ELSE 'Y'
END AS "이전 중성화 여부",

CASE WHEN OUTS.SEX_UPON_OUTCOME LIKE '%Neutered%' OR
OUTS.SEX_UPON_OUTCOME LIKE '%Spayed%' THEN 'Y'
ELSE 'N'
END AS "이후 중성화 여부",

CASE WHEN (OUTS.SEX_UPON_OUTCOME LIKE '%Neutered%' OR
OUTS.SEX_UPON_OUTCOME LIKE '%Spayed%' )AND 
INS.SEX_UPON_INTAKE LIKE '%Intact%'
THEN 'Y'
ELSE 'N'
END AS "나중에 중성화 여부"

FROM ANIMAL_OUTS OUTS, ANIMAL_INS INS
WHERE OUTS.ANIMAL_ID = INS.ANIMAL_ID
ORDER BY OUTS.ANIMAL_ID;

# 개와 고양이 별로, 보호시 상태와 보호시와 방출시 중성화 여부 각각 합계

# 개와 고양이 별로, 년도별 입양 현황과, 상태, 중성화 여부 합계

1. 년도별 보호 합계와, 방출 합계

SELECT 보호년도, 보호합계, 방출년도, 방출합계
FROM(
(SELECT TO_CHAR(INS.DATETIME, 'YYYY') 보호년도,COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) 보호합계
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
)IN1
LEFT JOIN
(SELECT TO_CHAR(OUTS.DATETIME, 'YYYY') 방출년도, COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) 방출합계
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY'))OUT1
ON IN1.보호년도 = OUT1.방출년도
)
ORDER BY 보호년도,방출년도;

2. 동물별 보호합계와 방출합계 추가

SELECT dog보호년도, dog보호합계,dog방출년도,dog방출합계,cat보호년도, cat보호합계, cat방출년도,cat방출합계,보호년도, 보호합계, 방출년도, 방출합계
FROM(    
(SELECT TO_CHAR(INS.DATETIME, 'YYYY') 보호년도,COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) 보호합계
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
)IN1
LEFT JOIN
(SELECT TO_CHAR(OUTS.DATETIME, 'YYYY') 방출년도, COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) 방출합계
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY'))OUT1
    
ON IN1.보호년도 = OUT1.방출년도
    LEFT JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') dog보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) dog보호합계,
     TO_CHAR(OUTS.DATETIME, 'YYYY') dog방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) dog방출합계
    FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
    WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID AND INS.ANIMAL_TYPE='Dog'
    GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY'),TO_CHAR(INS.DATETIME, 'YYYY')
    ) DOG
    
     ON DOG.dog보호년도=IN1.보호년도 AND DOG.dog방출년도=OUT1.방출년도
    LEFT JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') cat보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) cat보호합계,
     TO_CHAR(OUTS.DATETIME, 'YYYY') cat방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) cat방출합계
    FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
    WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID AND INS.ANIMAL_TYPE='Cat'
    GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY'),TO_CHAR(INS.DATETIME, 'YYYY')
    ) CAT
    ON CAT.cat보호년도=IN1.보호년도 AND CAT.cat방출년도=OUT1.방출년도
)
ORDER BY 보호년도,방출년도;

문제

-> 고양이와 개의 합이 총합과 안맞는다. -> 오류 : 각각의 보호년도와 방출년도의 합계가 같다.

-> 값이 없을 경우 0으로 표시하고 싶다.

(1) 보호년도와 방출년도 각각 조인

SELECT dog보호년도, dog보호합계,dog방출년도,dog방출합계,cat보호년도, cat보호합계, cat방출년도,cat방출합계,보호년도, 보호합계, 방출년도, 방출합계
FROM(    
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') 보호년도,COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) 보호합계
    FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
    WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    )IN1
    LEFT JOIN
    (SELECT TO_CHAR(OUTS.DATETIME, 'YYYY') 방출년도, COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) 방출합계
    FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
    WHERE INS.ANIMAL_ID= OUTS.ANIMAL_ID
    GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )OUT1
    ON IN1.보호년도 = OUT1.방출년도
    LEFT JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') dog보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) dog보호합계
    FROM ANIMAL_INS INS
    WHERE INS.ANIMAL_TYPE='Dog'
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    ) DOGIN
     ON DOGIN.dog보호년도=IN1.보호년도
    LEFT JOIN
    (SELECT 
     TO_CHAR(OUTS.DATETIME, 'YYYY') dog방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) dog방출합계
     FROM ANIMAL_OUTS OUTS
     WHERE OUTS.ANIMAL_TYPE='Dog'
     GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )DOGOUT
    ON  DOGOUT.dog방출년도=OUT1.방출년도
    LEFT JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') cat보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) cat보호합계
    FROM ANIMAL_INS INS
    WHERE INS.ANIMAL_TYPE='Cat'
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    ) CATIN
    ON CATIN.cat보호년도=IN1.보호년도
    LEFT JOIN
    (SELECT
     TO_CHAR(OUTS.DATETIME, 'YYYY') cat방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) cat방출합계
     FROM ANIMAL_OUTS OUTS
     WHERE OUTS.ANIMAL_TYPE='Cat'
     GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )CATOUT
    ON CATOUT.cat방출년도=OUT1.방출년도
    )
ORDER BY 보호년도,방출년도;

-> 그래도 개수가 안맞는다.

-> 방출합계오류 -> 방출과 보호테이블에서 쓸데없는 이너조인으로 값 누락

SELECT dog보호년도, dog보호합계,dog방출년도,dog방출합계,cat보호년도, cat보호합계, cat방출년도,cat방출합계,보호년도, 보호합계, 방출년도, 방출합계
FROM(    
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') 보호년도,COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) 보호합계
    FROM ANIMAL_INS INS
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    )IN1
    FULL JOIN
    (SELECT TO_CHAR(OUTS.DATETIME, 'YYYY') 방출년도, COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) 방출합계
    FROM ANIMAL_OUTS OUTS
    GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )OUT1
    ON IN1.보호년도 = OUT1.방출년도
    FULL JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') dog보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) dog보호합계
    FROM ANIMAL_INS INS
    WHERE INS.ANIMAL_TYPE='Dog'
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    ) DOGIN
     ON DOGIN.dog보호년도=IN1.보호년도
    FULL JOIN
    (SELECT 
     TO_CHAR(OUTS.DATETIME, 'YYYY') dog방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) dog방출합계
     FROM ANIMAL_OUTS OUTS
     WHERE OUTS.ANIMAL_TYPE='Dog'
     GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )DOGOUT
    ON  DOGOUT.dog방출년도=OUT1.방출년도
    FULL JOIN
    (SELECT TO_CHAR(INS.DATETIME, 'YYYY') cat보호년도,
     COUNT(TO_CHAR(INS.DATETIME, 'YYYY')) cat보호합계
    FROM ANIMAL_INS INS
    WHERE INS.ANIMAL_TYPE='Cat'
    GROUP BY TO_CHAR(INS.DATETIME, 'YYYY')
    ) CATIN
    ON CATIN.cat보호년도=IN1.보호년도
    FULL JOIN
    (SELECT
     TO_CHAR(OUTS.DATETIME, 'YYYY') cat방출년도,
     COUNT(TO_CHAR(OUTS.DATETIME, 'YYYY')) cat방출합계
     FROM ANIMAL_OUTS OUTS
     WHERE OUTS.ANIMAL_TYPE='Cat'
     GROUP BY TO_CHAR(OUTS.DATETIME, 'YYYY')
    )CATOUT
    ON CATOUT.cat방출년도=OUT1.방출년도
    )
ORDER BY 보호년도,방출년도;

-> null 값이 없으므로, LEFT, RIGHT, FULL 외부조인 모두 같은결과

저작자표시 비영리

'Data Science > SQLP' 카테고리의 다른 글

[ORACLE] sqlplus 접속 문제 해결 (0)	2022.04.12
[프로그래머스] 6.String, Date (0)	2022.03.07
[프로그래머스] 4. IS NULL (0)	2022.03.07
[프로그래머스] 3. GROUP BY (0)	2022.03.07
[프로그래머스] 2. SUM, MAX, MIN (+ 누적합계) (0)	2022.03.07

Just do it, and then some

[프로그래머스] 5. JOIN

'Data Science > SQLP' 카테고리의 다른 글

티스토리툴바

[프로그래머스] 5. JOIN

'Data Science > SQLP' 카테고리의 다른 글

'Data Science/SQLP' Related Articles

티스토리툴바