[중급 쿼리] JOIN, Subquery, UNION, INTERSECT, EXCEPT

서브쿼리

MySQL에서는 ‘FROM 테이블명’에 서브쿼리를 입력하게 되면 반드시 alias(이름)을 지정해주어야 한다.

/* rental 테이블에서 고객별 평균 대여 횟수보다 많은 대여를 한
고객들의 이름( first_name , last_name )을 찾아주세요. */

SELECT first_name, last_name
FROM customer
WHERE customer_id IN ( -- JOIN쿼리
	SELECT customer_id
    FROM rental
    GROUP BY customer_id
    HAVING COUNT(*) > (
		SELECT AVG(rental_count) -- 전체 평균값(각 customer_id별 rental 횟수/customer_id 수)
        FROM (SELECT COUNT(*) AS rental_count -- 각 customer_id별 rental 횟수
			  FROM rental
              GROUP BY customer_id
              ) AS rental_counts -- FROM 서브쿼리이므로 ALIAS 지정 필수
		)
    );

 

 

상관서브

SELECT절 - SELECT절을 비교하는 쿼리

JOIN으로 대체되는 경우가 많기 때문에 정말 어려운 쿼리가 아닌 이상 쓰는 경우는 실제로 그렇게 많지 않은 것 같다.

SELECT P.customer_id, P.amount, P.payment_date
FROM payment P
WHERE P.amount > (
	SELECT AVG(amount)
	FROM payment
	WHERE customer_id = P.customer_id
	);

 

 

---

JOIN

• RIGHT/LEFT JOIN
• UNION(OUTER JOIN) : MySQL에는 OUTER JOIN이 없고, RIGHT/LEFT OUTER JOIN만 존재하기 때문에 OUTER JOIN이 필요할 때 UNION을 사용한다.
• CROSS JOIN : 두 테이블을 기준없이 전부 다 하나하나 합쳐서 ROW를 생성 ex) 5개의 행 테이블 + 10개의 행 테이블 —CROSS JOIN→ (5*10)50개의 행 테이블 생성
• SELF JOIN(INNER JOIN) : 테이블 1개를 스스로 JOIN *테이블의 alias(별명)을 반드시 지정해주어야 한다는 특이점 有

 

---

UNION, UNION ALL, INTERSECT, EXCEPT

두 개 이상의 SELECT 문의 결과를 결합 및 비교하는 집합 연산

1. UNION : 두 개 이상의 SELECT 문의 결과 집합을 결합하며, 중복된 행은 제거 각 SELECT 문의 열은 같은 순서를 가져야 하며, 유사한 데이터 유형을 가지고 있어야 합니다.
2. UNION ALL : 두 개 이상의 SELECT 문의 결과 집합을 결합하며, 중복된 행도 포함 UNION과 마찬가지로, 각 SELECT 문의 열은 같은 순서를 가져야 하며, 유사한 데이터 유형을 가지고 있어야 합니다.

• UNION 예제

# Write your MySQL query statement below
SELECT 'Low Salary' AS category,
       SUM(IF(income < 20000, 1, 0)) AS accounts_count
FROM accounts

UNION ALL

SELECT 'Average Salary' AS category,
       SUM(IF(income between 20000 and 50000, 1, 0)) AS accounts_count
FROM accounts

UNION ALL

SELECT 'High Salary' AS category,
       SUM(IF(income > 50000, 1, 0)) AS accounts_count
FROM accounts

1. INTERSECT : 두 개 이상의 SELECT 문의 결과 집합의 교집합을 반환 즉, 모든 SELECT 문에 공통적으로 있는 행을 반환합니다. UNION과 UNION ALL과 마찬가지로, 각 SELECT 문의 열은 같은 순서를 가져야 하며, 유사한 이터 유형을 가지고 있어야 합니다.
2. EXCEPT : 두 SELECT 문의 결과 집합의 차집합을 반환 즉, 첫 번째 결과 집합에는 있지만 두 번째 결과 집합에는 없는 행을 반환합니다. 각 SELECT 문의 열은 같은 순서를 가져야 하며, 유사한 데이터 유형을 가지고 있어야 합니다.

SELECT film_id FROM film
UNION, UNION ALL, INTERSECT, EXCEPT
SELECT film_id FROM inventory;

집합 연산을 사용할 때는 SELECT 문이 동일한 개수의 열을 선택해야 하며, 해당 열은 호환 가능한 데이터 유형을 가져야 하고, 열은 동일한 순서로 있어야 한다는 점을 기억할 것.

단, 모든 데이터베이스 시스템이 INTERSECT 와 EXCEPT 를 지원하지는 않음!