29강 SQL이 뭐지? PostgreSQL Subquery

29강 SQL이 뭐지? PostgreSQL Subquery   

PostgreSQL

SQL이 뭐지? 29강 시작합니다.

 

이번 강의에서는

▶ PostgreSQL Subquery

에 대해서 알아보겠습니다.

 

이번 강의는 PostgreSQL Subquery입니다.

복잡한 쿼리를 Subquery로 구현할 수 있습니다.

실무에서 아주 많이 사용되니 집중해서 봐주세요.

---

Introduction to PostgreSQL subquery   

간단한 예를 들어 보겠습니다.

평균 대여료보다 대여료가 높은 영화를 찾고 싶다고 가정해 보겠습니다. 두 단계로 수행할 수 있습니다.

● SELECT 문 및 평균 함수(AVG)를 사용하여 평균 대여료를 구합니다.
● 두 번째 SELECT 문의 첫 번째 쿼리 결과를 사용하여 원하는 필름을 찾습니다.

 

다음 쿼리는 평균 임대율을 가져옵니다.

SELECT
   AVG (rental_rate)
FROM
   film;   

평균 대여료는 2.98입니다.

이제, 우리는 평균 대여료보다 더 높은 대여료율을 가진 영화들을 얻을 수 있습니다.

SELECT
    film_id,
    title,
    rental_rate
FROM
    film
WHERE
    rental_rate > 2.98;   

코드가 그렇게 우아하지 않아서 두 단계가 필요합니다. 

우리는 첫 번째 쿼리의 결과를 두 번째 쿼리에 전달하는 방법을 원합니다. 

해결책은 하위 쿼리를 사용하는 것입니다.

하위 쿼리는 SELECT, INSERT, DELETE 및 UPDATE와 같은 다른 쿼리 내부에 중첩된 쿼리입니다. 

이 강의에서는 SELECT 문에만 초점을 맞추고 있습니다.

하위 쿼리를 구성하기 위해 두 번째 쿼리를 괄호 안에 넣고 WHERE 절에서 표현식으로 사용합니다.

SELECT
    film_id,
    title,
    rental_rate
FROM
    film
WHERE
    rental_rate > (
              SELECT
                 AVG (rental_rate)
              FROM
                 film
              );   

 

괄호 안의 쿼리를 하위 쿼리 또는 내부 쿼리라고 합니다. 하위 쿼리를 포함하는 쿼리를 외부 쿼리라고 합니다.

PostgreSQL은 하위 쿼리를 포함하는 쿼리를 다음 순서로 실행합니다.

● 먼저 하위 쿼리를 실행합니다.
● 둘째, 결과를 가져와 외부 쿼리에 전달합니다.
● 셋째, 외부 쿼리를 실행합니다.

 

PostgreSQL subquery with IN operator   

하위 쿼리는 0개 이상의 행을 반환할 수 있습니다. 이 하위 쿼리를 사용하려면 WHERE 절에서 IN 연산자를 사용합니다.

예를 들어, 2005-05-29와 2005-05-30 사이의 반환 날짜를 가진 필름을 가져오려면 다음 쿼리를 사용합니다.

SELECT
  inventory.film_id
FROM
  rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE
  return_date BETWEEN '2005-05-29'
                  AND '2005-05-30';   

83 개의 행이 추출됩니다.

 

그런데  PostgreSQL TUTORIAL에서 제공하는 SQL에 오류가 있습니다.

SUBQUERY를 설명하는 부분은 맞지만 WHERE 조건절이 오류입니다.

 

지난 강의에서도 언급한 적이 있는데

return_date는 TIMESTAMP형입니다.

예시된 SQL의 결과에서는 2005-05-30의 데이터는 추출되지 않습니다.

 

10강에서 언급했던 내용 

※※※ 중요 ※※※ payment_date가 date형이라면 위의 쿼리가 맞습니다. 그러나 payment_date는 timestamp형입니다. where 조건문이 오류입니다.  PostgreSQL Tutorial 오류를 발견했네요. WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15'는 오류입니다.   조건을 만족하는 다음의 방법이 있습니다.  1) WHERE payment_date BETWEEN '2007-02-07 00:00:00.000000' AND '2007-02-15 23:59:59.999999' 2) WHERE to_char(payment_date, 'YYYY-MM-DD') BETWEEN '2007-02-07' AND '2007-02-15' 3) WHERE date(payment_date) BETWEEN '2007-02-07' AND '2007-02-15' 1번은 timestamp형에 맞게 일자를 set 하였습니다. 2번은 to_char 함수를 사용해서 char로 변경하였습니다. 3번은 timestamp형을 date형으로 변환했습니다.   여기서 2번, 3번 방법은 결과는 나오지만 추천하지 않습니다. WHERE절에서 테이블의 컬럼에 함수를 사용하는 것은 금기시됩니다. 조건 상수에 함수를 사용하는 것은 가능합니다. 왜냐하면, 조건 상수는 한 번만 변환되지만 칼럼은 행을 가져올 때마다 변환합니다. 쿼리의 성능이 떨어집니다.   위의 쿼리는 초보들이 흔히 하는 실수입니다. 여러분들은 실수하지 않기를 바랍니다.

 

그러면 쿼리를 수정해 보겠습니다. 확인을 위해 return_date도 출력해봅니다.

SELECT
  inventory.film_id,

  return_date
FROM
  rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE
  return_date BETWEEN '2005-05-29 00:00:00.000000'
                  AND '2005-05-30 23:59:59.999999';     

'2005-05-30' 데이터까지 181건이 출력되었습니다.

직접 쿼리를 수행해보시고 차이를 이해하세요.

 

이어지는 예문은 수정해서 진행합니다.

---

여러 행을 반환하므로 다음과 같이 쿼리의 WHERE 절에서 이 쿼리를 하위 쿼리로 사용할 수 있습니다.

SELECT
   film_id,
   title
FROM
   film
WHERE
   film_id IN (
        SELECT
           inventory.film_id
        FROM
           rental
        INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
        WHERE
           return_date BETWEEN '2005-05-29 00:00:00.000000'
                           AND '2005-05-30 23:59:59.999999'
           );   

---

PostgreSQL subquery with EXISTS operator   

다음 표현식은 EXISTS 연산자를 사용하여 하위 쿼리를 사용하는 방법을 보여줍니다.

 

EXISTS subquery   

 

하위 쿼리는 EXISTS 연산자의 입력일 수 있습니다. 하위 쿼리에서 행을 반환하면 EXISTS 연산자는 TRUE를 반환합니다. 하위 쿼리가 행을 반환하지 않으면 EXISTS 연산자의 결과는 FALSE입니다.

EXISTS 연산자는 하위 쿼리에서 반환되는 행의 수에만 신경을 쓰므로 EXISTS 연산자의 일반적인 코딩 규칙은 다음과 같습니다.

EXISTS (SELECT 1 FROM tbl WHERE condition);   

 

다음 쿼리를 참조하십시오.

SELECT
      first_name,
      last_name
FROM
      customer
WHERE
      EXISTS (
            SELECT
                  1
            FROM
                  payment
            WHERE
                  payment.customer_id = customer.customer_id
      );   

쿼리는 customer_id 열의 내부 조인처럼 작동합니다. 

그러나 내부 조인과는 다릅니다.

결제 테이블에 고객의 결제가 여러 건이 있어도 고객 테이블의 한 행만 반환됩니다.

---

복잡한 쿼리를 구성하는 PostgreSQL 하위 쿼리를 학습했습니다.

실무에서 많이 쓰이는 내용이니 숙지하시기 바랍니다.

 

수고하셨습니다.

다음 강의에서 만나요.

---

본 강의는 PostgreSQL Tutorial을 참조합니다. 

PostgreSQL Tutorial - Learn PostgreSQL from Scratch

실습용 테이블에 대해서는 3강을 참조하세요.

3강 SQL이 뭐지? 도구들을 설치해보자. ( PostgreSQL, HeidiSQL )

---

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.