SQL | SubQuery

SubQuery

Note:

• 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 서브쿼리는 메인쿼리를 포함한 종속적인 관계이다.

종류

단일행 서브 쿼리

select id, create_date, update_date, product_count, product_title, work_id, category_set_id
from v_app_product
where category_set_id = (
    SELECT id
    FROM v_category
    where title = '여성의류'
)
;

다중행 서브 쿼리

select id, create_date, update_date, product_count, product_title, work_id, category_set_id
from v_app_product
where category_set_id in (
    SELECT id
    FROM v_category
    where title = '여성의류'
       or title = '가디건'
)
;

다중컬럼 서브쿼리

select id, create_date, update_date, product_count, product_title, work_id, category_set_id
from v_app_product
where (category_set_id, product_count) in (
    SELECT id, product_count
    FROM v_category
    where title = '여성의류'
       or title = '가디건'
)
;

---

위치에 따른 서브쿼리

서브쿼리는 사용한 곳은 한정적이다.

• SELECT
• FROM
• WHERE
• HAVING
• INSERT
• UPDATE

Warning:
서브쿼리르 괄호로 감싸준다
서브쿼리는 단일행 / 복수행 비교연산자와 함께 사용 가능
서브쿼리에서는 ORDER BY 를 사용 못한다.

SELECT

SELECT id,
       create_date,
       update_date,
       product_count,
       product_title,
       work_id,
       (
           SELECT COUNT(*)
           FROM v_category
           where title = '여성의류'
              or title = '가디건'
       ) as category_cnt
FROM v_app_product
;

FROM

SELECT A.work_id, A.title
FROM (SELECT *
      FROM v_category
      where title = '여성의류'
         or title = '가디건'
     ) as A
;

WHERE

select id, create_date, update_date, product_count, product_title, work_id, category_set_id
from v_app_product
where category_set_id = (
    SELECT id
    FROM v_category
    where title = '여성의류'
)
;

Having

select gender, sum(count)
from v_app_user
GROUP BY gender
having sum(count) >
       (
           SELECT 2000
           FROM dual
       )
;

Insert

insert
app_product (create_date, update_date, product_count, product_title, work_id, category_set_id)
    value (now(), now(), 100, 'test1', 'system',
           (
               select id
               from category
               where title = '자켓'
           )
    )
;

Update

update app_product
set update_date     = now(),
    category_set_id = (
        select id
        from category
        where title = '조끼'
    )
where product_title = 'test1'
;