[구디아카데미 후기]DAY 8 셀프조인, 서브쿼리

NA YE SOM·2023년 7월 7일
IT 국비지원구디아카데미 후기민경태 강사님
0
post-thumbnail
post-custom-banner

▶ 한줄평 : 서브쿼리가 어느상황에서 쓰이는지를 알았고 어려운 내용임에도 민경태 강사님께서 차근차근 설명해주셔서 이해가 잘되었다.

<복습>

-> INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN
-> ON 조인조건(1:M 관계 일치하는지?)

  • 조인(JOIN) : PK - FK관계로 있어야 함
  • 크로스 조인 : A 테이블의 첫번째 행 - 다음 테이블 5개 조합(테스트하는 용도의 데이터)
    실무) 조인의 결과가 어떤 데이터를 가지고 있지 않음
    -> 조인 결과 의미 없음
    -> 조인 조건을 적지 않으면) 쓰레기 결과가 나옴 (크로스 조인)
    (EX) 부서 정보 1개 - 사원정보 107개 붙임(하나의 부서에 107명이 근무하는 것 같지만 나머지 106개는 틀린 데이터)
    2번째 부서 정보 - 사원정보 107개
    ... -> 결과 엄청 나게 나옴

(EX) 부서 2개, 사원 4명 -> 8개 결과 (데이터가 많아짐) - 입출력 테스트 용도로 쓰임

-> 많이 쓰이지 않음

내부조인 : 두 칼럼에 공통적으로 존재하는 값만 결함됨


-> 3,4번 조회대상이 아님

★외부조인 : 일치 여부와 상관없이 한 테이블의 내용은 모두 포함됨(꼭 기억!)

//

ANSI 왼쪽의 값이(3,4) (EMPLOYEE에는 없지만) 도 조회대상에 시키자
(왼쪽의 값은 항상 포함을 시켜라)

  • NULL값(일치하는 정보가 없어서) (3,4번 부서에 근무하는 사원은 없어서)

RIGHT OUTER JOIN : 오른쪽 테이블을 모두 포함

  • 사실은 5번은 존재할 수 없는 데이터 (FK는 PK가 가진 값만 가질 수 있음 : 참조 무결성 위배라서)
  • DISABLE 외래키 중지시킨다음에 테스트 가능
  • DEPARTMENT 1,2,5 모두 조회 -> 5번은 부서가 없어서 NULL값으로 나옴

셀프조인 : 하나의 테이블에 하는 조인방식, 각각 다른 별명
(주인공이 누구인지 생각해서 join방향 설정)

EMP 사원 번호
MGR 상사 번호

(EX) 이은영 - 상사
상사번호도 사원번호 안에서 데이터 가져올 수 있다

1:M(일:다) 관계
EMPLOYEE_TBL A(좌측 테이블) : 사원정보 (EMPLOYEE_NO사용하는 사원 TABLE)
EMPLOYEE_TBL B(우측 테이블) : 상사정보

연습문제 풀이 1~7 : 조인, 8~10 : 셀프 조인

  1. 모든 사원들의 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER의 FIRST_NAME을 조회하시오. (SELF JOIN)


-> MANAGER_ID 조회가능(NAME아니라 ID는 쉽게 해결가능)

-> 사원테이블의 매니저번호 = 상사테이블의 사원번호 일치하는 정보를 조인하는 것

inner join 사용시)
kim 의 사원 1 없음
lee ,park 일치하는 정보 있으니까 조회 됨
-> inner join시) 사원 2명 조인됨 (kim은 빠짐)
-> 값이 일치 하지 않으니까
(kim 상사 없으면 null이라고 표시해서 상사가 없구나 표시하기 위해서)

--> 결론, 외부조인함

1 : DRIVE 쓰면 좋음(상사테이블 = DRIVE TABLE)


포함시키고 싶은건
(원래는 누락되는데 -> 사원테이블 E 에있음 -> 오른쪽)



-> 외부조인 (STEVEN KING이 포함됨, 내부조인 : 빠짐)

오라클문법

서브쿼리

  • 쿼리문에 포함된 쿼리문
  • SELECT 안에 SELECT
  • WHERE 절 (일반 서브쿼리), SELECT(스칼라 서브쿼리), FROM(인라인 뷰: FROM절에 나오는 SELECT) : 어디에 나오는 SELECT인가?

-제품번호 '1001'인 제품이 동일한 공장 : 정확히 어느 공장인지


-빨간색 : 서브쿼리
-파란색 : 메인쿼리

-해석순서: 파란색(서브쿼리 먼저) : 공장이름 나옴 -> 빨간색


-> 서브쿼리 먼저 처리


-결과가 N개 : 없을수도 있다 .

서브쿼리




-서브쿼리 들어갈 자리(파란색) (WHERE절 자리)

단일행 서브쿼리 결과 1개 -> =(등호) (메인쿼리와 )
다중행 서브쿼리 결과 2개 -> IN(메인쿼리와)

*ANY , OR 잘 안씀 - 대체품이 있어서


-> () 넣지 않아도 서브쿼리 먼저 함 (실행순서는 같음)

-> SELECT JOB_ID 답으로 내주니 ) WHERE JOB)ID로 내놔야 함

  • 단일행 서브쿼리라고 알 수 있는것? 결과는 하나이다
    (1) EMPLOYEE_ID : PK (중복 X)
    (2) = 등호


-> 서브쿼리 결과 NULL

PK라서 중복 X -> 단일행

부서테이블과 사원테이블이 서브쿼리의 형태로 ~


-> PK아니고 UNIQUE칼럼도 아님
-> 운좋게 중복이 없을 뿐이다


-> PK도 아니라서 중복을 가질 수 있는 아이
IT라는 가질 수 있는게 2개 이상있을 수 있다
IT 만족하는 데이터가 여러개 있을 수 있다

  1. 'Seattle'에서 근무하는 사원 조회하기

1 . 부서번호를 찾기

근무지에 따라서 부서번호 다름

  1. 부서 테이블 - LOCATION_IT 정보 있음



-> CITY UNIQUE 가 아니라서 2개 나올 수 있음
(EX) 광주 - 서울, 경기도 -2개
-> 결과가 두개 이상 나올 수 있음 : IN


-> 2번째 서브쿼리의 결과가 여러개 나올 수 있으니까
-> IN으로 바꾸기



-> SEATTLE 18명

-> 어떤 거든 상관없이 먼저 떠오르는 걸로 풀기!

  1. 연봉 가장 높은 사원 조회하기
    하나의 결과를 가지고 서브쿼리를 매기는 것


-> 안됨

  1. 가장 먼저 입사 -> '작은값'(옛날값)

  2. 평균 연봉 이상을 받는 사원 조회하기

FROM 절의 서브쿼리(인라인 뷰)


뷰 : 쿼리문을 저장하고 있는것, 실행하면) 테이블이 나옴

-진짜 테이블 - 하드디스크에 데이터가 저장되어 있음
-가짜 테이블 - 쿼리문만 있음(SELECT만 있음 - SELECT실행시 테이블이 답으로 나옴)
(실행하면 테이블이라 테이블이라고 부르긴함)

FROM TABLE인데
TABLE 대신 VIEW

= FROM VIEW
= FROM SELECT
-> FROM 절에 포함된 뷰(인라인 뷰)

  • 존재 이유 : 정렬먼저 하고 싶을때, 인라인 뷰 씀, 실행순서 바꾸고자 할때
    (첫번째) FROM 먼저 -> (가장 마지막) ORDER BY
    -> '정렬'을 '인라인뷰'에 넣어두면 '정렬을 먼저 함'

(EX) 웹툰 - 1PAGE

-> 작성일자 : '내림차순'으로 함
-> 목록 처리시) 가장 먼저 해야할 것 (날짜순으로)

  • 451 ~ 432 20개만 커트해서 가지고 옴 -> 먼저 '정렬'하고 -> '20개' 잘라서 옴

*CRUD (게시판)(Create=Insert, Read(목록,상세페이지), Update, Delet) : (구현해야 하는 5가지 핵심 기술)
★ 목록 - 필수 기술 - SELECT로 만듦
★ 상세페이지 -SELECT로 만듦
삽입, 수정, 삭제
☆ 면접 : 게시판 CRUD는 할 줄 아는가?

-> 기타함수(행번호 붙이기 함수 : 정렬을 시키는 쿼리문법이 함께 사용됨)
1) 2) 가 서브 쿼리가 됨



2,3번 연봉이 같음


-> RANK로 바꾸기


-> 실행순서 안맞음
-> 서브쿼리로 조정해야함

-> 행번호 인식 시키기 위해서 FROM절로 뺌 (순서만 조정하면 됨)
(FROM절에서 먼저 인식하고 WHERE절에서 인식함)

  1. 목록 가져오는 예시 ★ 페이지 처리 필요 쿼리 !

    ROWNUM : 행번호 가져오는 키워드로 쓸 수 없음 -> RN으로 !

(1) 정렬 시킨대로 번호

(2) 구간에 따라서 값 가지고 오기

  • 실제 조회시 행번호 필요없음 : RN 뺌
  • ASC 생략 가능하시만 명시함

SELECT 절의 서브쿼리



-> 조인 , 서브쿼리 가능
-> 부서명 :부서테이블에서 가지고 오는 SELECT를 넣음


-> MAIN 쿼리에도 DEPARTMENT_ID 있음

-> 비상관쿼리 : 메인쿼리, 서브쿼리하고 상관이 없음

-> 상관쿼리 : 메인 쿼리, 서브쿼리 관계 있음

-기술 , 유지 보수 관점에서도 위에 방법이 나음
-> 50번 번호 한번만 들어가서(위에는 두번 들어감)

-> 스칼라 서브 쿼리를 의도적으로 많이 쓸 필요는 없음(서브쿼리 중 성능떨어짐)
-> 조인으로 풀면 더 간단하게 풀 수 있음

-> FROM 절의 서브쿼리 제외하고 나머지는 원하면) 조인으로 바꿀 수 있음

★★FROM 절의 서브쿼리는 꼭 기억!

-공공기관 서비스 : 자바 많이 씀
-전자정부 프레임 워크 : 자바로 되어있음
-스타트업 : NODE.JS 등 씀
-확실하게 알때까지 한가지 기술만 공부하기!



WITH
1. 자주 사용하거나 복잡한 쿼리문을 WITH절의 코드 블록으로 등록시켜 놓을 수 있다.
2. WITH 절의 코드 블록은 임시로 저장되기 때문에 곧바로 사용해야한다.(성능의 향상 기대할 수 없음)
3. 쿼리문의 가독성이 좋아진다.(쿼리문 읽기가 편해진다)

1.1~10번째로 고용된 사원 조회하기

1) 서브쿼리

SELECT EMPLOYEE_ID, HIRE_DATE
FROM (SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE FROM EMPLOYEES)
WHERE RN BETWEEN 1 AND 10;

2) WITH


-> MY_SUBQUERY 이름으로 블록을
-> WITH로 저장시키고

WITH
MY_SUBQUERY AS(
SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE
FROM EMPLOYEES
)

2. 부서별 부서번호, 부서명, 연봉총액을 조회하기

1) 조인

1) 먼저 부서별 연봉총액 구하기(연봉총액이 그룹의 목적)

SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID

SELECT MY.DEPARTMENT_ID
, MY.TOTAL_SALARY
, D.DEPARTMENT_NAME
FROM DEPARTMENTS D INNER JOIN(SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) MY
ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;

-> EMPLOYEE TABLE만 있으면 ) 부서 이름은 안나오지만
부서별 연봉총액 구할 수 있음


-> DISTNICT 랑 같음

SELECT (하나의 테이블)
JOIN
D_ID
-> D_ID 이용한 JOIN

2) WITH

WITH MY_SUBQUERY AS(
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
)

SELECT MY.DEPARTMENT_ID
, D.DEPARTMENT_NAME
, MY.TOTAL_SALARY
FROM DEPARTMENTS D INNER JOIN MY_SUBQUERY MY
ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;


-> 덩어리로 봐서 가독성이 편해지는 경우 있음

-> 따로따로 보고 합치기

profile
NA YE SOM
개발자 velog
이전 포스트

[구디아카데미 후기]DAY 7 WEHRE절, HAVING절, INNER JOIN(내부조인), OUTER JOIN(외부조인)

다음 포스트

서브쿼리 연습문제

post-custom-banner

0개의 댓글