[MySql] DQL SELECT문에서 subquery 활용법

Query 안에 또 다른 query를 넣는 것을 subquery라고 한다.
예를 들어, SELECT 문 안에 중첩되어 SELECT 문이 들어가는 경우.
 
SELECT 문은 크게 보았을 때, SELECT의 대상이 되는 칼럼 (field)와 FROM 혹은 JOIN 뒤에 붙는 테이블이 있다.
이에 따라 subquery 사용 경우의 수를 두 가지로 나눠 볼 수 있다.
 
1. SELECT (SELECT ~ FROM~) FROM ~
Subquery를 칼럼 중 하나로 넣어 줄 때에는 Subquery로 SELECT한 칼럼은 1개여야 하고 subquery안에 바깥 액자 역할을 하는 table과 연결 (link) 될 수 있도록 하는 where문을 넣어줘야 한다.
 
예시는 아래와 같음.

SELECT 
    m.id movie_id,
    m.title title,
    m.rate rate,
    m.open_date open_date,
    d.business_name distributor_name,
    (
        SELECT 
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    "actorId", a.id,
                    "name", a.name
                )
            ) 
        FROM actors a
        JOIN movies_actors ma ON a.id = ma.actor_id
        WHERE ma.movie_id = m.id
        GROUP BY ma.movie_id
    ) actor_list
FROM movies m
JOIN distributors d ON d.id = m.distributor_id
WHERE d.business_name = "Nolan Unlimited" AND m.rate = (SELECT MAX(rate) FROM movies)
GROUP BY m.id

 
2. SELECT ~ FROM ~ JOIN (SELECT ~ FROM~)
 
JOIN 뒤에 들어가는 경우 SELECT문으로 새로운 table을 생성하여 그대로 다른 table에 이어붙인다고 생각하면 되므로,
SELECT 대상으로 들어갈 때보다 쉽게 이해될 것 같다.
 
예시는 아래와 같음.

SELECT 
 m.id movie_id,
 m.title title,
 m.rate rate,
 m.open_date open_date,
 d.business_name distributor_name,
 actors.actorList actor_list
FROM movies m
JOIN distributors d ON d.id = m.distributor_id
JOIN (
	SELECT 
		ma.movie_id,
		JSON_ARRAYAGG(
			JSON_OBJECT(
				"actorId", a.id,
				"name", a.name
			)
		) actorList
	FROM actors a
	JOIN movies_actors ma ON a.id = ma.actor_id
	GROUP BY ma.movie_id
) actors ON actors.movie_id = m.id
WHERE d.business_name = "Nolan Unlimited" AND m.rate = (SELECT MAX(rate) FROM movies)
GROUP BY m.id