SQL | SubQuery

SubQuery

種類

1行サブクエリ

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

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'
;