SQL - JOIN

1. CROSS JOIN (교차결합)

• 집합에서는 곱집합으로, 두 테이블의 데이터가 결합할 수 있는 모든 경우의 수를 계산한다.

• 비용이 많이 드는 연산이고, 실무에서는 필요한 경우가 거의 없어 사용되지 않는다.

• {user1, user2, user3, user4} 집합과 {post1, post2, post3, post4, post5} 집합을 교차 결합시 결과

(user1, post1), (user1, post2), (user1, post3), (user1, post4), (user1, post5)
(user2, post1), (user2, post2), (user2, post3), (user2, post4), (user2, post5)
(user3, post1), (user3, post2), (user3, post3), (user3, post4), (user3, post5)
(user4, post1), (user4, post2), (user4, post3), (user4, post4), (user4, post5)
(user5, post1), (user5, post2), (user5, post3), (user5, post4), (user5, post5)

2. INNER JOIN (내부결합)

• 집합에서는 교집합으로, 기준이 되는 테이블과 JOIN 하는 테이블 양쪽 모두에 결합조건이 맞는 row만 검색하는 방법이다.
• 곱집합 + 결합조건 을 더해서 검색하는 방법이다. 사용 방법은 다음과 같다.

SELECT * 
FROM table_1 
INNER JOIN table_2 ON search_condition

• 따라서 다음과 같은 방법으로 posts 테이블과 users 테이블을 결합하여 posts의 user_id 값과 users의 id 값이 일치하는 정보만 조회할 수 있다.

 SELECT 
	posts.id,
	posts.content, 
	users.name
FROM posts 
INNER JOIN users ON posts.user_id = users.id;

3.OUTER JOIN (외부결합)

• 외부 결합에는 LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN 이 있다.

• 결합하는 테이블에서 한 쪽에는 데이터가 있고 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블을 기준으로 모두 출력하는 결합 방법이다.

1) LEFT (OUTER) JOIN

• 기준 테이블 (left table)의 모든 row와, join이 걸리는 테이블(right table) 중에서 left table과 매칭되는 row만 검색한다.
• 기준 테이블(left table) 쪽에만 존재하는 row는 모두 가져오면서, join이 걸리는 테이블에서 매칭되는 결과가 없는 경우에는 빈 값(NULL)로 표시한다.

SELECT 
	users.id, 
	users.name, 
	users.job_id, 
	jobs.job 
FROM users LEFT JOIN jobs ON users.job_id = jobs.id

2) RIGHT (OUTER) JOIN

• join이 걸리는 테이블(right table)의 모든 row와 기준 테이블 (left table)에서 right table과 matching되는 row만 검색하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.

SELECT 
	users.id, 
	users.name, 
	users.job_id, 
	jobs.job 
FROM users 
RIGHT JOIN jobs ON users.job_id = jobs.id;

3) FULL (OUTER) JOIN

• LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 결합 방법이다.
  기준이 되는 테이블(left table)과 join이 걸리는 테이블(right table) 양쪽 모두의 row를 검색하고 빈 값은 NULL 로 표시한다.
• MySQL에서는 FULL OUTER JOIN이라는 키워드를 지원하지 않기에, 아래와 같이 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION 하는 방식으로 FULL OUTER JOIN을 구현한다.

SELECT 
	users.id, 
	users.name, 
	users.job_id, 
	jobs.job 
FROM users LEFT JOIN jobs ON users.job_id = jobs.id
UNION
SELECT 
	users.id, 
	users.name, 
	users.job_id, 
	jobs.job 
FROM users RIGHT JOIN jobs ON users.job_id = jobs.id;

Assignment 3 | SQL Advanced (선택)

과제 1. 
MySQL의DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해주세요. ‘2022-03-01’ 이후에 개봉한 영화들의 기본 정보와 해당 영화의 배급사의 정보를 다음과 같은 형태로 출력해주세요.

과제 1 풀이

• INNER JOIN, WHERE 조건을 활용했다.

SELECT
	movies.id AS movie_id,
	movies.title,
	movies.rate,
	movies.open_date,
	distributors.business_name AS distributor_name
FROM movies 
INNER JOIN distributors ON distributors.id = movies.distributor_id
where open_date >= '2022-03-01';

과제 2. 
페이지 하단 참고자료의 SQL 키워드들과 MySQL의DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해주세요. 특정 에이전시 회사(Agencyfluent)의 소속 배우들 정보와 해당 배우들이 출연한 영화 목록을 다음과 같은 형태로 출력해주세요.

과제 2 풀이

• INNER JOIN으로 actors, movies, movies_actors, agencies 를 결합하고 GROUP BY와 JSON_ARRAYAGG 함수로 한 줄에 여러 데이터를 표시했다.

SELECT
	A.id AS actor_id,
	A.name AS actor_name,
	JSON_ARRAYAGG(M.title) AS filmography
FROM actors A
INNER JOIN movies_actors MA ON A.id = MA.actor_id
INNER JOIN movies M ON M.id = MA.movie_id
INNER JOIN agencies AG ON A.agency_id = AG.id
WHERE AG.business_name = 'Agencyfluent'
GROUP BY A.id;

과제 3.
페이지 하단 참고자료의 SQL 키워드들과 MySQL의DQL(Data Query Language)를 사용하여, 아래 결과가 출력될 수 있는 쿼리문을 작성해주세요. 특정 배급사(Nolan Unlimited)의 영화 중 가장 높은 평점을 받은 영화의 모든 정보와 출연 배우 목록을 다음과 같은 형태로 출력해주세요.

과제 3 풀이

• 발상 : movies, movies-actors, distributors, actors 4개 테이블을 여러 번의 INNER JOIN을 활용해 결합했다.
• INNER JOIN 안에서 서브쿼리를 통해 actors 테이블 안의 내용을 별도 JSON OBJECT로 가져왔다.
• MAX()를 WHERE절에 활용해 가장 높은 값을 가져왔다.
• GROUP BY와 JSON_ARRAYAGG을 이용했다.

SELECT
  movies.title,
  movies.rate,
  movies.open_date,
  distributors.business_name AS distributor_name,
  JSON_ARRAYAGG(actor_list_json.actor_nameid) AS actor_list
FROM movies 
INNER JOIN movies_actors ON movies.id = movies_actors.movie_id
INNER JOIN distributors ON distributors.id = movies.distributor_id
INNER JOIN (
  SELECT
  A.id,
  JSON_OBJECT ('name', A.name,'actorId', A.id) AS actor_nameid
  FROM actors A
) actor_list_json ON actor_list_json.id = movies_actors.actor_id
WHERE distributors.business_name = 'Nolan Unlimited' AND rate = (SELECT MAX(M.rate) FROM movies M)
GROUP BY movies.title, movies.rate, movies.open_date, distributor_name;