[SQLD] SQL기본&활용 : 집합연산자와 순수관계연산자, 조인 JOIN, 조인 종류, 계층형 질의, 서브쿼리 SUB QUERY, 뷰 VIEW

https://lotuus.tistory.com/42

[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제

 

[목차]

💚집합연산자와 순수관계연산자

   집합연산자

      차집합 다르게 표현하기

      교집합 다르게 표현하기

   순수관계연산자

💚조인 JOIN

  ⭐⭐조인 작성법

   JOIN과 ALIAS 별명

💚JOIN의 종류

   INNER JOIN과 NATURAL JOIN 예제

   SELF JOIN 셀프조인

💚 계층형 질의 (ORACLE만 지원한다)

   가상칼럼과 함수

   전개방법

💚 SUB QUERY 서브쿼리
   반환값에 따른 분류
   동작방식, 사용위치에 따른 분류
💚 뷰 VIEW : 가상테이블
   뷰의 장점
   뷰 사용방법

---

💚 집합연산자와 순수관계연산자

집합연산자

조인을 사용하지 않고 연관된 데이터를 조회하는 방법이다.

여러 SELECT문 결과를 연결하여 하나로 결합하는 방식을 사용한다.

SELECT문의 칼럼수가 동일하고, 데이터타입이 상호호환되어야 한다.

종류	설명	SQL 기능
UNION	합집합. 두 집합을 모두 표현한다. / 중복제거O / 정렬O	UNION
	합집합. 두 집합을 모두 표현한다. / 중복제거X / 정렬X	UNION ALL
INTERSECTION	교집합. 두 집합의 공통집합을 추출한다. / 중복제거O	INTERSECT
DIFERENCE	차집합. 첫번째 집합에서 두번째 집합과의 공통부분 제외 / 중복제거O	MINUS (MSSQL은 EXCEPT)
PRODUCT	두 집합의 모든 경우의 수를 추출한다. / 중복제거O	CROSS JOIN으로 구현되었다.

SELECT문
집합연산자
SELECT문
ORDER BY ~  -- ORDER BY는 가장 마지막줄에 한번만 기술한다.

차집합 다르게 표현하기

    SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 MINUS SELECT ~ FROM ~ WHERE 칼럼 연산자 조건2
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND 칼럼 <> 조건2
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND NOT EXISTS(SELECT 1 FROM ~ WHERE 칼럼 연산자 조건2)
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND NOT IN(SELECT 칼럼1 FROM ~ WHERE 칼럼 연산자 조건2)

교집합 다르게 표현하기

    SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 INTERSECT SELECT ~ FROM ~ WHERE 칼럼 연산자 조건2
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND 칼럼 연산자 조건2
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND EXISTS(SELECT 1 FROM ~ WHERE 칼럼 연산자 조건2)
 = SELECT ~ FROM ~ WHERE 칼럼 연산자 조건1 AND IN(SELECT 칼럼1 FROM ~ WHERE 칼럼 연산자 조건2)

 

순수관계연산자

종류	설명	SQL 기능
SELECT	여러 행 중 조건에 맞는 수평적 부분집합 추출	WHERE절로 구현되었다.
PROJECT	여러 열 중 조건에 맞는 수직적 부분집합 추출	SELECT절로 구현되었다.
JOIN	다른 테이블과 공통속성을 중심으로 새로운 집합을 만든다.	JOIN절로 구현되었다.
DIVIDE	-	사용되지 않는다.

 

 

💚 조인 JOIN

두개 이상의 테이블과 연결하여 데이터를 출력하기위해 사용한다.

일반적으로 PRIMARY KEY나 FOREIGN KEY로 연결되어 JOIN이 성립한다.

FROM 절에 여러 테이블이 나열되도 2개씩 먼저 JOIN처리된다.

JOIN의 두 테이블 연결 예시

⭐⭐조인 작성법

JOIN과 ALIAS 별명

FROM절에서 테이블에 별명을 붙여 SELECT, WHERE절에서 사용할 수 있다.

단, 별명을 사용할 경우엔 별명만 사용해야한다. (별명과 테이블명 혼합해서 사용 X)

ALIAS 별명 이용하여 JOIN 표현	예제
SELECT A.칼럼명, B.칼럼명 FROM 테이블1 A, 테이블2 B WHERE A.칼럼명 = B.칼럼명;	SELECT PLAYER.NAME, P.NO, T.NAME  FROM PLAYER P, TEAM T WHERE P.NO = T.NO; --오류발생

 

 

💚 JOIN의 종류

 

INNER JOIN과 NATURAL JOIN 예제 

 

 SELF JOIN 셀프조인

동일 테이블 사이의 조인을 말한다.  테이블의 칼럼명이 모두 동일하기때문에 반드시 별명(ALIAS)를 사용해야한다.

자신과 자신의 직속 상위행은 구할 수 있으나, 차상위행은 한번 더 셀프조인을 수행해야 구할 수 있다.

SELECT 별명1.칼럼명, 별명2.칼럼명
FROM 테이블명 별명1, 테이블명 별명2
WHERE 별명1.상위칼럼명 = 별명2.하위칼럼명;

SELECT JU.J_ID 학생학번, JU.NAMES 이름, SE.NAMES 선배이름
FROM STUDENTS JU, STUDENTS SE
WHERE JU.S_ID = SE.J_ID;

SELECT JU.J_ID 후배번호, JU.NAMES 후배이름, SE.NAMES 선배이름
FROM STUDENTS JU LEFT OUTER JOIN STUDENTS SE
ON JU.S_ID = SE.J_ID
ORDER BY JU.J_ID;

 

반응형

 

💚 계층형 질의 (ORACLE만 지원한다)

계층형 데이터 : 상하관계가 명확한 데이터

계층형 질의 : 계층형 데이터를 조회하는 방법. 

루트노드	계층형 데이터의 최상위 노드를 말한다. 부모를 가지지 않는다. (아래 사진에서 A에 해당)
형제노드	같은 계층에 있는 데이터들을 말한다. (아래 사진에서 B, C / D, E 끼리 형제노드이다)
리프노드	계층형 데이터의 최하위 노드를 말한다. 자식을 가지지 않는다. (아래 사진에서 B, D, E에 해당)

가상칼럼과 함수

가상칼럼	LEVEL	순방향, 역방향 관계없이 루트를 1로 지정한다. 자식을 루트로 하면 부모는 2가 된다. (아래 사진에서 B가 루트이면 A는 2)
	CONNECT_BY_ISLEAF	해당 칼럼이 리프노드이면 1, 아니면 0을 반환한다.
함수	SYS_CONNECT_BY_PATH(칼럼명, '구분자')	루트부터 전개할 데이터까지의 경로를 표시한다.
	CONNECT_BY_ROOT 칼럼	해당 칼럼의 루트데이터를 표시한다.

전개 방법

PRIOR  = '이전의' 라는 뜻을 가지고 있다. 상위 행의 값을 참조한다.

순방향전개	역방향전개
부모 = PRIOR 자식	PRIOR 부모 = 자식
이전의 자식값 중에서 부모(기준값)을 찾는다 부모가 자식을 결정한다	이전의 부모값 중에서 자식(기준값)을 찾는다 자식이 부모를 결정한다

계층형 질의	Oracle
구문	SELECT ~ FROM ~ WHERE ~ START WITH 루트노드 지정조건 CONNECT BY [NOCYCLE] PRIOR 전개방향 조건 [ORDER SIBILINGS BY 컬럼명] - NOCYCLE : 상사가 꼬리에 꼬리를 무는 것 방지 - SIBILINGS BY : 형제들 정렬 기준 정의
예시	SELECT CONNECT_BY_ROOT 사원 '루트', SYS_CONNECT_BY_PATH(사원, '/') '경로', LEVEL, 사원, 관리자 FROM 사원테이블 START WITH 사원='D' CONNECT BY PRIOR 관리자 = 사원; -- D를 루트로 지정하겠다. -- 사원이 관리자를 결정한다. (역방향전개) -- D와 연결된 C가 LEVEL 2로 오게된다 -- C와 연결된 A가 LEVEL 3로 오게된다 루트    경로    LEVEL  사원  관리자 -------------------------------------- D      /D          1       D       C D      /D/C       2       C       A D      /D/C/A    3       A	SELECT CONNECT_BY_ROOT 사원 '루트', SYS_CONNECT_BY_PATH(사원, '/') '경로', LEVEL, 사원, 관리자 FROM 사원테이블 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자; -- A를 루트로 지정하겠다. -- 관리자가 사원을 결정한다. (순방향전개) -- A와 연결된 B, C가 LEVEL 2로 오게된다 -- C와 연결된 D, E가 LEVEL 3로 오게된다 루트    경로    LEVEL  사원  관리자 -------------------------------------- A      /A          1       A         A      /A/B       2       B         A A      /A/C       2       C         A A      /A/D       3       D         C A      /A/E        3       E         C

📌문제

START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
다음 중 틀린 것은?

2) CONNECT_BY_ISLEAF 는 LEAF 면 1을 아니면 0 을 반환한다
3) 자식에서 부모로 가는 역방향이다      // PRIOR 사원 = 관리자 : 관리자가 사원을 결정한다. 순방향전개이다.
4) LEVEL 은 계층의 깊이를 의미하며 KING 은 최상위 계층이다

 

💚 SUB QUERY 서브쿼리

하나의 SQL문 안에 포함된 또 다른 SQL문.

서브쿼리는 괄호로 감싸서 사용하고, ORDER BY는 메인쿼리에서만 사용할 수 있다.

반환값에 따른 분류

동작방식, 사용위치에 따른 분류

 

 

 

💚 뷰 VIEW : 가상테이블

실제 데이터를 가지고 있지 않지만 테이블의 역할을 수행할 수 있다.

때문에 가상 테이블이라고도 한다.

 

뷰의 장점

독립성	테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성	복잡한 질의를 뷰로 생성하면 관련 질의를 편리하게 작성할 수 있다.
보안성	뷰 생성 시 특정칼럼(급여정보..)을 빼고 생성하여 정보를 감출 수 있다.

 

뷰 사용방법

생성
CREATE VIEW 뷰이름 AS SELECT문장;

사용 (뷰를 사용하는 경우에는 DBMS가 내부적으로 SQL문을 재작성한다)
SELECT * FROM 뷰이름;     = SELECT * FROM (SELECT문장);

삭제
DROP VIEW 뷰이름;

 

---

계층형질의 관련문제 오답노트 

[SQLD] 34회 기출복원 오답노트

 

 

 

 

 

 

#SQLD #집합연산자 #순수관계연산자 #조인 #JOIN #조인작성법 #JOIN과 별명 #INNERJOIN #내부조인 #NATURALJOIN #자연조인 #OUTERJOIN #외부조인 #LEFTJOIN #RIGHTJOIN #FULLJOIN #SELFJOIN #계층형질의 #계층형질의전개방법 #서브쿼리 #SUBQUERY #서브쿼리분류 #스칼라서브쿼리 #인라인뷰 #뷰 #VIEW #가상테이블