SQL: 서브쿼리 (+WITH)

1. 스칼라 서브쿼리 : 스칼라 서브쿼리 쓸때는 하나의 값만 나오도록 select 절을 작성해줘야한다는 점! 명심하기

-- 이렇게 서브쿼리를 통해서 조인한 결과를 한번에 보여주기
select a.id, a.name, 
      (select b.name from grade_info b where b.code = a.grade_code) as grade
  from customers a;
  
-- 여러 다른 테이블에서 데이터 가져오기
select 
	(select count(*) from country) as tco
    ,(select count(*) from city) as tci
    , (select count(distinct language) from countrylanguage) as tl
from dual;

 

2.인라인뷰: from 절에 쓰는 서브쿼리 - 가장 흔히 사용하는 서브쿼리

쓰는 이유: 테이블 전체 테이블을 쓰지않고 일부 테이블만 가져와서 조인할때 사용

from 에 들어가는거니까, from 에서 인라인뷰를 통해 미리 필요한 정보만 취한 후에, 조인하는 거라 메모리 사용이 줄어듦.

select g.group_name, g.debut, m.youngest
from idol_group g,
     (select group_name, max(birthdate) youngest  --group_name 이 select 에 없으면 조인이 어려움. 최종 사용할 열이 아니더라도 절대 빼먹으면 안됨
        from idol_member 
        group by group_name) m
 where m.group_name = g.group_name;
 
 --추가예시 (원래 > 인라인뷰로 변경)
SELECT co.code, co.name, ci.name, ci.population
FROM country co
JOIN city ci
ON co.code = ci.countrycode AND ci.population >= 800 * 10000;

--변경후
SELECT co.code, co.name, ci.name, ci.population
FROM country co
JOIN (SELECT * FROM city WHERE population >= 800 * 10000) ci
ON co.code = ci.countrycode;

-- 인라인뷰도 여러개 사용가능 but 너무 많아지면 가독성이 떨어지니 조심해서 사용

-- 여러개 사용가능 but 너무 많아지면 가독성이 떨어지니 조심해서 사용

-- 테이블로 존재하지않는 가상의 데이터를 인라인뷰로 만들어서 조인시켜줄 때 유용하게 사용할 수 있음
select tmp.hello, m.name
from (select '안녕' as hello from dual) tmp,   -- 가상의 데이터 
     idol_member m
     
     --예, 텍스트데이터보다 연도 데이터를 인라인뷰로 만들어서 집계되도록 하는 방법을 쓰는 경우가 종종있음

 

3. WHERE 절 서브쿼리 (중첩 서브쿼리, having 절에도 사용가)

-- 한국의 인구수보다 인구수가 많은 나라의 여러 데이터 뽑기
SELECT code, name, population
FROM COUNTRY
where population > (select POPULATION from COUNTRY where code = 'KOR');

 

4. UNION

-- 문제: 인구수가 많은 도시 3개와 인구수가 적은 도시 3개를 출력

SELECT countrycode, name, population
FROM city
ORDER BY population DESC
LIMIT 3
UNION
SELECT countrycode, name, population
FROM city
ORDER BY population 
LIMIT 3;
-- 하면 오류남.order by와 limit을 쓰고나면 그 아래에는 union을 쓸수없음

--수정
SELECT *
FROM ( SELECT countrycode, name, population
	   FROM city
	   ORDER BY population DESC
	   LIMIT 3 ) sub1    --alias 꼭 써주기
UNION
SELECT countrycode, name, population
FROM ( SELECT countrycode, name, population
	   FROM city
	   ORDER BY population ASC
	   LIMIT 3 ) sub2;
       
-- 이렇게는 가능함
SELECT code from country
union all
SELECT countrycode as code FROM city -- 여기까지 계산 완료한 후 
ORDER BY code         -- 계산 된 결과를 여기서 정렬하는 것은 가능함(단 위에 합친 열 이름이 같아야함)
LIMIT 5;