0504 | ORACLE (6) // SELECT구절 - ORDER BY, GROUP BY, HAVING, 순위함수, 서브쿼리, INNER JOIN, OUTERJOIN, SELFJOIN, UNION
[1] SELECT 구절과 정렬
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY // 반드시 암기해야함. 순서도 바뀌면 안됨!
1. FROM - 격자형 데이터 마련
2. WHERE - 조건에 맞는 필터링
3. 집계함수 - 조건에 맞는 데이터 집계 (COUNT, AVG) > GROUP BY절을 통해서 집계함수 사용 가능
* 집계함수는 WHERE절에 쓰지 못한다
4. 집계된 결과를 재필터링 > HAVING
5. 정렬 : ORDER BY
[2] 정렬하기 (ORDER BY)
ASC: 오름차순 // 설정하지 않으면 기본적으로는 오름차순
DESC: 내림차순
Q. 이름을 기준으로 내림차순
SELECT * FROM MEMBER ORDER BY NAME DESC;
Q. 오씨 성을 가진 회원을 조회 (단, 나이를 오름차순)
👉 SELECT * FROM MEMBER WHERE NAME LIKE '오%' ORDER BY AGE ASC; // 순서 바뀌지 않도록 주의
Q. 조회수 내림차수 기준으로 정렬. 단 조회수가 동일할때 등록일을 내림차순으로 정렬
👉 SELECT * FROM NOTICE ORDER BY HIT DESC, REGDATE DESC; // 이차 정렬 설정 가능
[2] 집계함수와 GROUP BY
2-1) 집계함수
SUM, MIN, MAX, COUNT(NULL값은 세지 않는다), AVG
2-2) 묶어서 산출하고자 하는 명령어 GROUP BY
SELECT WRITER_ID,COUNT(HIT) FROM NOTICE GROUP BY WRITER_ID ORDER BY COUNT(HIT) ASC;
// 그룹으로 묶인 항목 외 다른 항목은 함께 출력될 수 없다. 또한 산출된 결과를 ORDER BY를 통해 정렬할 수 있다
✔ 실제 실행 순서
FROM ▶ CONNECT BY ▶ WHERE ▶ GROUP BY ▶ HAVING ▶ ORDER BY ▶ SELECT
SELECT에 있는 별칭은 HAVING, GROUP BY절에서 쓸 수 없다. 더 나중에 작동되기 때문
[3] HAVING절
WHERE절에서는 그룹 함수(집계함수) 를 쓸 수 없다. 따라서 WHERE COUNT(ID)<2 는 불가
FROM ▶ CONNECT BY ▶ WHERE (ROW_NUMBER) ▶ GROUP BY (집계함수) ▶ HAVING ▶ SELECT ▶ ORDER BY
👉 따라서 WHERE을 대신하여 필터링을 위한 구절인 HAVING절 제공
Q. 회원별 게시글 수를 조회하되, 게시글이 2 이하인 레코드만 출력
👉 GROUP BY WRITER_ID HAVING COUNT(HIT)<=2 ORDER BY COUNT(HIT) ASC;
[4] 순위함수 ROW_NUMBER(), RANK()
: ROWNUM이 ORDER BY때문에 섞여서 출력됨
: 정렬된 후에 넘버링을 하고 싶을때 쓰는게 ROW_NUMBER() 함수.
SELECT ROWNUM, ROW_NUMBER() OVER (ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;
-- ORDER BY HIT을 먼저 수행후 ROW_NUMBER() 함수 실행
--등수를 매기는 함수 RANK()
SELECT ROWNUM,RANK() OVER (ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;
-- 공동 4등 다음에 5등이 나올 수 있도록 수가 이어지게 하는 DENSE_RANK()
SELECT ROWNUM, DENSE_RANK() OVER (ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;
-- PARTITION 을 사용하면 WRITER_ID로 그룹이 묶인 다음에 그 안에서 HIT로 정렬을 한다
SELECT ROWNUM, DENSE_RANK() OVER (PARTITION BY WRITER_ID ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;
[5] 부조회 (서브쿼리)
서브쿼리를 사용하는경우? ▶구절의 순서를 바꿔야 하는 경우
-- 최신 등록 순으로 정렬한 결과에서 상위 열명을 원할때
SELECT * FROM MEMBER ORDER BY REGDATE DESC WHERE ROWNUM BETWEEN 1 AND 5;
-- 이 경우, 순서때문에 오류가 발생하므로 (조건인 WHERE절보다 ORDER가 먼저올 수 없다) 서브쿼리로 정렬된 결과물을 가지고 데이터를 조회함
SELECT * FROM (SELECT * FROM MEMBER ORDER BY REGDATE DESC)
WHERE ROWNUM BETWEEN 1 AND 5;
Q. 평균 나이 이상인 회원 목록을 조회 - 평균나이를 먼저 구한 후 회원목록을 조회해야 한다
👉 SELECT * FROM MEMBER WHERE AGE >= (SELECT AVG(AGE) FROM MEMBER);
[6] 조인 JOIN
: 데이터의 무결성을 위해 데이터가 서로를 참조하고 있음
: 참조중인 테이블을 원래 모양대로 합치는 것 = JOIN
INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN
* 서로 관계가 있는 레코드는 INNER, 관계가 없는 레코드는 OUTER . 관계가 있는 레코드들만 합치는 조인이 INNER JOIN
6-1) INNER JOIN
: 참조키를 기준으로 일치하는 행만 조인
SELECT * FROM MEMBER INNER JOIN NOTICE ON MEMBER.ID=NOTICE.WRITER_ID;
// 멤버의 아이디=노티스의 WRITER ID가 동일하다고 제시해줌. 해당 아이디가 동일해야만 데이터가 연결된다.
6-2) OUTER JOIN
: 참조키를 기준으로 일치하지 않는 행도 포함시키는 조인
MEMBER LEFT / RIGHT / FULL OUTER JOIN NOTICE ON MEMBER.ID=NOTICE.WRITER_ID;
명령문을 기준으로 LEFT, RIGHT가 해당된다
SELECT * FROM NOTICE N LEFT OUTER JOIN MEMBER M ON N.WRITER_ID=M.ID; // 없는 레코드는 자동으로 NULL값 처리됨
* 테이블명도 별칭처리하여 명령문을 짧게 처리 가능
6-3) SELF JOIN
: 데이터가 서로 포함 관계를 가지는 경우 (테이블 내에서 연결된 자신의 값을 반환)
: INNER, OUTER조인 두개만 있는게 맞지만, 개념적으로 셀프조인이 존재함
SELECT M.*,B.NAME BOSS_NAME -- 멤버 전체값에 보스 네임만 추가로 반환
FROM MEMBER M
LEFT OUTER JOIN MEMBER B
ON M.BOSS_ID=B.ID; -- 여기서 ID가 참조키
[7] UNION
: 조인처럼 합치는 방법. 단 조인은 컬럼을 기준으로 합쳐지지만 유니온은 컬럼이 늘어나지 않고 레코드가 합쳐진다
: 합칠때 컬럼의 개수, 자료형만 맞춰주면 합칠 수 있다
: 하나의 테이블처럼 통합검색이 가능해짐
: 만약 레코드가 중복될 경우는 중복값이 사라진다.
SELECT ID,NAME FROM MEMBER UNION SELECT WRITER_ID, TITLE FROM NOTICE;
* MINUS는 중복값이 빠지고 중복되지 않는것만 남겨진다
** INTERSECT는 중복값만 남는다
*** UNION ALL 중복값도 상관없이 다 나오게 한다
SELECT ID, NAME FROM MEMBER WHERE ID LIKE '%n%'
MINUS
SELECT ID, NAME FROM MEMBER WHERE ID LIKE '%e%'; -- 하나의 테이블로도 조회가능