두 테이블을 조인해서 각 영화의 대여 가능한 사본 수
958개의 행을 반환한다.
쿼리가 조인조건을 충족하는 행만 반환하도록 했기 때문.
inventory테이블에 행이 있는지 없는지와 관계없이 1000개의 영화 모두 반환하도록 하려면
-
inner에서 left outer로 변경.
조인이 성공하면 오른쪽에 있는 열도 포함. -
count(*)에서 count(i.inventory_id)로 변경되었기 때문에 inventory.inventory_id의 열이 null이 아닌 것만 카운팅
group by 절 제거 후 필터링
조인 조건이 실패한 경우 null이 된다.
10.1.1 왼쪽 vs 오른쪽 외부 조인
오른쪽에 있는 테이블이 결과셋의 행 수를 결정하는 반면, 왼쪽에 있는 테이블은 열 값을 제공하는데 사용됨.
오른쪽 외부조인은 거의 발생하지 않으며, 항상 왼쪽 외부 조인을 사용하는게 좋습니다.
outer는 선택 사항으로A left join B를 사용해도 되지만, 명확성을 위해outer를 붙이길 권장
10.1.2 3방향 외부 조인
세 개의 테이블을 외부 조인하는 것을 3방향 외부 조인이라고 한다.
10.2 교차조인
조인 조건을 지정하지 않고 조인하면 데카르트 곱이 생성되나, 그리 일반적으로 사용하지 않음.
데카르트 곱을 생성하려 한다면 교차조인을 지정
16 * 6 개의 행 = 96개의 행을 반환.
0부터 399까지 숫자만들기로 윤달이 포함된 날짜 만들기
사용자의 개입 없이 윤일(2월 29일)을 자동으로 포함.
SELECT DATE_ADD('2020-01-01', INTERVAL (ones.num+ tens.num + hundreds.num) DAY) dt
FROM
(SELECT 0 num UNION ALL
SELECT 1 num UNION ALL
SELECT 2 num UNION ALL
SELECT 3 num UNION ALL
SELECT 4 num UNION ALL
SELECT 5 num UNION ALL
SELECT 6 num UNION ALL
SELECT 7 num UNION ALL
SELECT 8 num UNION ALL
SELECT 9 num) ones
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 10 num UNION ALL
SELECT 20 num UNION ALL
SELECT 30 num UNION ALL
SELECT 40 num UNION ALL
SELECT 50 num UNION ALL
SELECT 60 num UNION ALL
SELECT 70 num UNION ALL
SELECT 80 num UNION ALL
SELECT 90 num) tens
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 100 num UNION ALL
SELECT 200 num UNION ALL
SELECT 300 num) hundreds
WHERE DATE_ADD('2020-01-01', INTERVAL (ones.num+ tens.num + hundreds.num) DAY) < '2021-01-01'
ORDER BY 1;
05년의 일별 영화 대여횟수를 함께 표기하는 보고서 (대여하지 않은 날을 비롯해 그 해의 모든 날 표기해야함.)
교차조인, 외부조인, 날짜함수, 그룹화, 집합 연산과 집계함수를 모두 포함하고 있다.
최선의 해결책은 아니지만, 교차조인처럼 거의 사용되지 않는 기능도 사용 가능하다.
10.3 자연 조인
조인 조건 r.customer_id = c.customer_id를 추가하여 두 테이블을 조인한다.
SELECT f.film_id, f.title, COUNT(*) num_copies
FROM film f
INNER JOIN inventory i
ON f.film_id = i.film_id
GROUP BY f.film_id, f.title;
SELECT f.film_id, f.title, COUNT(i.inventory_id) num_copies
FROM film f
LEFT OUTER JOIN inventory i
ON f.film_id = i.film_id
GROUP BY f.film_id, f.title;
SELECT f.film_id, f.title, i.inventory_id
FROM film f
RIGHT OUTER JOIN inventory i
ON f.film_id = i.film_id
WHERE f.film_id BETWEEN 13 AND 15;
SELECT f.film_id, f.title, i.inventory_id, r.rental_date
FROM film f
LEFT OUTER JOIN inventory i
ON f.film_id = i.film_id
LEFT OUTER JOIN rental r
ON i.inventory_id = r.inventory_id
WHERE f.film_id BETWEEN 13 AND 15;
## 10.2 교차조인
SELECT c.name category_name, l.name language_name
FROM category c
CROSS JOIN LANGUAGE l;
SELECT days.dt, COUNT(r.rental_id) num_rentals FROM rental r
RIGHT OUTER JOIN(
SELECT DATE_ADD('2005-01-01', INTERVAL (ones.num+ tens.num + hundreds.num) DAY) dt
FROM
(SELECT 0 num UNION ALL
SELECT 1 num UNION ALL
SELECT 2 num UNION ALL
SELECT 3 num UNION ALL
SELECT 4 num UNION ALL
SELECT 5 num UNION ALL
SELECT 6 num UNION ALL
SELECT 7 num UNION ALL
SELECT 8 num UNION ALL
SELECT 9 num) ones
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 10 num UNION ALL
SELECT 20 num UNION ALL
SELECT 30 num UNION ALL
SELECT 40 num UNION ALL
SELECT 50 num UNION ALL
SELECT 60 num UNION ALL
SELECT 70 num UNION ALL
SELECT 80 num UNION ALL
SELECT 90 num) tens
CROSS JOIN
(SELECT 0 num UNION ALL
SELECT 100 num UNION ALL
SELECT 200 num UNION ALL
SELECT 300 num) hundreds
WHERE DATE_ADD('2005-01-01', INTERVAL (ones.num+ tens.num + hundreds.num) DAY) < '2006-01-01'
ORDER BY 1) days
ON days.dt = DATE(r.rental_date)
GROUP BY days.dt
ORDER BY 1;
## 10.3 자연 조인
SELECT c.first_name, c.last_name, DATE(r.rental_date)
FROM (SELECT customer_id, first_name, last_name FROM customer) c
NATURAL JOIN rental r;
## 10.4 실습
SELECT * FROM customer;
SELECT c.last_name, SUM(p.amount)
FROM customer c
LEFT OUTER JOIN payment p
ON c.customer_id=p.customer_id
GROUP BY c.customer_id;
