서브쿼리

쿼리 안에 들어가는 다른 쿼리

대표적으로 SELECT, FROM, WHERE절에서 사용 가능

서브 쿼리(부속 질의) 밖에 있는 쿼리 -> 메인 쿼리 또는 주 질의

 

일반적으로 대량의 데이터를 다루는 경우, 데이터를 모두 합쳐서 연산하는 JOIN보다 필요한 데이터만 찾아서 조회하는 서브 쿼리가 성능이 좋다.

 

종류

1.SELECT절에 사용하는 스칼라 서브 쿼리 : 단일행 단일열만 가능하다.

2.FROM절에 사용하는 인라인 뷰

3.WHERE절에 사용하는 중첩 질의

 

쿼리 실행 순사

-서브 쿼리가 먼저 실행 >메인 쿼리 실행

-서브 쿼리의 값이 먼저 반한 >메인 쿼리에서 비교

 

작성 시, 주의 사항

서브 쿼리는 반드시 ()괄호 안에 작성한다.

 

FROM절에 사용하는 서브 쿼리, 인라인 뷰

-FROM절에 사용하는 서브 쿼리를 말한다.

-인라인 뷰 라는 이름은, 결과를 뷰 형태로 반환하기 때문이다.

-뷰는 임시로 만들어진 가상 테이블을 말한다.

-FROM절에서 사용한 서브 쿼리의 결과를 하나의 테이블로 사용한다고 생각하시면 된다.

-인라인 뷰를 사용하면 일반적인 테이블처럼 사용가능하다.

-반환되는 값은 다중 열, 다중 행 전부 가능하다.

 

SELECT

    조회할_열_이름

*

FROM

    (SELECT*FROM table_name)

JOIN도 가능, WHERE 사용 가능. 등등

 

회원 레벨이 2인 회원들 중, 판매하는 상품 가격이 60만원 이상인 판매자의 ID, 상품 이름, 각 상품의 가격을 조회한 후, 내림 차순 정렬

>인라인 뷰, JOIN 사용, ORDER BY

>인라인뷰 -->레벨2 회원을 찾아요.

 

SELECT
	g.g_seller_id
,	mem.m_name
,	g.g_name
,	g.g_price
FROM
	(select
		m.m_id
	,	m.m_name
	from
		tb_member AS m
	where
		m.m_level =2) AS mem
	INNER join
		tb_goods AS g
	on
		mem.m_id = g.g_seller_id
WHERE
	g.g_price >=600000
ORDER BY g.g_price;

===========================

WHERE절에 사용하는 서브쿼리, 중첩 질의

-WHERE절에 사용 할 수 있다.

-IN연산자, 비교 연산자를 사용할 수 있다.

 

IN:

 

goods table에서 각 제품의 평균 가격을 구하시오

평균 가격보다 비싼 가격의 제품의 이름과 가격을 조회하세요.

이걸 서브쿼리 이용해서 넣어주면

짠!

 

 

다중 행 연산자 IN, ANY 를 많이 사용해봤대 쌤이.

IN:리턴되는 값 중에서 조건에 해당하는 값이 있으면 참.

WHERE 컬럼 = 값 -> 이퀄 기호를 사용하면 반환되는 값이 단일 행을 반환하는 경우에만 사용 가능.

그래서 이럴때 IN를 사용해줘서 해결가능함!

IN연산자를 사용하면 반환 값이 여러 개 있을 경우, 다중 행을 반환할 수 있다.!!

 

ANY, SOME: 서브 쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참이다.

ALL: 서브 쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 모든 값을 만족하면 참이다.

 

EXISTS, NOT EXISTS

데이터의 존재 유무 확인한다. 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나도 존재하면 참이다.

 

WHERE 절 서브 쿼리 사용 시, 주의할 점

1.메인 쿼리의 WHERE절에서 비교하는 컬럼의 수와 서브 쿼리에서 조회하는 컬럼의 수 불일치로 발생하는 에러.

 

SELECT절에 사용하는 스칼라 서브 쿼리

-SELECT절에 사용할 수 있다.

-단일 행과 단일 열의 결과값을 반환한다.

-결과값이 다중 행 또는 다중 열이면 에러 발생

-결과가 없다면 NULL을 출력한다.

 

*다중 행 또는 다중 열이라면, DBMS가 어떤 값을 출력해야 하는지 알 수 없어요.

하나의 값만 출력해야 하는데, 조회 값이 여러 개.

 

구매자(고객) 별 총 구매 금액과 고객의 이름을 출력하시오.

힌트>고객의 이름 출력 스칼라 서브 쿼리.

힌트>스칼라 서브 쿼리 FROM절 다음에 테이블 조인 -> 조인 시 사용하는 별칭(AS)을 스칼라 서브쿼리에 가져와서 사용할 수 있다.

 

스

 

 

스칼라 서브 쿼리는 데이터가 많으면 반복적으로 SELECT를 수행한다. 성능 저하 문제 발생.

 

서브 쿼리가 단일 행을 반환 또는 다중 행을 반환에 따라서 분류 할 수 있다.

 

단일 행 부속 질의(서브 쿼리)

-서브 쿼리에서 하나의 행을 반환해서 메인 쿼리에 전달한다.

-스칼라 서브 쿼리,비교 연산자에서 주로 나타남.

 

다 중 행 서브 쿼리

-서브 쿼리 결과가 여러 개의 행을 반환하는 경우.

-IN연산자를 사용하여 여러 행을 처리 할 수 있다.

========================================================

UNION(기본 값)

-여러 쿼리의 합집합

-여러 SQL문을 합쳐서 하나의 SQL문으로 만드는 방법.

-예전 테이블의 데이터를 새로 만든 테이블과 함께 추출해야하는 업무.

-중복결과를 없애고 데이터를 출력한다.

-중복된 행은 한 번만 출력한다.

 

UNION ALL

-중복결과까지 포함해서 데이터를 출력한다.

-중복된 행도 그대로 출력한다.

 

실행속도는 UNION ALL이 빠름.(UNION ALL >UNION)

규칙

-컬럼의 개수가 동일해야 한다.

-컬럼의 이름을 동일하게.(다르면 AS사용해서 맞춰줄수 있음. 예: user_id AS member_id)

-컬럼의 데이터 타입이 동일해야 한다.

중요...!

유니온 사용 규칙이 DBMS마다 허용하는 범위 차이가 있다!

*규칙을 지켜서 작성하는 것이 좋다!

 

 

===========================================

VIEW

-VIEW는 사용자에게 접근이 허용된 자료를 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 만들어진 가상의 테이블

 

장점

-보안강화

-간단한 SQL문을 사용 가능

-사용자에 필요한 내용만 보이도록 할 수 있다.

 

특징

-이름을 가지는 가상 테이블

-ALTER 구문을 사용할 수 없다.

 

테이블 정의

 

MySQL은 ALTER VIEW허용.

 

종류

단순 뷰:하나의 테이블과 연관된 뷰

복합 뷰: 두 개 이상의 테이블과 연관된 뷰

 

장점

-편리성, 재사용성

-보안성

-독립성

 

VIEW 생성

-테이블 만들기 때문에 CREATE문을 사용한다.

-tb_member > tb_member 구분을 위해 v_뷰이름

-view

 

구문

CREATE 뷰_이름

AS

SELECT문;

 

결과 확인

SELECT

     조회할 열 이름

FROM

     정의한 뷰 이름;

*뷰 생성 시, 조회할 열 이름을 입력하고, 뷰를 조회할때, 원하는 열 이름만 작성해서 조회할 수 있다.

 

단순 뷰

city table에서 district가 England인 국가의 이름과 국가 코드를 조회하는 뷰를 만드시오.

뷰 조회 시, 이름만 조회.

 

뷰 조회 시, 이름만 조회.

 

VIEW 삭제

DROP VIEW 뷰_이름

 

뷰 조회 시, 구매자의 아이디와 제품명, 가격을 조회

============================

CTE(with)절

CTE = Common Table Expression

-MySQL8 버전 이상에서 사용 가능

-임시로 쿼리 결과를 저장하고, 사용한다고 생각.

 

구문:WITH

CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있따. 그리고 더 간결하다는 장점이 있다.

 

CTE vs VIEW

VIEW는 생성하려면 권한이 필요하지만, CTE는 필요가 없다

하나의 쿼리문이 종료될 때 까지만 유지되는 일회성 테이블이다.

-주로 복잡한 쿼리문에서 가독성과 코드 재사용성을 높이기 위해 사용한다.

 

미리 추출한 테이블을 CTE로 정의한다.

필요한 컬럼과 데이터를 미리 정해서 활용할 수 있다.

 

구문

WITH cte_이름(열_이름)

AS

(

    ...쿼리문 --> 테이블 추출

)

 

--> 여기까지를 하나로 사용. 쿼리문의 결과 자체를 테이블로 사용할 수 있다.

 

예시

WITH cte_test(userId, total)

AS

(

           SELECT

           m_id

           ,SUM(amount * price)

         FROM

                         test_table

);

 

SELECT

*

FROM

          cte_test

GROUP BY total;