다중 테이블 쿼리

골머리·2021년 11월 10일

MySQL

목록 보기

7/16

조인

대부분의 쿼리는 2개 이상의 테이블이 필요한 경우가 많을거다.
1개 테이블에 모든 정보를 다 넣을수가 없기 때문이다.
이 경우, FOREIGN KEY를 연결수단으로 사용하여 2개 이상의 테이블의 열을 하나의 결과셋에 보여지게 할 수 있다.
이걸 조인 JOIN 이라고 한다.

데카르트 곱

FROM 절에 JOIN을 사용하여 두개의 테이블을 연결하는 방법으로 교차 조인 이라고 하지만 거의 사용하지 않는다.
예를 들어, 고객의 성, 이름, 주소를 한번에 보고 싶다고 하자.

데카르트 곱(교차조인) 사용 :

SELECT customer.first_name, customer.last_name, address.address
FROM customer 
JOIN address;
----------
361197 rows in set

교차 조인을 사용하면 고객 599명 * 주소 603개 = 361,197개의 조합을 생성하여 행으로 반환한다. 쿼리가 어떻게 두 테이블을 조인해야할지 몰라서 데카르트 곱으로 합쳤기 때문이다.

내부조인 : JOIN ... ON, INNER JOIN, USING

customer 테이블과 address 테이블을 살펴보자.

address_id를 이용하여 2개의 테이블을 조인해보자.

/*(방법1) FROM 절의 하위절에 JOIN ... ON 을 추가*/

SELECT c.first_name, c.last_name, a.address
FROM customer c 
JOIN address a 
ON c.address_id = a.address_id ;
  
/*(방법2) INNER JOIN ... ON 사용 => 추천! */

SELECT c.first_name, c.last_name, a.address
FROM customer c 
	INNER JOIN address a 
	ON c.address_id = a.address_id ;
  
/*(방법3) 두 테이블을 조인할 때, 열의 이름이 동일한 경우(address_id처럼)
-> ON 대신 USING 사용 가능*/

SELECT c.first_name, c.last_name, a.address
FROM customer c 
INNER JOIN address a 
USING (address_id);

ANSI 조인 문법

이 책에서 테이블 조인에 사용된 문법은 ANSI SQL 표준의 SQL92 버전이다.

ANSI 조인 문법의 장점

조인과 필터가 각각 다른절로 구분되어 보기 편하다.
조인 조건의 일부가 실수로 누락될 가능성이 낮다.
SQL92 조인 문법을 사용하는 쿼리는 표준화 되어 있어서, 주요 데이터베이스(오라클, MS, MySQL, IBM 등) 서버 간에 이식이 가능하다.

조인과 필터조건을 동시에 포함하는 쿼리를 살펴보자.

SELECT c.first_name, c.last_name, a.address
FROM customer c 
	INNER JOIN address a
	ON c.address_id = a.address_id
WHERE a.postal_code = 52137 ;
----------
2 rows in set

customer 테이블과 address 테이블을 합친뒤(조인), 우편번호가 52137인 고객만(필터) 결과셋에 나타난다.

3개 이상의 테이블 조인

3개 테이블을 조인할 때는 FROM 절에 3개의 테이블과 2개의 조인유형이 있고, 2개의 ON 하위절이 있게된다.
이번에는 고객의 주소가 아니라 '도시'를 반환해보자.

address_id를 사용해 customer 테이블과 address 테이블을 연결하고, city_id를 통해 address 테이블과 city 테이블을 연결해야한다.

SELECT c.first_name, c.last_name, ct.city
FROM customer c
	INNER JOIN address a 
	ON c.address_id = a.address_id 
	INNER JOIN city ct 
	ON a.city_id = ct.city_id ;

이때 FROM 절에서 테이블을 쓰는 순서는 중요하지 않다. (city 테이블을 먼저 써도 같은 결과)
왜냐하면 SQL은 비절차적 언어로, 데이터베이스 서버가 알아서 쿼리를 잘 실행하는 방법을 결정하기 때문이다.

서브쿼리 사용

서브쿼리를 사용해 address 테이블에서 district가 캘리포니아인 고객들의 성, 이름, 주소, 도시를 가져와보자.

SELECT 
	c.first_name
	, c.last_name
	, addr.address
	, addr.city
	, addr.district 
FROM customer c
	INNER JOIN 
	   (SELECT 
	  		a.address_id
	  		, a.address
	  		, ct.city
	  		, a.district
	    FROM address a
	    	INNER JOIN city ct
	    	ON a.city_id = ct.city_id
	    WHERE a.district = 'California'
	   ) addr 
	ON c.address_id = addr.address_id ;

테이블 재사용

film, film_actor, actor 3개의 테이블을 이용하여 2명의 특정 배우가 출연한 영화 제목을 모두 찾아보자.

SELECT 
	f.title
	, a.first_name 
	, a.last_name 
FROM film f
	INNER JOIN film_actor fa 
	ON f.film_id = fa.film_id 
	INNER JOIN actor a 
	ON fa.actor_id = a.actor_id 
WHERE 
	(
	(a.first_name = 'CATE' AND a.last_name = 'MCQUEEN')
	OR (a.first_name = 'CUBA' AND a.last_name = 'BIRCH')
	) ;
----------
54 rows in set

만약, Cate Mcqueen 과 Cuba Birch 가 모두 출연한 영화만 검색하고 싶다면 배우 각각을 검색할 수 있도록 쿼리를 짜야한다.

SELECT 
	f.title
FROM film f
	INNER JOIN film_actor fa1
	ON f.film_id = fa1.film_id 
	INNER JOIN actor a1
	ON fa1.actor_id = a1.actor_id 
	INNER JOIN film_actor fa2
	ON f.film_id = fa2.film_id
	INNER JOIN actor a2
	ON fa2.actor_id = a2.actor_id 
WHERE 
	(a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN')
	AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');
----------
2 rows in set