서브쿼리에 대해 알아보자(인라인뷰, exists, insert)

인라인뷰, exists 서브쿼리, 서브쿼리를 사용한 insert문을 차례대로 알아보자.

 

1. 인라인뷰

인라인뷰는 from절에서 서브쿼리를 사용하여 가상의 테이블을 만들어 사용하는 것을 말한다.

이는 주로 다른 테이블로부터 값을 가져와서 기준 테이블과 조인하거나, 서브쿼리 결과를 필터링하기 위해 사용된다.

 

예시

emp테이블과 manager테이블을 조인하여 각 직원의 부서 정보를 가져오자.

select name, department
from employees
where department in (select department from managers)

 

위 쿼리에서 서브쿼리는 managers  테이블에서 부서 정보를 가져온다.

그리고 이 부서정보를 가지고 emp  테이블에서 조건에 맞는 직원을 선택한다.

 

응용1

students 테이블과 grades 테이블에서 각 학생의 이름과 평균 성적을 출력하는 쿼리를 인라인 뷰를 사용하여 학생별 평균 성적을 계산해보자.

-- student 테이블
| id | name   | age | gender |
|----|--------|-----|--------|
| 1  | Alice  | 20  | Female |
| 2  | Bob    | 22  | Male   |
| 3  | Carol  | 21  | Female |

--grades 테이블
| student_id | subject | grade |
|------------|---------|-------|
| 1          | Math    | 85    |
| 1          | English | 90    |
| 2          | Math    | 75    |
| 2          | English | 80    |
| 3          | Math    | 95    |
| 3          | English | 85    |

 

select s.name, avg(g.grade) as avg_grade
from students s
join (
	-- 인라인 뷰
    select student_id, avg(grade) as grade
    from grades
    group by student_id
) as g 
on s.id=g.student_id
group by s.name;

 

위 서브쿼리는 아래와 같은 결과를 반환한다.

| student_id | grade |
|------------|-------|
| 1          | 87.5  |
| 2          | 77.5  |
| 3          | 90.0  |

 

 

응용2

각 학생의 이름과 각 과목의 평균 성적을 출력하는 쿼리를 인라인 뷰를 사용하여 각 과목별 평균 성적을 계산해보자.

-- students 테이블
| id | name   | age | gender |
|----|--------|-----|--------|
| 1  | Alice  | 20  | Female |
| 2  | Bob    | 22  | Male   |
| 3  | Carol  | 21  | Female |

-- subjects 테이블
| id | subject |
|----|---------|
| 1  | Math    |
| 2  | English |
| 3  | Physics |

-- grades 테이블
| student_id | subject_id | grade |
|------------|------------|-------|
| 1          | 1          | 85    |
| 1          | 2          | 90    |
| 2          | 1          | 75    |
| 2          | 2          | 80    |
| 3          | 1          | 95    |
| 3          | 2          | 85    |

 

selct s.name, g.subject, g.avg_grade
from students s
join (
	select subject_id, avg(grade) as avg_grade
    from grades
    group by subject_id
) as g on s.id = g.student_id

*학생마다 과목이 달라질 수 있기에, grades 테이블에서 과목을 구분하는 주요 식별자는 subject_id이기 때문에 해당 컬럼이 group by 기준이 됨

 

응용3

각 도시별로 인구 밀도(population density)를 계산하여 도시 이름(name)과 함께 출력하는 SQL 쿼리 작성

인구 밀도 = 도시의 인구(population)를 도시가 속한 국가의 총 인구(population)로 나눈 값

결과는 도시의 인구 밀도를 내림차순으로 정렬

-- cities 테이블
| id | name      | population | country_id |
|----|-----------|------------|------------|
| 1  | New York  | 8537673    | 1          |
| 2  | London    | 8787891    | 2          |
| 3  | Paris     | 2140526    | 3          |
| 4  | Tokyo     | 9273000    | 4          |


-- countries 테이블
| id | name      | population |
|----|-----------|------------|
| 1  | USA       | 327200000  |
| 2  | UK        | 67886011   |
| 3  | France    | 65273511   |
| 4  | Japan     | 126476461  |

 

select c.name as city_name
	round(c.population / co.population,2) as pop_density
from cities c
join countries co on c.country_id = co.id
order by pop_density desc;

 

 

응용4

각 주문별로 주문한 제품의 총 가격을 계산하여 주문 ID(order_id)와 함께 출력하는 SQL 쿼리를 작성

-- orders 테이블
| order_id | customer_id | order_date |
|----------|-------------|------------|
| 1        | 101         | 2024-01-01 |
| 2        | 102         | 2024-01-02 |
| 3        | 103         | 2024-01-03 |

-- products 테이블
| product_id | product_name | price |
|------------|--------------|-------|
| 1          | Phone        | 500   |
| 2          | Laptop       | 1000  |
| 3          | Tablet       | 800   |

SELECT o.order_id, p.total_price
FROM orders o
JOIN (
    SELECT order_id, SUM(price) AS total_price
    FROM orders
    GROUP BY order_id
) AS p ON o.order_id = p.order_id;

 

---

 

2. exists 서브쿼리

  exists 서브쿼리는 결과가 존재하는지 여부에 따라 참 또는 거짓을 반환함

주로 exists와 함께 where절에서 사용된다.

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (
    SELECT column1, column2, ...
    FROM table2
    WHERE condition
);

exists는 외부 쿼리의 각 행에 대해 서브쿼리의 결과가 적어도 한 번은 존재하는지 여부를 확인하며, 결과가 참이면 외부 쿼리의 해당 행을 반환한다.

SELECT name
FROM employees e
WHERE EXISTS (SELECT 1 FROM salaries s WHERE e.id = s.employee_id);

*select 1 : 결과 집합에 모든 열을 반환하지 않고 각 행에 상수 1을 반환함

일반적으로 특정 조건의 존재 여부 확인 시 사용

 

예시

주문 테이블에서 특정 고객이 최소한 한 번 이상 주문을 했는지 확인하는 쿼리 작성

-- orders 테이블
| order_id | customer_id |
|----------|-------------|
| 1        | 101         |
| 2        | 102         |
| 3        | 101         |
| 4        | 103         |

select customer_id
from orders o
where exists (
	select * 
    from orders
    where customer_id = o.customer_id
)

 

결과

| customer_id |
|-------------|
| 101         |
| 102         |
| 103         |

 

위 쿼리는 각 고객에 대해 최소한 한 번 이상의 주문이 있는지 확인하고 있다.

결과는 해당 조건을 만족하는 모든 고객의 customer_id를 반환한다.

 

적으면서 든 생각인데, 위와 같은 결과는 

select distinct customer_id
from orders

와 같은 결과를 가져온다.

그럼 왜 distinct를 안쓰고 exists 서브쿼리를 사용하는지에 대한 의문이 생겼다!

 

distinct와 exists의 차이를 잠깐 살펴보자면,

exists : 일반적으로 특정 조건이 충족되는지 여부를 확인하거나, 부분 집합의 존재 여부 확인 시 사용됨

distinct : 중복을 제거하는 것이 주 목적

즉, 사용 목적이 다르다는 것이다~(궁금증 해결💡)

 

다시 본론으로 돌아가서 exists에 대한 응용문제를 풀어보겠다.

 

응용1

다음과 같은 조건을 만족하는 고객을 찾는 SQL 쿼리 작성

• 최소한 한 번 이상의 주문을 한 고객이어야 합니다.
• 주문한 제품 중에 'Phone'이 포함되어야 합니다.

-- orders 테이블
| order_id | customer_id | order_date |
|----------|-------------|------------|
| 1        | 101         | 2024-01-01 |
| 2        | 102         | 2024-01-02 |
| 3        | 103         | 2024-01-03 |

-- products 테이블
| product_id | product_name | price |
|------------|--------------|-------|
| 1          | Phone        | 500   |
| 2          | Laptop       | 1000  |
| 3          | Tablet       | 800   |

select customer_id
from orders o
where exists (
			select *
            from products p
            where o.order_id = p.product_id
            and product name = 'Phone'
)

*만약 p가 들어가는 제품을 찾는다면 [and product name like '%p%']

 

응용2

다음과 같은 조건을 만족하는 SQL 쿼리 작성

• 2024년 1월에 주문된 제품의 총 매출(revenue)을 계산합니다.
• 결과는 총 매출(revenue)만을 반환합니다.

-- orders 테이블
| order_id | customer_id | order_date |
|----------|-------------|------------|
| 1        | 101         | 2024-01-01 |
| 2        | 102         | 2024-01-02 |
| 3        | 103         | 2024-01-03 |

-- products 테이블
| product_id | product_name | price |
|------------|--------------|-------|
| 1          | Phone        | 500   |
| 2          | Laptop       | 1000  |
| 3          | Tablet       | 800   |

SELECT SUM(p.price) AS revenue
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.order_id = p.product_id
    AND o.order_date >= '2024-01-01'
    AND o.order_date < '2024-02-01'
)

 

---

 

3. insert를 활용한 서브쿼리

일반적으로 SQL에서는 데이터를 추가할 때 다음과 같은 구문을 사용한다.

INSERT INTO 테이블명 (열1, 열2, ...)
VALUES (값1, 값2, ...);

하지만 때로는 다른 테이블에서 데이터를 가져와서 새로운 테이블에 추가해야 할 때가 있다.

이때 서브쿼리를 사용하여 데이터를 추출하고, 그 결과를 INSERT문에 활용할 수 있다.

 

예시

학생들의 성적을 관리하는 "Students" 테이블과 새로 입학한 학생들의 정보를 담고 있는 "New_Students" 테이블이 있다고 가정.

새로운 학생들 중 성적이 80점 이상이고 "Students" 테이블에 이미 존재하지 않는 학생들만을 "Students" 테이블에 추가하고 싶다고 한다면

INSERT INTO Students (student_id, name, grade)
SELECT student_id, name, grade
FROM New_Students
WHERE grade >= 80
AND student_id NOT IN (SELECT student_id FROM Students);

 

이처럼 INSERT문에서 서브쿼리를 활용하면 다른 테이블의 데이터를 쉽게 가져와서 새로운 테이블에 추가할 수 있다.