3.1.12 [DB] JOIN, SUBQUERY

JOIN

 

* ALIAS : SQL문의 컬럼 또는 테이블에 달아줄 수 있는 별칭

SELECT m.menu_code
	   , m.menu_name
       , m.menu_price
  FROM tbl_menu m;  -- as 생략 가능

 

• INNER JOIN

두 테이블의 교집합을 반환하는 SQL JOIN 유형으로, INNER JOIN에서 INNER 키워드는 생략이 가능하다.

-- ON을 활용한 JOIN
-- 컬럼명이 같거나 다를 경우 ON으로 서로 연관 있는 컬럼에 대한 조건을 작성
SELECT m.menu_code
	   , m.menu_name
       , c.category_name
FROM tbl_menu m
INNER JOIN tbl_category c ON m.category_code = c.category_code;

-- USING을 활용한 JOIN
-- 컬럼명이 같은 경우 USING으로 서로 연관 있는 컬럼에 대한 조건을 작성
SELECT m.menu_code
	   , m.menu_name
       , c.category_name
FROM tbl_menu m
JOIN tbl_category c USING (category_code);

 

• LEFT JOIN

첫 번째(왼쪽) 테이블의 모든 레코드와 두번째(오른쪽) 테이블에서 일치하는 레코드를 반환 

SELECT m.menu_code
	   , m.menu_name
       , c.category_name
FROM tbl_category c
LEFT JOIN tbl_menu m ON m.category_code = c.category_code;

 

• RIGHT JOIN

두 번째(오른쪽) 테이블의 모든 레코드와 첫 번째(왼쪽) 테이블에서 일치하는 레코드를 반환

SELECT m.menu_code
	   , m.menu_name
       , c.category_name
FROM tbl_category c
RIGHT JOIN tbl_menu m ON m.category_code = c.category_code;

 

• CROSS JOIN

두 테이블의 모든 가능한 조합을 반환

SELECT a.menu_name, b.category_name
FROM tbl_menu a
CROSS JOIN tbl_category b;

 

• SELF JOIN

같은 테이블 내에서 행과 행 사이의 관계를 찾기 위해 사용

SELECT a.category_name, b.category_name as '상위 카테고리명'
FROM tbl_category a
JOIN tbl_category b;

 

 

SUBQUERIES

 

다른 쿼리 내에서 실행되는 쿼리로, 서브쿼리의 결과를 활용해서 복잡한 메인쿼리를 작성해 한번에 여러 작업을 수행할 수 있다.

-- 메뉴명이 열무김치라떼인 메뉴의 카테고리와 동일한 카테고리의 메뉴 정보 조회
SELECT menu_code
	   , menu_name
       , menu_price
       , category_code
       , orderable_status
FROM tbl_menu
WHERE category_code = (SELECT category_code
						FROM tbl_menu
						WHERE menu_name = '열무김치라떼');

 

FROM 절에 쓰인 서브쿼리(파생 테이블)는 반드시 자신의 별칭이 있어야 한다.

-- 가장 많은 메뉴가 포함된 카테고리의 메뉴 개수 조회
SELECT MAX(count)
FROM (SELECT COUNT(*) AS 'count'
		FROM tbl_menu
		GROUP BY category_code) AS countmenu;

 

• 상관 서브쿼리

메인 쿼리가 서브쿼리의 결과에 영향을 주는 경우 상관 서브쿼리라고 한다.

SELECT menu_code
	   , menu_name
       , menu_price
       , category_code
       , orderable_status
FROM tbl_menu a
WHERE menu_price > (SELECT AVG(menu_price)
					FROM tbl_menu
                    WHERE category_code = a.category_code
                    GROUP BY category_code);

 

• EXISTS

조회 결과가 있을 때 true 아니면 false

-- EXISTS와 서브쿼리를 활용한 메뉴가 있는 카테고리 조회
SELECT category_name
FROM tbl_category a
WHERE EXISTS(
				SELECT 1
                FROM tbl_menu b
                WHERE b.category_code = a.category_code
			);

 

 

SET_OPERATORS

 

SET 연산은 두 개 이상의 SELECT문의 결과 집합을 결합하는 데 사용한다.

SET 연산을 통해 결합하는 결과 집합의 컬럼이 동일해야 한다.

• UNION (합집합)

두 개 이상의 SELECT 문의 결과를 결합하여 중복된 레코드를 제거한 후 반환

SELECT menu_code, menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code = 10
UNION
SELECT menu_code, menu_name, menu_price, category_code
FROM tbl_menu
WHERE menu_price < 9000;

 

• UNION ALL (합집합 + 교집합)

두 개 이상의 SELECT 문의 결과를 결합하며, 중복된 레코드를 제거하지 않고 모두 반환

SELECT menu_code, menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code = 10
UNION ALL
SELECT menu_code, menu_name, menu_price, category_code
FROM tbl_menu
WHERE menu_price < 9000;

 

• INTERSECT (교집합)

두 SELECT 문의 결과 중 공통되는 레코드만을 반환 

MYSQL은 INTERSECT를 제공하지 않음. 단, INNER JOIN 또는 IN을 활용한 구현 가능

-- INNER JOIN 활용
SELECT a.menu_code, a.menu_name, a.menu_price, a.category_code
FROM tbl_menu a
JOIN (
		SELECT menu_code, menu_name, menu_price, category_code
		FROM tbl_menu
		WHERE menu_price < 9000
	 ) b ON a.menu_code = b.menu_code
WHERE a.category_code = 10;

-- IN 활용
SELECT menu_code, menu_name, menu_price, category_code
FROM tbl_menu
WHERE category_code = 10
AND menu_code IN (SELECT menu_code
					FROM tbl_menu
					WHERE menu_price < 9000);

 

• MINUS (차집합)

첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과가 포함하는 레코드를 제외

MySQL은 MINUS를 제공하지 않음. 단, LEFT JOIN을 활용한 구현 가능

SELECT a.menu_code, a.menu_name, a.menu_price, a.category_code
FROM tbl_menu a
LEFT JOIN (
			SELECT menu_code, menu_name, menu_price, category_code
			FROM tbl_menu
			WHERE menu_price < 9000
		  ) b ON a.menu_code = b.menu_code
WHERE a.category_code = 10
AND b.menu_code IS NULL;