SQL

[SQL] 데이터 생성과 조회 : 04. 테이블 조회(JOIN, CASE WHEN, 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리, UNION, WITH ROLLUP, 윈도우 함수)

zzheng 2024. 10. 11. 02:25

INNER JOIN

  • 교집합의 개념으로 기준이 되는 키에 따른 칼럼값이 존재하는 것만 병합해서 결과를 만든다.
  • 시즌1과 시즌2 모두 출연한 사람 출력하기
SELECT * FROM 시즌1;
SELECT * FROM 시즌2;
SELECT * FROM 시즌1 INNER JOIN 시즌2 ON 시즌1.CAST = 시즌2.CAST;
  • IS를 지정해서 시즌1과 시즌2 모두 출연한 사람 출력하기
SELECT * FROM 시즌1;
SELECT * FROM 시즌2;
SELECT * FROM 시즌1 WT1 INNER JOIN 시즌2 WT2 ON WT1.CAST = WT2.CAST;
  • INNER JOIN 을 사용하지않고 출력하기
SELECT WT1.CAST FROM 시즌1 WT1, 시즌2 WT2 WHERE WT1.CAST = WT2.CAST;

 

OUTER JOIN

  • Left Outer Join
    • 왼쪽 테이블의 모든 행을 포함시키면서 조인을 수행한다.
SELECT * FROM 시즌1 WT1 LEFT OUTER JOIN 시즌2 WT2 ON WT1.CAST = WT2.CAST;
  • Right Outer Join
    • 오른쪽 테이블의 모든 행을 포함시키면서 조인을 수행한다. 
SELECT * FROM 시즌1 WT1 RIGHT OUTER JOIN 시즌2 WT2 ON WT1.CAST = WT2.CAST;

 

CASE WHEN

  • IF THEN ELSE 구문과 비슷하다.
  • 조건 A 를 만족할 땐 A라 출력하고, B를 만족하면 B 그 외에는 다 C라고 출력하는 쿼리이다.
SELECT 컬럼명
   		CASE WHEN(조건A) THEN A
        	 WHEN(조건B) THEN B
			 ELSE C END AS 원하는 컬럼명
	FROM TABLE;

 

서브쿼리

  • SQL문의 내부에 DBMS가 제공하는 다양한 함수를 넣을 수 있는 것과 마찬가지로 함수가 들어갈 수 있는 위치에는 그 위치에 맞는 결과를 반환하는 또다른 독립적인 SQL문을 넣는 것이 가능하며, 이를 서브쿼리(Subquery)라고 한다. 
  • 즉, 서브쿼리란 SQL내에 다른 SQL이 포함되어있는 포함되어있는 쿼리이다. 
  • 쿼리의 수행 순서는 메인 쿼리 -> 서브 쿼리 순으로 실행된다.
  • 메인쿼리란, 서브쿼리를 품고있는 쿼리를 뜻한다. 
  • 서브쿼리가 메인쿼리에 종속되기 때문에 단독적으로 실행할 수 없고 메인쿼리를 필터링하는 방식으로 사용된다.
  • 서브쿼리는 들어가는 위치에 따라서 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리가 있다. 
SELECT  (SELECT ... )    --> 스칼라 서브쿼리
FROM    (SELECT ... )    --> 인라인 뷰
WHERE   (SELECT ... )    --> 중첩 서브쿼리

 

1. 스칼라 서브쿼리

  • Select절에 있는 서브쿼리이다. 
  • 칼럼이 입력되는 위치에 삽입되므로 결과는 하나의 칼럼만을 가져야 한다.
SELECT A.ID, A.NAME,
		(SELECT B.NAME FROM GRADE_INFO B WHERE B.CODE = A.GRADE_CODE) AS GRADE
  FROM CUSTOMERS A;

 

2. 인라인 뷰

  • From절에 있는 서브쿼리이다.
  • 뷰가 기존의 테이블로부터 파생되어 동적으로 생성되는 테이블인 것과 마찬가지로 인라인 뷰 역시 쿼리 실행시 생성되는 동적 테이블이라고 볼 수 있다.
  • 반드시 하나의 테이블로 출력되야한다.
 SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
 FROM EMP E,
 	  (SELECT DEPTNO, DNAME, LOC FROM DEPT) D
 WHERE E.DEPTNO = D.DEPTNO;

 

3. 중첩 서브쿼리

  • 쿼리 안에 다른 쿼리가 중첩되어 들어간 경우로, 특히 조건절인 Where절과 HAVING 절에 있는 서브쿼리이다.
  • 메인쿼리와 비교할 수 있다. 
  • 스칼라 서브쿼라나 인라인 뷰와 달리 반환하는 값의 형태가 하나가 아니며 다양한 반환값(단일행, 다중행, 다중칼럼)을 가질 수 있다.
  • 중첩 서브쿼리의 경우 메인쿼리에서 참조하고 있는 테이블의 칼럼을 서브쿼리 내에 다시 사용할 수 있으며, 이렇게 메인쿼리와 연관성을 가진 것을 특별히 연관 서브 쿼리라고 하고 메인쿼리와 연관성이 없으면 비연관 서브쿼리라고 한다. 
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL >= (SELECT AVG(E.SAL) FROM EMP E);

 

집합연산자

  • 두 테이블에 대한 집합 연산(합집합, 교집합 등)을 수행하는 연산자이다.
  • JOIN 이 특정한 기준키를 가지고 두 테이블에 대한 집합 연산을 수행하는 것과 달리 특정한 기준키 없이 두 테이블의 레코드들에 대해서 합집합과 교집합 연산을 수행하므로 두테이블의 칼럼 구성, 즉 스키마가 동일해야한다.
  • 스키마 구성은 동일하나 칼럼의 이름은 다를 수 있는데 이때 반환되는 칼럼 이름은 첫번째 테이블을 따른다.

 

1. UNION ALL

  • 합집합 연산으로 세로로 결합한다.
  • 중복된 개수만큼 그대로 포함된다. 
SELECT * FROM WORLD_TOUR1
UNION ALL
SELECT * FROM WORLD_TOUR2
SELECT CAST AS "출연자", NAME AS "이름" FROM WORLD_TOUR1
UNION ALL
SELECT CAST AS C, NAME AS N FROM WORLD_TOUR2;

 

2. UNION 

  • 중복 레코드는 제거후 출력한다.
SELECT * FROM WORLD_TOUR1
UNION
SELECT * FROM WORLD_TOUR2

 

3. INTERSECT 문

  • 교집합 연산을 수행한다. 
SELECT * FROM WORLD_TOUR1 A
WHERE EXISTS (SELECT 1 FROM WORLD_TOUR2 B WHERE A.CAST = B.CAST);

 

4. MINUS 문

  • 차집합 연산을 수행한다.
SELECT * FROM WORLD_TOUR1 A
WHERE NOT EXISTS (SELECT 1 FROM WORLD_TOUR2 B WHERE A.CAST = B.CAST);

 

WITH ROLLUP

  • 그룹 함수이다.
  • GROUP BY 절에 들어가는 칼럼을 대상으로 하위 그룹핑을 수행하는 함수이다.
  • 일반적으로 소계와 총계를 구할 때 사용한다.
  • 예를 들어, GROUP BY ROLLUP(날짜)와 같이 사용하면, 먼저 날짜별로 그룹핑을 하고, 마지막에 전체를 하나로 묶은 그룹을 추가해준다.
  • GROUP BY ROLLUP(날짜, 이름)의 경우에는 (날짜, 이름) --> (날짜) --> (전체)의 순서로 하위 그룹을 묶어준다.
SELECT ANIMAL, TYPE, COUNT(*) FROM ANIMAL_INFO GROUP BY ANIMAL, TYPE WITH ROOLUP;

 

윈도우 함수

  • 특정 부분을 대상으로 데이터를 계산해주는 함수이다.
  • 행과 행 간의 관계를 나타내는 연산을 쉽게 하기 위한 함수이다.
  • 하나의 칼럼 내에서 각 행에 대해서 연산을 수행한다는 점에서 GROUP BY 연산과 유사하지만 GROUP BY 연산은 각 행을 대상으로 연산을 수행한 다음 새로운 구성을 만드는데 비하여, 윈도우 함수는 각 행의 기존 구성을 유지한 상태로 해당 행에 대해서 새로운 값을 추가하거나 아니면 기존의 값을 변경한다는 점에서 차이가 있다.
  • 모든 윈도우 함수는 OVER 키워드와 함께 사용된다.
  • 대표적인 함수로 순위함수인 ROW_NUMBER, RANK, DENSE_RANK이 있다.

 

1. ROW_NUMBER

  • 동일 순위라도 각각의 행이 고유의 순위값을 가진다.
  • 예) 1,2,3,4,5,6,7,

 

2. RANK

  • 동일 순위는 같은 순위값을 가진다. 
  • 순위값은 앞 순위까지의 누적 개수 +1이 된다.
  • 예) 1,2,2,4,4,4,7,

 

3. DENSE_RANK

  • 동일 순위는 같은 순위값을 가진다.
  • 순위값은 단순하게 앞 순위 +1이다.
  • 예) 1,2,2,3,3,3,4,
SELECT FROM FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY,
	ROW_NUMBER() OVER(PARTITION BY DEAPRTMENT_ID ORDER BY SALARY DESC) AS ROW_NO,
    RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK_NO,
    DENSE_RANK()OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS DENSE_RANK_NO
  FROM EMPLOYEES
  ORDER BY DEPARTMENT_ID, ROW_NO;