오라클 Oracle 조인 Join 과 서브 쿼리 SubQuery 이해

오라클 Oracle 서브 쿼리 SubQuery

서브 쿼리Sub-Query 란 한 SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문을 의미한다.
최종 결과를 출력하는 쿼리를 메인 쿼리라고 한다면,
이를 위한 중간 단계 혹은 보조 역할을 하는 SELECT문을 서브 쿼리라 한다.

조인 절에서 소개했던 SQL문 중 괄호 안에 들어있는 SELECT문이 바로 서브 쿼리에 속한다.

하나의 SQL문을 기준으로 메인 쿼리를 제외한 나머지 모든 SELECT문을 서브 쿼리로 보면 되며,
따라서 서브 쿼리는 여러 개를 사용할 수 있다.

서브 쿼리는 다양한 형태로 사용된다.
즉 SELECT (스칼라), FROM(인라인뷰), WHERE 절 (서브쿼리)모두에서 사용할 수 있을 뿐만 아니라,
INSERT, UPDATE, MERGE, DELETE 문에서도 사용할 수 있다.

메인 쿼리와의 연관성에 따라
연관성 없는(Noncorrelated) 서브 쿼리
연관성 있는 서브 쿼리

형태에 따라
일반 스칼라 쿼리 (SELECT 절)
인라인 뷰(FROM 절)
일반 서브쿼리(WHERE 절)

오라클 Oracle 연관성 없는 서브 쿼리 SubQuery

메인 쿼리와의 연관성이 없는 서브 쿼리를 말한다.
즉 메인 테이블과 조인 조건이 걸리지 않는 서브 쿼리를 가리킨다.

오라클 Oracle 연관성 있는 서브 쿼리 SubQuery

메인 쿼리와의 연관성이 있는 서브 쿼리, 즉 메인 테이블과 조인 조건이 걸린 서브 쿼리를 말한다.

SELECT - 스칼라 서브 쿼리 -> 단일값
From에 의해 생성된 영역에서 한 ROW 추출하고
추출한 row에 값(컬럼)을 뺴서 저장할 수 있는 1 Row 생성한다

FROM 인라인뷰 - 테이블처럼 사용

WHERE 조건 값, 서브 쿼리 -> 단일값 (단일연산자), 다중값 (다중값연산자)

단일값 연산자 -> '=' 비교연산자 컬럼 = 값 <- 서브 쿼리 (단일값)

다중값 연산자 -> IN 컬럼 IN (값1, 값2,... )

오라클 Oracle 인라인뷰 Inline View = 중간단계, 테이블 처럼 사용한다

FROM 절에 사용하는 서브 쿼리를 인라인 뷰 InlineView 라고 한다.
서브 쿼리를 FROM 절에 사용해 하나의 테이블이나 뷰처럼 사용할 수 있다

오라클 Oracle 복잡한 쿼리를 작성해야 할때 방법

테이블 조회되는 결과의 항목을 정의, 확인한다.
->> 문제파악!!
문제를 풀기 위해 필요한 테이블과 컬럼을 파악한다.
다시 작은 단위로 분할해서 쿼리를 작성한다.
->> 중간단계 만들고 합쳐라 divide
분할한 단위의 쿼리를 하나로 합쳐 최종의 결과를 산출한다
결과를 검증한다
->>  갯수 체크!!!

-- 평균 급여보다 많이 받는 사원수 SELECT AVG(a.SALARY) FROM EMPLOYEES a ; SELECT COUNT(*) FROM EMPLOYEES a WHERE a.SALARY >= ( SELECT AVG(b.SALARY) FROM EMPLOYEES b -- 107 row )-- 6461 ; -- 문제 : 부서가 없는 사원 수 조회 SELECT COUNT(*) FROM EMPLOYEES a WHERE a.DEPARTMENT_ID IS NULL -- 사원중에 부서가 없다 ; -- 문제 : 상위 부서가 없는 사원 수 조회 -- 사원수 => employees table -- 상위 부서 => departments table. parent_id 컬럼 is null SELECT b.DEPARTMENT_ID -- 상위 부서가 없는 부서번호 FROM DEPARTMENTS b WHERE b.parent_id IS NULL ; SELECT COUNT(*) FROM EMPLOYEES b WHERE b.DEPARTMENT_ID IN ( --> = OR SELECT b.DEPARTMENT_ID -- 여러개 조회 FROM DEPARTMENTS b WHERE b.parent_id IS NULL ) ; -- SELECT COUNT(*) FROM DEPARTMENTS a WHERE a.DEPARTMENT_ID IS NULL; -- 부서중에 부서번호 없다 -- 문제 : 사원중에 업무 변경 이력이 있는 사원 조회 -- 변경이력 테이블 : job_history table -- 업무 변경 이력이 있는 사원 : job_history 테이블 값이 존재하는 사원 SELECT * FROM job_history j ; -- 변경 이력이 있는 사원 조회 SELECT a.EMPLOYEE_ID, a.EMP_NAME FROM EMPLOYEES a WHERE a.EMPLOYEE_ID IN ( SELECT j.EMPLOYEE_ID FROM job_history j ) ; -- 부서변경 이력이 있고 업무는 변경되지 않은 사원 조회 SELECT a.EMPLOYEE_ID, a.EMP_NAME, a.JOB_ID FROM EMPLOYEES a WHERE (a.EMPLOYEE_ID, a.JOB_ID) IN ( SELECT b.EMPLOYEE_ID, b.JOB_ID FROM JOB_HISTORY b ) ; a.EMPLOYEE_ID IN ( SELECT b.EMPLOYEE_ID FROM JOB_HISTORY b ) AND a.JOB_ID IN ( SELECT b.JOB_ID FROM JOB_HISTORY b ) ;

 -- 문제 : 사원중에 부서 변경이 있는 부서번호, 부서명 -- 부서 변경 이력 조회 SELECT DISTINCT a.DEPARTMENT_ID FROM JOB_HISTORY a ; SELECT a.DEPARTMENT_ID, a.DEPARTMENT_NAME FROM DEPARTMENTS a WHERE a.DEPARTMENT_ID IN ( SELECT b.DEPARTMENT_ID FROM JOB_HISTORY b ) ;

-- 2000년 이탈리아 고객이 구매한 월별 건수 SELECT a.SALES_MONTH, COUNT(*) FROM SALES_60 a , CUSTOMERS b , COUNTRIES c WHERE a.SALES_MONTH BETWEEN '200001' AND '200012' AND c.COUNTRY_NAME = 'Italy' AND c.COUNTRY_ID = b.COUNTRY_ID AND b.CUST_ID = a.CUST_ID GROUP BY a.SALES_MONTH -- 월별 ORDER BY a.SALES_MONTH ASC ;

-- 2000년 이탈리아 고객이 구매한 월별 평균 구매액 SELECT a.SALES_MONTH, ROUND(AVG(a.AMOUNT_SOLD)) AS asold FROM SALES_60 a , CUSTOMERS b , COUNTRIES c WHERE a.SALES_MONTH BETWEEN '200001' AND '200012' AND c.COUNTRY_NAME = 'Italy' AND c.COUNTRY_ID = b.COUNTRY_ID AND b.CUST_ID = a.CUST_ID GROUP BY a.SALES_MONTH -- 월별 ORDER BY a.SALES_MONTH ASC ;

SELECT a.* FROM (SELECT a.SALES_MONTH, ROUND(AVG(a.AMOUNT_SOLD)) AS asold FROM SALES_60 a , CUSTOMERS b , COUNTRIES c WHERE a.SALES_MONTH BETWEEN '200001' AND '200012' AND c.COUNTRY_NAME = 'Italy' AND c.COUNTRY_ID = b.COUNTRY_ID AND b.CUST_ID = a.CUST_ID GROUP BY a.SALES_MONTH -- 월별 ORDER BY a.SALES_MONTH ASC ) a , ( -- 2000년도 이탈리아 고객의 연평균 금액 SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS ysold FROM SALES_60 a , CUSTOMERS b , COUNTRIES c WHERE c.COUNTRY_NAME = 'Italy' AND a.SALES_MONTH BETWEEN '200001' AND '200012' AND c.COUNTRY_ID = b.COUNTRY_ID AND b.CUST_ID = a.CUST_ID ) b WHERE a.asold > b.ysold ;