[MySQL] Join 알아보기 + 집합 연산 + 서브쿼리

SQL 공부할 때 Join이 들어가면 뭔가 헷갈렸었다.. 아마 지금도? 머쓱.. 뭐 써야지 하고 생각만 하고 모아놨더니 쓸게 한 바가지네..

출처 - [MYSQL] 📚 테이블 조인(JOIN) - 그림으로 알기 쉽게 정리 (tistory.com)

 

냅다 그림 투척 이것만 봐도 뭔지 대충은 알겠다..! 하나하나 알아보자.

 

먼저 예시 테이블 a1과 b1이다.

a1 테이블

b1 테이블

1. JOIN ( == INNER JOIN == CROSS JOIN) 

 

보통 a1과 b1에서 일치하는 key의 값을 가져올 때 어떻게 하냐?

SELECT a.key FROM a1 a, b1 b WHERE a.key = b.key;

 

이렇게 하죠 간단하게? 이건 사실 INNER JOIN이 숨어있다..!

SELECT a.key FROM a1 as a INNER JOIN b1 as b ON a.key = b.key;

 

이렇게 말이다..! 두둥 결과는 똑같다. 그럼 뭐다? JOIN은? ON 절의 조건이 일치하는 값을 JOIN 한다! INNER JOIN은 그림 보면 알겠지만 공통된 부분을 나타낸다.

 

2. LEFT (OUTER) JOIN / RIGHT (OUTER)  JOIN

 

2개의 테이블을 JOIN 할 때 왼쪽 테이블이 기준이냐 오른쪽 테이블이 기준이냐에 따라 결과가 달라진다. 기준이 되는 테이블의 데이터는 모두 출력된다.

SELECT * FROM a1 as a LEFT JOIN b1 as b ON a.key = b.key;

위 쿼리 결과

a1 테이블의 행은 모두 나왔는데 b1에 있던 key가 3인 행은 나오지 않았다. 왜냐? ON절 조건이 key가 동일한 행이니까. 

 

ON절에 다른 조건을 줘보자.

SELECT a.key, b.value FROM a1 as a LEFT JOIN b1 as b ON a.key >= b.key;

 

이런 쿼리가 있으면 잘못생각하면 안 되는데 a1 테이블의 행 하나씩을 b1 테이블의 모든 행과 비교하게 된다. 거기서 ON절을 만족하는 행만 뽑히게 되는 거지

위 쿼리 실행 결과

 

3. SELF JOIN

 

자기 자신을 JOIN 한다.

SELECT e.empname AS 사원, m.empname AS 직속상관 FROM EMPLOYEE e, EMPOLOYEE m WHERE e.MANAGER = m.EMPNO;

 

내 테이블 예제는 아니지만 보통 이럴 경우에 많이 쓴다. 

 

4. 집합 연산 UNION / UNION ALL

 

여러 SELECT 결과를 한 결과 집합으로 표시해 준다. 

 

UNION 연산자를 쓰려면 SELECT 문으로 선택된 각 필드가 동일해야 한다.

SELECT a1.key FROM a1
UNION
SELECT b1.key FROM b1

 

위 쿼리 결과

 

UNION은 중복값을 제거해 주지만 UNION ALL은 중복값을 제거해주지 않는다.

SELECT a1.key FROM a1
UNION ALL
SELECT b1.key FROM b1

위 쿼리 결과

 

5. 집합 연산 - 교집합 (오라클엔 따로 연산자가 있지만 MySQL은 JOIN을 이용해서 구한다.)

 

이미 위해서 교집합 했었다.

SELECT a.key FROM a1 a, b1 b WHERE a.key = b.key;

 

6. 집합 연산 - 차집합 (마찬가지로 JOIN을 이용한다.)

SELECT b1.key FROM b1 LEFT JOIN a1 ON b1.key = a1.key WHERE a1.key IS NULL;

위 쿼리 결과

b1을 기준으로 LEFT JOIN 하면 key가 3인 a1은 NULL일 테니 그 값이 바로 차집합 결과이다. 다른 방법이 하나 더 있다. 

서브쿼리를 이용하는 것이다. NOT IN은 따로 정리했었고, 결과는 같다.

SELECT b1.key FROM b1 WHERE b1.key NOT IN(SELECT DISTINCT a1.key FROM a1);

 

7. 서브쿼리 

 

서브쿼리가 나온 김에 서브쿼리도 정리하겠다.

 

위 예제를 보면 알겠지만 서브쿼리는 하나의 SQL문 안에 포함되는 또 다른 SQL 문이다. 자식쿼리 내부쿼리라고도 하며 서브쿼리를 포함하는 쿼리를 메인쿼리 = 부모쿼리 = 외부쿼리 라고 한다.

 

• 서브쿼리는 메인쿼리의 칼럼 사용이 가능하다.
• 메인쿼리는 서브쿼리의 컬럼 사용이 불가하다. 
• 서브쿼리 실행 후 메인쿼리를 실행한다.
• 하나의 행만 반환하면 단일 행 서브쿼리, 여러 개의 행을 반환하면 다중 행 서브쿼리라 한다.
• 서브쿼리의 위치에 따라 명칭이 존재한다.

SELECT column1, (SELECT ... ) // 스칼라 서브쿼리 (하나의 컬럼처럼 사용한다.)
FROM (SELECT ... ) // 인라인 뷰 (하나의 테이블처럼 사용한다.)
WHERE (SELECT ... ) // 일반 서브쿼리 (하나의 변수처럼 사용한다.)

 

• WHERE 절에 오는 서브쿼리의 값이 여러 개면 ANY나 ALL 다중 비교 연산자를 써야 하며, 하나면 단일 비교 연산자를 사용하면 된다.
• FROM 절에 오는 서브쿼리는 무조건 AS 별칭을 지정해 줘야 한다.
• SELECT 절에 오는 서브쿼리는 하나의 값만을 반환해야 한다.

SELECT , FROM, WHERE 뿐만 아니라 HAVING, ORDER BY, INSERT문의 VALUES, UPDATE 문의 SET에서도 사용이 가능하다.

 

* UPDATE SET에 서브쿼리를 사용할 때에도 별칭을 주어야 한다는 소리가 있다.

---

 

참고 출처

[MYSQL] 📚 테이블 조인(JOIN) - 그림으로 알기 쉽게 정리 (tistory.com)

[MYSQL] 📚 테이블 조인(JOIN) - 그림으로 알기 쉽게 정리

[MYSQL] 📚 서브쿼리 개념 & 문법 💯 정리 (tistory.com)

[MYSQL] 📚 서브쿼리 개념 & 문법 💯 정리