[mySQL] 서브쿼리를 이용한 데이터 분석

command + f 혹은 Ctrl + f를 활용하여 검색, 또는 블로그 하단 검색 Input 활용해 검색

 

목차

• SELECT 절에서 사용되는 서브쿼리
• WHERE 절에서 사용되는 서브쿼리
• FROM 절에서 사용되는 서브쿼리
• 서브쿼리를 나누는 또다른 기준, 상관 서브쿼리와 비상관 서브쿼리

 

---

서브쿼리란 무엇인가? (이해하기)

 

 

SELECT 절에 사용된 서브쿼리(스칼라 서브쿼리) - 서브쿼리가 리턴하는 값이 하나의 column 하나의 row

 

예를 들어, 우리가 학생들의 시험 점수를 가지고 있고, 각 학생의 점수가 전체 평균 점수와 얼마나 차이가 나는지 알고 싶다면 다음과 같이 작성이 가능하다.

SELECT student_name, score, 
       (SELECT AVG(score) FROM scores) - score AS difference
FROM scores;

 

(SELECT AVG(score) FROM scores) - score AS difference 는 scores테이블의 score 컬럼의 전체 평균을 구한 뒤,

평균 값에서 각 행에 대해 score 값을 빼는 연산을 수행한 뒤 difference 컬럼(열)에 할당한다.

 

만약 평균 점수가 80이고, A학생의 점수가 90이면 이 A학생의 difference 값은 80(평균 점수) - 90(A학생의 점수) = -10;

 

 

 

WHERE 절에 사용된 서브쿼리 - 서브쿼리가 리턴하는 값이 하나의 column 여러개의 row

 

보통 단일 값을 반환하여 바깥 쿼리에서 조건 비교로 사용된다.

 

예를 들어, 한 학년의 모든 학생 중에서 평균 성적보다 높은 성적을 받은 학생들을 찾고싶다면 다음과 같이 작성할 수 있다.

SELECT student_name, score 
FROM scores
WHERE score > (
  SELECT AVG(score) 
  FROM scores
);

 

이 문에서 (SELECT AVG(score) FROM scores) 가 서브쿼리이고, 이 문의 역할은 다음과 같다.

scores 테이블의 score 컬럼의 전체 평균값을 가져오고, 이 전체 평균값보다 점수가 높은 학생의 이름과 점수를 선별한다.

 

 

WHERE 절에서 서브쿼리와 활발하게 사용되는 조건문

 

IN, ANY, ALL

 

IN: 서브쿼리가 반환하는 리스트 중 하나와 일치하는지 확인한다.

SELECT title 
FROM books
WHERE book_id IN (
    SELECT book_id 
    FROM reviews
);

 

 

ANY: 서브쿼리가 반환하는 값들 중에 하나라도 조건에 만족하면 TRUE 리턴.

SELECT title 
FROM books
WHERE price > ANY (
    SELECT price 
    FROM reviews
);

 

ALL: 서브쿼리가 반환하는 모든 값들과 일치하는지 확인한다.

SELECT title 
FROM books
WHERE price > ALL (
    SELECT price 
    FROM reviews
);

 

 

FROM 절에서 사용되는 서브쿼리(derived table, 인라인 뷰(Oracle)) - 서브쿼리가 리턴하는 값이 여러개의 column, 여러개의 row

주의! 모든 derived table은 AS를 붙여줘야 한다. (아래 예시 코드확인)

 

derived table은 그 SQL문 안에서만 유효하다.

더보기: 정적 테이블과 동적 테이블

일반적인 테이블은 미리 정의되어 있고, 데이터도 미리 저장되어 있다. 이 것이 정적 테이블이다.

동적 테이블은 프로그램이나 쿼리가 실제로 실행되는 도중에 만들어지는 것을 말한다.

 

따라서 실제 필요한 시점에 만들어 진다는 것을 의미한다.

 

쿼리가 실행될 때마다 동적으로 생성된다. 다음과 같이 사용될 수 있다.

SELECT *
FROM (
    SELECT id, name
    FROM student
    WHERE grade > 90
) AS top_students  // derived table은 AS 붙여주기
WHERE name LIKE 'J%';

이 문에서는 '메인 테이블' 이라는 것이 딱히 없다. FROM에는 서브쿼리가 생성하는 임시 테이블 이라고 할 수 있다.

 

 

비상관 서브쿼리와 상관 서브쿼리란?

    비상관 서브쿼리

메인 쿼리의 어떤 부분도 참조하지 않는 서브쿼리를 말한다. 다음은 비상관 서브쿼리의 예시 SQL문이다.

SELECT name
FROM student
WHERE age > (SELECT AVG(age) FROM student);

 

    상관 서브쿼리

메인 쿼리의 어떠한 부분을 참조하는 서브쿼리를 말한다. 다음은 상관 서브쿼리의 SQL문이다.

SELECT name
FROM student AS s1
WHERE age > (SELECT AVG(age) FROM student AS s2 WHERE s2.class = s1.class);

s2.class = s1.class 부분에서 s2가 s1을 참조하고 있기 때문에 상관 서브쿼리 라고 할 수 있다.

상관 서브쿼리의 값은 메인 쿼리의 현재 행에 따라 달라진다. 그러므로 상관 서브쿼리는 메인 쿼리의 각 행에 실행된다.

 

 

 

EXISTS와 NOT EXISTS 연산자

각각 '존재한다면', '존재하지 않으면' 이라는 뜻을 가지고 있고 다음과 같이 사용되며, 상관 서브쿼리와 자주 함께 사용된다.

 

EXISTS의 예시 SQL문은 다음과 같다.

SELECT s.name
FROM student s
WHERE EXISTS (
    SELECT 1  // 혹은 * 설명은 아래 확인
    FROM course c
    WHERE c.student_id = s.id
);

상관 서브쿼리 SELECT에 1이나 상수, 혹은 *을 넣어 사용이 가능하다. 이는 '하나 이상의 행이 존재하는지?' 혹은 '존재하지 않는지'만을 판단하기 때문에 어떤 특정한 값을 선택하는 것은 중요하지 않다.

 

더보기: 1을 사용하는 이유

몇몇 데이터베이스 엔진은 SELECT 1을 사용할 경우 최적화를 더 잘 수행하는 경우가 있으므로, 이런 패턴이 자주 사용된다.