[ PostgreSQL ] sub query - 서브 쿼리로 조회하기

PostgreSQL에서 서브쿼리(subquery)는 하나의 SQL 쿼리 안에서 또 다른 쿼리를 실행하여 그 결과를 바탕으로 작업을 수행하는 방법입니다. 서브쿼리는 주로 데이터 검색에서 복잡한 조건을 구현하거나, 다른 테이블의 데이터를 참조할 때 사용됩니다.

서브쿼리는 SELECT, INSERT, UPDATE, DELETE 문에서 사용할 수 있으며, 일반적으로 다음 두 가지 유형으로 나뉩니다:

1. 스칼라 서브쿼리: 하나의 값을 반환하는 서브쿼리.
2. 테이블 서브쿼리: 여러 행 또는 열을 반환하는 서브쿼리.

서브쿼리의 기본 문법

서브쿼리는 일반적으로 괄호로 감싸서 메인 쿼리 안에서 사용합니다. 다음은 서브쿼리의 기본 구조입니다.

SELECT 열1, 열2, ...
FROM 테이블이름
WHERE 열1 = (서브쿼리);

---

1. 서브쿼리의 종류와 사용 예시

1.1 스칼라 서브쿼리 (단일 값 반환)

서브쿼리가 하나의 값만 반환하는 경우, 스칼라 서브쿼리라고 합니다. 주로 WHERE 절이나 SELECT 절에서 사용됩니다.

예시: 가장 나이가 많은 사용자의 나이를 조회

users 테이블에서 나이가 가장 많은 사용자의 정보를 조회하려면:

SELECT * FROM users
WHERE age = (SELECT MAX(age) FROM users);

이 쿼리는 SELECT MAX(age) FROM users 서브쿼리가 나이가 가장 많은 값을 반환하고, 그 값을 WHERE 조건에서 사용하여 그 나이에 해당하는 사용자를 조회합니다.

---

1.2 다중 행 서브쿼리 (IN, ANY, ALL 사용)

서브쿼리가 여러 행을 반환할 수 있습니다. 이때 IN 절이나 ANY, ALL 등의 연산자를 사용하여 서브쿼리에서 반환된 여러 값을 메인 쿼리와 비교할 수 있습니다.

예시: 특정 나이 이상의 사용자들보다 나이가 적은 사용자 조회

예를 들어, 나이가 30 이상인 사용자들의 나이보다 작은 사용자들을 조회하려면:

SELECT * FROM users
WHERE age < ANY (SELECT age FROM users WHERE age >= 30);

ANY는 서브쿼리에서 반환된 값 중 하나라도 조건을 만족하면 해당 데이터를 반환합니다.

예시: 특정 사용자 그룹에 속하는 이메일을 가진 사용자들 조회

특정 나이대에 있는 사용자들의 이메일을 조회하려면:

SELECT email FROM users
WHERE age IN (SELECT age FROM users WHERE age BETWEEN 30 AND 40);

이 쿼리는 나이가 30에서 40 사이인 사용자의 이메일을 반환합니다.

---

1.3 EXISTS 서브쿼리 (존재 여부 확인)

EXISTS는 서브쿼리의 결과가 존재하는지 여부를 확인하는 데 사용됩니다. EXISTS는 서브쿼리에서 한 행이라도 반환되면 참(TRUE)을 반환하며, 아무 것도 반환되지 않으면 거짓(FALSE)을 반환합니다.

예시: 특정 나이대의 사용자가 존재하는지 확인

나이가 40 이상인 사용자가 있는지 확인하고, 해당 사용자가 있을 경우 전체 사용자 목록을 반환하는 쿼리:

SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM users WHERE age >= 40);

SELECT 1은 서브쿼리가 결과를 반환할 수 있으면 TRUE를 반환하고, 메인 쿼리가 실행됩니다.

---

1.4 서브쿼리를 FROM 절에서 사용하기

서브쿼리는 FROM 절에서도 사용할 수 있습니다. 이는 서브쿼리에서 반환된 결과를 임시 테이블처럼 사용하는 방식입니다.

예시: 각 사용자 그룹의 평균 나이를 구하고 그 결과를 조회

먼저 사용자 나이의 평균을 계산하는 서브쿼리를 FROM 절에 사용한 후, 그 평균을 기준으로 결과를 조회할 수 있습니다.

SELECT name, age FROM (
    SELECT name, age, AVG(age) OVER () AS avg_age
    FROM users
) AS subquery
WHERE age > avg_age;

이 쿼리는 각 사용자의 나이를 전체 평균 나이와 비교하여 평균 나이보다 나이가 많은 사용자를 조회합니다.

---

1.5 서브쿼리와 조인 결합

서브쿼리는 조인(Join)과 결합하여 사용할 수도 있습니다. 일반적으로 조인은 데이터를 테이블 간에 결합하여 반환하지만, 서브쿼리를 함께 사용하면 더 복잡한 조건을 구현할 수 있습니다.

예시: 사용자 중에서 이메일이 다른 사용자와 같은 나이대에 속하는 사용자만 조회

SELECT u1.name, u1.email
FROM users u1
JOIN (SELECT age, email FROM users WHERE email LIKE '%@example.com') u2
ON u1.age = u2.age;

이 쿼리는 example.com 도메인을 가진 사용자와 같은 나이대를 공유하는 모든 사용자를 조회합니다.

---

1.6 업데이트 후 결과 바로 확인하기

UPDATE events SET event_date = '2024-02-01' WHERE id = 1 RETURNING *;

2. 서브쿼리와 성능 고려 사항

서브쿼리는 매우 유용한 도구이지만, 복잡한 서브쿼리가 포함된 쿼리는 성능 저하를 초래할 수 있습니다. 특히 서브쿼리가 다중 행을 반환하거나, 서브쿼리 내부에서 인덱스가 잘못 설정된 경우 성능이 크게 저하될 수 있습니다.

• 인덱스 설정: 서브쿼리에서 사용되는 필드에 적절한 인덱스를 설정하면 성능을 크게 개선할 수 있습니다.
• 조인과 비교: 서브쿼리를 사용하기보다는 조인(JOIN)을 사용하는 것이 더 효율적인 경우도 있습니다. 따라서 상황에 따라 조인과 서브쿼리의 사용을 적절히 조합하는 것이 좋습니다.

---

요약

• 스칼라 서브쿼리: 하나의 값만 반환하여 메인 쿼리에서 사용됩니다.
• 다중 행 서브쿼리: 여러 값을 반환하고, IN, ANY, ALL 등과 함께 사용됩니다.
• EXISTS 서브쿼리: 서브쿼리의 결과가 존재하는지 여부를 확인할 때 사용됩니다.
• FROM 절 서브쿼리: 서브쿼리의 결과를 임시 테이블로 간주하고 이를 기반으로 조회할 수 있습니다.
• 서브쿼리와 조인: 조인과 서브쿼리를 결합하여 더 복잡한 조건을 구현할 수 있습니다.

서브쿼리는 PostgreSQL에서 매우 강력한 기능으로, 복잡한 데이터 검색을 효율적으로 처리할 수 있습니다. 적절한 상황에서 서브쿼리를 사용하는 것이 중요하며, 성능 문제를 고려하면서 사용해야 합니다.