One To Many & Joins (일대다)
1 : MANY
해당 이미지는 Orders TABLE이 Customers TABLE에 있는 customer_id 컬럼을 참조하는 것을 보여주는 이미지다.
customer_id 속성만 참조하여 ORDERS TABLE을 중복없이 관리할 수 있다.
주의할 점
- 기본키(Primary Key)는 반드시 고유해야 한다.
- 바로 위 이미지에서 기본키는
customer_id와order_id이다.
- 외래키
- 외래키는
어떤 테이블 안에 있는 다른 테이블을 참조하는 키 - 바로 위 이미지에서 외래키는
ORDERS테이블의customer_id이다.
CUSTOMERS테이블의id를 참조하기 때문이다.
외래키 다루기
FOREIGN KEY(적용할 키 값) REFERENCES <참조할 테이블>(참조할 컬럼)
테이블 2개를 생성해보자.
CREATE TABLE customers(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(20) NOT NULL,
email VARCHAR(20) NOT NULL
);
CREATE TABLE orders(
orders_id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount FLOAT NOT NULL,
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);
-- FOREIGN KEY(customer_id) REFERENCES customers(customer_id) - 외래키 적용
-- customer_id값을 외래키로 사용할 거고, 이 값은 customers 테이블에 있는 customer_id 컬럼을 참조한다.만약
INSERT INTO orders(order_date, amount, customer_id) VALUES(CURDATE(), 11.5, 975);외래키 설정을 해놓은 컬럼에 데이터를 임의로 추가하려하면 다음과 같은 에러를 마주할 수 있다.
Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
a foreign key constraint fails: 외래키 제약조건 실패
!!주의!!
모든 ID를 거절하는 것이 아니다.
customers_id에 있는 값이면 입력이 가능하다.
여기서 주의할 점은 주문하지 않은 고객의customers_id은 orders 테이블에 있는 customers_id 외래키에 적용할 수 있다는 부분이다.
크로스 조인 (Cross Join)
A 테이블의 모든 행을 B 테이블의 모든 행과 join 시킨다.
즉, 모든 조합이 가능해진다.
-- SELECT * FROM customers WHERE last_name = "George";
-- SELECT * FROM orders WHERE customer_id = 1;
-- SELECT * FROM orders WHERE customer_id = (SELECT customer_id FROM customers WHERE last_name = "George");
SELECT * FROM customers, orders;두 개의 테이블의 컬럼이 모두 출력된다.
두 개 테이블의 모든 정보를 조합한다.
(잘 쓰이지 않음)
이너 조인 (Inner Join)
대표 조인 세 가지 중 하나.
INNER JOIN, LEFT JOIN, RIGHT JOIN
A 테이블과 B 테이블의 공통되는 부분만 추출한다.
⭐️겹치는 부분만 가져온다
-- INNER JOIN
SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
-- customers 테이블을 orders 테이블과 결합할거다.
-- customers 테이블에 있는 customer_id와 orders 테이블에 있는 customer_id가 같은 것만 출력해라.
-- 보기 좋게 정리
SELECT CONCAT(first_name, " ", last_name) as name, order_date, amount FROM customers JOIN orders ON customers.customer_id = orders.customer_id;GROUP BY와 함께 사용
SELECT
first_name,
last_name,
SUM(amount) AS 'Total'
FROM
customers
JOIN orders ON orders.customer_id = customers.customer_id
GROUP BY
first_name,
last_name
ORDER BY
'Total' DESC;LEFT JOIN
(A 테이블 기준으로 진행)
A 테이블 행 모두를 가져온다.
왼쪽은 모든 것을 가져오고, 오른쪽은 겹치는 것만 가져온다.
SELECT first_name, last_name, order_date, amount FROM customers
LEFT JOIN orders ON orders.customer_id = customers.customer_id;해당 코드에서 왼쪽을 담당하는 것이
SELECT first_name, last_name, order_date, amount FROM customers
즉, customers 테이블
오른쪽을 담당하는 것이
LEFT JOIN orders ON orders.customer_id = customers.customer_id;
즉, orders 테이블
위에 적어놓은 말을 적용시켜보면
customers테이블에 있는 것은 모두 가져오고,
orders테이블에 있는건 겹치는 것만 가져와라
- 출력 결과
customers테이블을 모두 가져왔기 때문에 모든 고객이 출력되었다.
그리고NULL이 있는 것을 볼 수 있는데,
일치하는 정보가 없기 때문에NULL이 출력된다.
➡️ 주문 내역이 없는 고객임을 알 수 있다.
LEFT JOIN을 이용해야 하는 대표적인 예시 상황은 다음과 같다.
- 한 번도 주문한 적이 없는 비활성 회원 정보를 알고싶을 때
- 게시물과 댓글 데이터 조회
- 부서와 직원 데이터 조회
!!알고 넘어가자!!
테이블 순서를 변경하면 출력되는 결과가 달라질 수 있다.
IFNULL
IFNULL(일반적인 함수 및 값, Null일 때, 표현하고 싶은 형식)
IFNULL(SUM(amount), 0) -- SUM(amount)해서 나온 값중에, Null이 있다면 그 값은 0으로 표현하라.
LEFT JOIN과 GROUP BY
사용 예시
SELECT CONCAT(first_name, " ", last_name) AS user, IFNULL(SUM(amount), 0) AS money_spent FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id GROUP BY user;RIGHT JOIN
왼쪽과 오른쪽 테이블을 조인할 때,
RIGHT JOIN을 사용하면,
오른쪽에서 모든 행을 가져오고 왼쪽에서는 겹치는 부분만 가져온다.
-- 예시코드
SELECT
first_name, last_name, order_date, amount
FROM
customers
RIGHT JOIN
orders ON customers.id = orders.customer_id;
-- orders 테이블에 있는 것을 모두 가져오고, customers 테이블에서는 customers.id와 orders.customer_id 조건이 맞는 것을 불러와라.