[SQL]JOIN

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

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

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

또는

SELECT emp.ename, dept.dname
FROM emp
CROSS JOIN dept;
//14 * 4 =56개 행 출력
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.001 sec)

INNER JOIN

Equi Join (동등 조인)

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

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

 

• WHERE문을 사용

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

SELECT emp.empno, emp.ename, dept.dname
FROM emp, 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 |
+-------+--------+------------+

 

• 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;

 

• USING 절을 사용한 조인

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

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

 

SELECT emp.empno, emp.ename, dept.dname
FROM emp
JOIN dept USING (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;

-- 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)이 연결

 

 

OUTER JOIN

OUTER JOIN은 SQL에서 두 테이블 간의 관계를 설정할 때, 한쪽 테이블에만 데이터가 있는 경우에도 모든 행을 포함할 수 있도록 도와주는 조인 방식. INNER JOIN과 달리, 일치하지 않는 데이터도 함께 조회할 수 있는 것이 큰 특징.

 

LEFT OUTER JOIN

LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 유지하고, 오른쪽 테이블에서 일치하는 데이터만 가져온다. 일치하지 않는 오른쪽 테이블의 데이터는 NULL로 표시된다.

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

 

RIGHT OUTER JOIN

RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 유지하고, 왼쪽 테이블에서 일치하는 데이터만 가져온다. 일치하지 않는 왼쪽 테이블의 데이터는 NULL로 표시된다.

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

 

-- 모든 부서와 해당 부서에 속한 직원 정보 가져오기. 직원이 없는 부서도 포함
SELECT d.deptno, e.empno, e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;

+--------+-------+--------+------------+
| deptno | empno | ename  | dname      |
+--------+-------+--------+------------+
|     10 |  7782 | CLARK  | ACCOUNTING |
|     10 |  7839 | KING   | ACCOUNTING |
|     10 |  7934 | MILLER | ACCOUNTING |
|     20 |  7369 | SMITH  | RESEARCH   |
|     20 |  7566 | JONES  | RESEARCH   |
|     20 |  7788 | SCOTT  | RESEARCH   |
|     20 |  7876 | ADAMS  | RESEARCH   |
|     20 |  7902 | FORD   | RESEARCH   |
|     30 |  7499 | ALLEN  | SALES      |
|     30 |  7521 | WARD   | SALES      |
|     30 |  7654 | MARTIN | SALES      |
|     30 |  7698 | BLAKE  | SALES      |
|     30 |  7844 | TURNER | SALES      |
|     30 |  7900 | JAMES  | SALES      |
|     40 |  NULL | NULL   | OPERATIONS |
+--------+-------+--------+------------+

 

SELF JOIN

SELF JOIN은 하나의 테이블을 스스로 조인하여 동일 테이블 내의 데이터를 결합할 때 사용하는 조인 방식입니다. SELF JOIN은 일반적으로 테이블 내의 두 행 간의 관계를 비교하거나, 계층 구조를 나타낼 때 유용합니다.

SELF JOIN은 같은 테이블에서 두 개의 서로 다른 행을 조인하는 방식입니다. 여기서는 직원과 상사 간의 관계를 나타내기 위해 같은 테이블을 두 번 참조합니다.

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

 

SELF JOIN을 사용하여 직원과 상사 관계 표시

SELECT e.empno AS 직원번호, e.ename AS 직원이름, m.empno AS 상사번호, m.ename AS 상사이름
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;

+--------------+--------------+--------------+--------------+
| 직원번호     | 직원이름     | 상사번호     | 상사이름     |
+--------------+--------------+--------------+--------------+
|         7369 | SMITH        |         7902 | FORD         |
|         7499 | ALLEN        |         7698 | BLAKE        |
|         7521 | WARD         |         7698 | BLAKE        |
|         7566 | JONES        |         7839 | KING         |
|         7654 | MARTIN       |         7698 | BLAKE        |
|         7698 | BLAKE        |         7839 | KING         |
|         7782 | CLARK        |         7839 | KING         |
|         7788 | SCOTT        |         7566 | JONES        |
|         7839 | KING         |         NULL | NULL         |
|         7844 | TURNER       |         7698 | BLAKE        |
|         7876 | ADAMS        |         7788 | SCOTT        |
|         7900 | JAMES        |         7698 | BLAKE        |
|         7902 | FORD         |         7566 | JONES        |
|         7934 | MILLER       |         7782 | CLARK        |
+--------------+--------------+--------------+--------------+

 

**SELF JOIN은 언제 사용?

[문제] 사원번호, 사원이름, 관리자 이름을 출력 (관리자가 없으면 '관리자 없음'으로 출력)

결과
+--------------+--------------+------------------+
| 사원번호     | 사원이름     | 관리자이름       |
+--------------+--------------+------------------+
|         7369 | SMITH        | FORD             |
|         7499 | ALLEN        | BLAKE            |
|         7521 | WARD         | BLAKE            |
|         7566 | JONES        | KING             |
|         7654 | MARTIN       | BLAKE            |
|         7698 | BLAKE        | KING             |
|         7782 | CLARK        | KING             |
|         7788 | SCOTT        | JONES            |
|         7839 | KING         | 관리자 없음      |
|         7844 | TURNER       | BLAKE            |
|         7876 | ADAMS        | SCOTT            |
|         7900 | JAMES        | BLAKE            |
|         7902 | FORD         | JONES            |
|         7934 | MILLER       | CLARK            |
+--------------+--------------+------------------+

SELECT e.empno AS 사원번호, e.ename AS 사원이름, 
       IFNULL(m.ename, '관리자 없음') AS 관리자이름
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;