[Day 11 | DB] Inner join

1. 카테시안 프로덕트 (크로스 조인)

카테시안 프로덕트(Cartesian Product)란 두 개의 테이블의 모든 행을 조합하여 반환하는 연산입니다. SQL에서 CROSS JOIN 또는 JOIN 조건 없이 단순히 테이블 두 개를 함께 조회할 때 발생합니다. 이 결과는 두 테이블의 행 수를 곱한 만큼의 행을 반환하게 되며, 이를 데카르트 곱이라고도 부릅니다.

카테시안 프로덕트 만들기

SELECT emp.ename, dept.dname
FROM emp, dept;

또는

SELECT emp.ename, dept.dname
FROM emp
CROSS JOIN dept;

• emp와 dept 테이블의 모든 행을 조합하여 결과를 반환합니다.
• emp 테이블의 행 수가 14개, dept 테이블의 행 수가 4개라면, 결과는 14 x 4 = 56개의 행이 됩니다.

카테시안 프로덕트는 모든 행을 조합하여 반환하기 때문에, 일반적으로 의미 없는 데이터가 생성됩니다. 예를 들어, 모든 직원이 모든 부서에 포함된 것처럼 보이는 결과가 나오게 되며, 실제로는 의미 있는 연결을 제공하지 않습니다.

INNER JOIN

Equi Join (동등 조인)

Equi Join은 동등(=) 연산자를 사용하여 두 테이블의 열이 동일한 값을 가질 때 데이터를 결합하는 조인 방식입니다.

• 두 테이블에서 동일한 값을 가지는 행을 결합합니다.
• = 연산자를 사용하여 조건을 설정합니다.
• Equi Join의 결과에는 중복된 열이 포함될 수 있습니다.

1. WHERE문을 사용

SELECT 열_목록
FROM 테이블1, 테이블2
WHERE 테이블1.공통_열 = 테이블2.공통_열;

• FROM 절: 조인할 테이블을 나열하고, 쉼표로 구분합니다.
• WHERE 절: 두 테이블 간의 공통 열을 기준으로 조건을 작성하여 일치하는 데이터만 결합합니다.

SELECT emp.empno, emp.ename, dept.dname
FROM emp
INNER JOIN dept
WHERE emp.deptno = dept.deptno;

+-------+--------+------------+
| empno | ename  | dname      |
+-------+--------+------------+
|  7369 | SMITH  | RESEARCH   |
|  7499 | ALLEN  | SALES      |
|  7521 | WARD   | SALES      |
|  7566 | JONES  | RESEARCH   |
|  7654 | MARTIN | SALES      |
....
|  7934 | MILLER | ACCOUNTING |
+-------+--------+------------+

2. JOIN 연산자 사용

SELECT 열_목록
FROM 테이블1
JOIN 테이블2 ON 테이블1.공통_열 = 테이블2.공통_열;

SELECT emp.empno, emp.ename, dept.dname
FROM emp
INNER JOIN dept ON emp.deptno = dept.deptno;

• emp.deptno = dept.deptno 조건으로 두 테이블의 deptno 값이 동일한 행을 결합합니다.
• 결과: 각 직원의 이름(ename)과 부서 이름(dname)이 연결된 결과가 반환됩니다.

3. USING 절을 사용한 조인

USING 절을 사용하면 두 테이블에 동일한 열 이름이 존재할 때 그 열 이름만 명시하여 조인을 수행할 수 있습니다.

SELECT 열_목록
FROM 테이블1
JOIN 테이블2 USING (공통_열);

• 두 테이블에 동일한 이름을 가진 열이 있을 때만 사용할 수 있습니다.
• USING 절에 명시된 공통 열은 결과에 중복되지 않고 한 번만 표시됩니다.
• INNER JOIN, LEFT JOIN, RIGHT JOIN 등과 함께 사용 가능합니다.

SELECT emp.empno, emp.ename, dept.dname
FROM emp
JOIN dept USING (deptno);

• USING (deptno)를 사용하여 deptno 열을 기준으로 두 테이블을 조인합니다.
• 조인된 결과에는 deptno 열이 중복되지 않고 한 번만 표시됩니다.

별칭 (AS)를 통해 테이블 명 줄이기

-- 테이블 명 축약
SELECT d.deptno, d.dname, e.empno, e.ename
FROM emp AS e
INNER JOIN dept AS d ON e.deptno = d.deptno
WHERE e.deptno = 10;

• emp 테이블을 e로, dept 테이블을 d로 별칭을 설정하여 짧게 표기합니다.
• AS를 생략해도 쿼리가 더 간결해지며, 대부분의 SQL 데이터베이스에서 정상적으로 작동합니다.

-- AS 생략
SELECT d.deptno, d.dname, e.empno, e.ename
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno = 10;

Non-Equi Join (비동등 조인)

Non-Equi Join은 동등(=) 연산자가 아닌 다른 연산자(예: <, >, <=, >=, BETWEEN 등)를 사용하여 두 테이블의 열 간 조건을 설정하고 데이터를 결합하는 조인 방식입니다. Non-Equi Join은 일반적인 동등 조건이 아닌 범위 조건을 기반으로 데이터를 조인할 때 사용됩니다.

• 두 테이블의 열 값이 동등하지 않고, 특정 조건을 만족하는 경우에만 데이터를 결합합니다.
• 주로 <, >,BETWEEN과 같은 비동등 조건을 사용하여 데이터를 조인합니다.
• Non-Equi Join은 주로 범위 기반 조인이나 복잡한 관계를 설정할 때 사용됩니다.

SELECT 열_목록
FROM 테이블1
JOIN 테이블2 ON 테이블1.열 <테이블2.열

-- 예제 :  직원 테이블(emp)과 급여 등급 테이블(salgrade)을 조인하여
-- 직원의 급여가 특정 급여 범위에 속하는지 확인하기
SELECT emp.empno, emp.ename, emp.sal, salgrade.grade
FROM emp
JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

• salgrade 테이블의 losal과 hisal 범위 내에 있는 emp.sal 값을 가진 직원 데이터를 결합합니다.
• 결과: 각 직원의 급여(sal)가 속하는 급여 등급(grade)이 연결되어 표시됩니다.

수업문제

-- [문제] 입사년도가 2011년인 사원에 대한 사원번호, 사원명, 급여, 급여등급(호봉)을 출력

-- 답
SELECT emp.empno, emp.ename, emp.sal, salgrade.grade
FROM emp
JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal
WHERE YEAR(emp.hiredate) = 2011;

+-------+--------+---------+-------+
| empno | ename  | sal     | grade |
+-------+--------+---------+-------+
|  7499 | ALLEN  | 1600.00 |     3 |
|  7521 | WARD   | 1250.00 |     2 |
|  7566 | JONES  | 2975.00 |     4 |
|  7654 | MARTIN | 1250.00 |     2 |
|  7698 | BLAKE  | 2850.00 |     4 |
|  7782 | CLARK  | 2450.00 |     4 |
|  7839 | KING   | 5000.00 |     5 |
|  7844 | TURNER | 1500.00 |     3 |
|  7900 | JAMES  |  950.00 |     1 |
|  7902 | FORD   | 3000.00 |     4 |
+-------+--------+---------+-------+

-- [문제] 사원에 대한 사원번호, 사원이름, 급여등급, 기준급여, 인상급여를 출력
1 호봉  -> 40% 인상
2 호봉  -> 30% 인상
3 호봉  -> 20% 인상
기타     -> 인상 없음

-- 답
SELECT e.empno, e.ename, s.grade, e.sal 기준급여,
       CASE
           WHEN s.grade = 1 THEN e.sal * 1.4
           WHEN s.grade = 2 THEN e.sal * 1.3
           WHEN s.grade = 3 THEN e.sal * 1.2
           ELSE e.sal
       END AS 인상급여
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

+-------+--------+-------+--------------+--------------+
| empno | ename  | grade | 기준급여     | 인상급여     |
+-------+--------+-------+--------------+--------------+
|  7369 | SMITH  |     1 |       800.00 |     1120.000 |
|  7499 | ALLEN  |     3 |      1600.00 |     1920.000 |
|  7521 | WARD   |     2 |      1250.00 |     1625.000 |
|  7566 | JONES  |     4 |      2975.00 |     2975.000 |
|  7654 | MARTIN |     2 |      1250.00 |     1625.000 |
|  7698 | BLAKE  |     4 |      2850.00 |     2850.000 |
|  7782 | CLARK  |     4 |      2450.00 |     2450.000 |
|  7788 | SCOTT  |     4 |      3000.00 |     3000.000 |
|  7839 | KING   |     5 |      5000.00 |     5000.000 |
|  7844 | TURNER |     3 |      1500.00 |     1800.000 |
|  7876 | ADAMS  |     1 |      1100.00 |     1540.000 |
|  7900 | JAMES  |     1 |       950.00 |     1330.000 |
|  7902 | FORD   |     4 |      3000.00 |     3000.000 |
|  7934 | MILLER |     2 |      1300.00 |     1690.000 |
+-------+--------+-------+--------------+--------------+

-- 부서이름까지 포함한 3개 테이블의 조인

SELECT e.empno, e.ename, s.grade, e.sal 기준급여,
       CASE
           WHEN s.grade = 1 THEN e.sal * 1.4
           WHEN s.grade = 2 THEN e.sal * 1.3
           WHEN s.grade = 3 THEN e.sal * 1.2
           ELSE e.sal
       END AS 인상급여,
       d.dname 부서이름
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
JOIN dept d ON e.deptno = d.deptno;

+-------+--------+-------+--------------+--------------+--------------+
| empno | ename  | grade | 기준급여     | 인상급여     | 부서이름     |
+-------+--------+-------+--------------+--------------+--------------+
|  7369 | SMITH  |     1 |       800.00 |     1120.000 | RESEARCH     |
|  7876 | ADAMS  |     1 |      1100.00 |     1540.000 | RESEARCH     |
|  7900 | JAMES  |     1 |       950.00 |     1330.000 | SALES        |
|  7521 | WARD   |     2 |      1250.00 |     1625.000 | SALES        |
|  7654 | MARTIN |     2 |      1250.00 |     1625.000 | SALES        |
|  7934 | MILLER |     2 |      1300.00 |     1690.000 | ACCOUNTING   |
|  7499 | ALLEN  |     3 |      1600.00 |     1920.000 | SALES        |
|  7844 | TURNER |     3 |      1500.00 |     1800.000 | SALES        |
|  7566 | JONES  |     4 |      2975.00 |     2975.000 | RESEARCH     |
|  7698 | BLAKE  |     4 |      2850.00 |     2850.000 | SALES        |
|  7782 | CLARK  |     4 |      2450.00 |     2450.000 | ACCOUNTING   |
|  7788 | SCOTT  |     4 |      3000.00 |     3000.000 | RESEARCH     |
|  7902 | FORD   |     4 |      3000.00 |     3000.000 | RESEARCH     |
|  7839 | KING   |     5 |      5000.00 |     5000.000 | ACCOUNTING   |
+-------+--------+-------+--------------+--------------+--------------+