customer 테이블
orders 테이블
JOIN
- 두 테이블을 묶어서 하나의 테이블을 만듦
- 왜? 두 테이블을 엮어야 원하는 형태가 나오기도 함
- 사용하려면 두 테이블이 PK-FK 관계여야 한다.
- 조인조건은 PK-FK관계를 쓴다고 생각하자
- SELECT 속성이름, ... FROM 테이블A, 테이블B WHERE 조인조건 AND 검색조건;
- SELECT 속성이름, ... FROM 테이블A INNER JOIN 테이블B ON 조인조건 WHERE 검색조건;
- 둘 중 편한걸 사용하지만, 보통은 JOIN문을 사용한다. Ex)
- WHERE 절을 이용해 조인 조건을 추가
- 참고) [테이블이름.테이블명] 표기: 특정 테이블의 열을 가르킴
SELECT * FROM customer, orders WHERE customer.custid = orders.custid;
SELECT * FROM customer INNER JOIN orders ON customer.custid = orders.custid;
SELECT * FROM customer JOIN orders ON customer.custid = orders.custid; -- INNER 생략 가능
-- custid 순서로 정렬
SELECT * FROM customer, orders WHERE customer.custid = orders.custid ORDER BY customer.custid; -- customer 테이블의 custid순으로 정렬
SELECT * FROM customer INNER JOIN orders ON customer.custid = orders.custid ORDER BY orders.custid; -- orders 테이블의 orderid 순으로 정렬
customer 테이블의 custid와 orders 테이블의 custid가 일치하는 투플 출력
고객별로 주문한 제품 총 구매액을 고객별로 정렬 (고객별로 주문한 제품 총 구매액 조회)
실행 결과(고객이름, 총 구매액)
SELECT customer.custname AS '이름', SUM(price * amount) AS '총 주문금액'
FROM customer, orders
WHERE customer.custid = orders.custid
GROUP BY custname
ORDER BY custname;
SELECT customer.custname AS '이름', SUM(price * amount) AS '총 주문금액'
FROM customer INNER JOIN orders -- customer와 orders는 순서가 바뀌어도 상관없음.
ON customer.custid = orders.custid
GROUP BY custname
ORDER BY custname;
서브 쿼리, 부속 질의
- SQL문 내에 또 다른 SQL문 작성
- SELECT문의 WHERE절에 또 다른 테이블 경과를 이용해서 다시 SELECT 문을 괄호로 묶는다!
=> SELECT ~ FROM ~ WHERE (SELECT ~ FROM ~~);STEP 1. 주문 금액이 가장 큰 주문 내역은 무엇인가?
SELECT MAX(price * amount) FROM orders;
번외) 위 STEP 1을 잘못 이해하고 모든 주문의 금액 합이 가장 큰 사람을 찾는 코드를 만들었다.
그냥 묻기에는 아까워서 올린다.
SELECT customer.custname AS '이름', SUM(price * amount) AS '총 주문금액'
FROM customer, orders
WHERE customer.custid = orders.custid
GROUP BY custname
ORDER BY SUM(price * amount) DESC LIMIT 1;
STEP 2. 가장 큰 주문 금액(10000원)에 대한 주문아이디, 고객아이디, 상품명 조회
SELECT orderid, custid, prodname FROM orders WHERE price * amount = 10000;
STEP 3. 서브 쿼리를 이용해 두 SQL문을 하나로 합치기
SELECT orderid, custid, prodname FROM orders
WHERE price * amount = (SELECT MAX(price * amount) FROM orders);
평균 주문 금액 이상인 사람 조회
SELECT orderid, custid, prodname FROM orders
WHERE price * amount > (SELECT AVG(price * amount) FROM orders);
SELECT custname FROM customer WHERE custid IN (SELECT custid FROM orders);
후기
SELECT문을 처음 배울 때만큼 난이도가 좀 있었다.
JOIN의 ON 뒤에는 꼭 기본키와 외래키를 사용한 조건이 나온다는 것을 잊지말자! 오늘 실습 문제를 풀 때 거기서 헷갈려서 애를 먹었었다.
