2023/09/08👀서브쿼리와 단일/다중행/WHIT구문(서브쿼리 팩토링)

 

신생아 개버린 이예요 , , 개버린 , ,

 
👇 정리한 SQL파일 및 예제/과제 데이터들은 하단 git 링크에 오시면 있어용 👇

GitHub - gayulz/StudyHistory: 나의 공부 기록 commit

 

 
 
 
즐거운 금요일입니다 ( 끝나고 푹 셔야지 했지만 그날도 공부함 ) 
포스팅 가보자! 이럇!
 
 

중요한 쿼리문의 실행 순서 및 정의

# 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요 매우중요


SELECT 절 	
	SELECT 절은 DML 중 튜플을 검색하는 절 입니다, 튜플 컬럼을 선정함
    
FROM 절	
 	대상 테이블
    
WHERE 절	
	튜플을 산정하기 위한 조건
    
GROUP BY 절	
	그룹화 하기 위한 기준
    
ORDER BY 절	
 	출력시 정렬 기준을 정의한다
    
HAVING 절	
	그룹화하는 GROUP BY 절 뒤에는 그룹을 만들기 위한 조건인 HAVING 절이 있다
	
쿼리의 순서	
	FROM > WHERE > GROUP BY > SELECT > ORDER BY 순서입니다

 
 
 
 
 
 
 
 
 
 

단일 행, 다중 행

단일 행 : SELECT 결과의 행이 1개, 단일행 비교연산자와 사용  ex) =, <, <=, >=, >, <>
다중 행 : SELECT 결과의 행이 2개 이상, 다중행 비교연산자와 사용   ex) IN, ALL, ANY, SOME, EXISTS 
단일 행과 다중 행은 서브 쿼리와 연관이 깊다.
서브 쿼리 결과 값이 단일 행 인지 다중 행 인지에 따라 연산자가 달라지기 때문에 
서브 쿼리를 쓸 때 결괏값이 여러 개 인지 단일 인지 꼭 생각하고 쿼리를 짜야한다
 
자주 쓰는 다중 행 연산자
 
IN
메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 하나라도 일치하면 참
 
ALL
메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 모든 값이 일치하면 참.
메인쿼리 < ALL (서브쿼리) :  서브쿼리 결과와 비교하여 최솟값 반환 
메인쿼리 > ALL (서브쿼리) : 서브쿼리 결과와 비교하여 최댓값 반환
 
ANY
메인 쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상이 일치하면 참
메인쿼리 < ANY(서브쿼리) : 서브쿼리의 결과와 비교해 메인쿼리의 데이터중 한 개라도 서브쿼리 결과보다 작다면 최솟값 반환
메인쿼리 > ANY(서브쿼리) : 서브쿼리의 결과와 비교해 메인쿼리의 데이터중 한 개라도 서브쿼리 결과보다 크다면 최댓값 반환
 
EXISTS
메인쿼리의 비교조건이 서브쿼리의 검색결과 중에 하나라도 만족하는 값이 존재하면 참
해당 로우가 존재하는지의 여부만 확인한다 ( = IN은 실제 존제하는 데이터들의 모든 값까지 확인함 ) 
NOT EXISTS는 메인쿼리의 칼럼명과 서브쿼리의 칼럼명을 비교하여 일치하지 않으면 메인쿼리 테이블의 모든 ROW를 반환한다.
 
 
 
 
 

서브쿼리 Subqueries 

하나의 쿼리 문장 내에 포함된 또 하나의 쿼리 문장이다.
비교연산자의 오른쪽에 기술해야 하며 반드시 괄호 안에 넣어야 한다.
메인 쿼리가 실행 전에 한 번만 실행된다는 특징이 있다.
서브 쿼리는 어느 위치에서 사용하냐에 따라 명칭이 달라진다.
서브쿼리 결과를 해당 절의 메인 쿼리에 적용하는 특징이 있다.
 
SELECT 절 서브쿼리 : 스칼라 서브쿼리
FROM 절 서브쿼리 : 인라인 뷰 서브쿼리 
WHERE 절 서브쿼리 : 중첩 서브쿼리 /
 
 
 
 
 
 
 

스칼라 서브쿼리 Scalar Subqueries 

SELECT 절에서 쓰는 서브쿼리다.
서브 쿼리 결과는 반드시 단일 행이나 SUM, COUNT 등의 집계 함수를 거친 단일 값으로 리턴해야 한다.
이유는 서브쿼리를 끝마친 값 하나를 메인 쿼리에서 SELECT 해야 하기 때문이다.

 
 
 
 

인라인 뷰 Inline Views

FROM 절에서 사용하는 서브 쿼리다.
이때 서브쿼리의 결과는 반드시 하나의 테이블로 리턴돼야 한다.
서브쿼리를 끝마친 테이블 하나를 메인 쿼리의 FROM 절에서 테이블로 잡기 때문임
 

 
 
 
 
 

중첩 서브쿼리 Nested Subqueries

WHERE 절에서 사용하는 서브 쿼리다.
가장 자주 쓰이는 대중적이며 단일행과 다중 행 모두 리턴이 가능하다
서브 쿼리를 끝 마친 값들을 메인 쿼리의 조건절을 통해 비교 등 하기 때문이다.

 
 
 
 
 

서브쿼리 팩터링 WITH 구문

SQL문을 단순화시키고 반복을 줄이기 위해 사용하는 기술이다.
해당 구문을 잘 이용한다면 쿼리문이 매우 단순해지고 반복도 줄며 성능도 좋아진다.
자주 사용하는 쿼리문이라면 한 번만 파싱(Parsing)되어 처리되므로 수행 성능상 장점이 매우 높다.
WITH구문으로 정의한 임시 테이블에 쉽게 검색되는 구조라 매우 효율적이다
보통 서브쿼리 분해절(sub query factoring clause)라고 한다
인라인뷰 서브쿼리 할 때 쿼리가 많이 지저분 해지는데 WITH 구문을 사용하면 뭔가 단락이 지어져서 가독성이 좋아 보이긴 하다
예제가 생각나는 게 별로 없어서 대충 비교해 보고자 올려본다
 

 
 
인라인뷰 FULL JOIN 사례

 
 
 

WITH 구문 FULL JOIN 사례

 
 
원래 뭔가 덕지덕지인 쿼리라서... 별거 아닌데도 더러워 보였는데 
그나마 WITH 연산ㄷ거에 조금은 가독성이 있어 보이긴 하다..(나만 그런가) 흠
 
 
 

오늘의 과제

# 제 이전 포스팅을 보시면 Mymember 테이블의 구조와 insert 구문이 있으니 참고해주세요




9월 8일 실습

SELECT * FROM mymember;

-- 1번문제, 포인트가 200인 이상인 사람들 그룹별로 몇인지 출력하시오
SELECT grade , count(*) 
FROM mymember
WHERE point >= 200
GROUP BY grade 
ORDER BY grade;



-- 2번문제, 그룹별 몇명인지 출력하시오, 단 옆 컬럼에는 전체 인원수를 출력한다
SELECT count(*) 그룹별인원 
	, (SELECT count(*) FROM mymember) 전체인원수
FROM mymember 
GROUP BY grade; 


-- 3번문제, 그룹몇 몇명인지 출력하시오, 단 옆 컬럼에는 grade=1 이면 1학년 이렇게 학년을 뒤에 붙혀서 출력하시오
SELECT count(*) 
FROM mymember 
GROUP BY grade; 



-- 4번문제, 그룹별 몇명인지 출력하시오, 단 그 옆 컬럼에는 m이면 남자, w이면 여자로 출력하시오
SELECT count(*) 
FROM mymember 
GROUP BY grade; 




-- 그룹별 몇명인지 출력하시오 단 그룹의 평균이 전체 평균보다 높은 그룹만 출력(평균은 포인트)
SELECT count(*), grade
FROM mymember
GROUP BY grade HAVING avg(point) > (SELECT avg(point) FROM mymember);




-- 전체 포인트 평균보다 높은 평균의 포인틀을 가진 사람들을 모두 출력하시오 
SELECT *
FROM mymember
WHERE name = ( SELECT name FROM MYMEMBER member);



-- 전체에서 최고점을 가진 사람의 이름과 성별과 점수를 출력하시오
SELECT name, gender, point
FROM mymember
WHERE point IN (SELECT max(point) FROM MYMEMBER);



-- 포인트가 300인 사람과 같은 도시에 살고 있는 사람의 이름을 출력하시오.
SELECT name
FROM mymember 
WHERE addr IN (SELECT addr FROM mymember WHERE point =300 );




-- 포인트가 300인 사람과 같은 그룹이면서 같은 도시에 살고 있는 사람의 이름을 출력하시오 
SELECT name 
FROM mymember
WHERE (grade.addr) IN (SELECT grade, addr, FROM mymember WHERE point=300);





-- m 이라고 하는건 테이블에 별칭을 지어주는 것, 약어
SELECT rownum, m.*
FROM mymember m;



-- rownum 사용하여 순서를 매긴 뒤 포인트가 가장 높은 사람 5명 출력
SELECT rownum , m.* 
FROM ( SELECT point FROM mymember ORDER BY point DESC ) m
WHERE rownum <= 5;


-- rownum 은 select 절에서 순서를 매기는데 다음에 order by 가 실행되면 순서가 바뀐다.
-- mymember 테이블 자리에 정렬된 테이블로 바꾸면 된다 > 서브쿼리중 인라인 뷰 로 해당됨
-- 포인트가 높은 순서대로 rownum 을 사용하여 번호를 부여하고 5번째~10번째 사람만 출력

SELECT b.* 
FROM ( SELECT rownum INS, a.* 
		FROM ( SELECT *
				FROM mymember 
				ORDER BY point DESC ) a ) b 
WHERE INS BETWEEN 10 AND 20;





/*+ALL_LOWS */