Oracle DBA/SQL

058 여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)

Nuez 2023. 6. 30. 12:02
반응형

JOIN

: 보다 더 좋은 정보를 테이블에서 뽑아내려면 '조인'을 사용해야 한다. 

 

하나의 테이블로는 알 수 없는 정보를 다른 테이블과 조인함으로써 알 수 있게 해주는 기술 

2개 이상의 테이블에서 각각 컬럼의 데이터를 모아서 하나의 결과로 출력해주는 SQL문법

예제. KING은 어느 부서에서 근무하는가? 부서 위치가 어떻게 되는가? 

SELECT ename, loc
FROM emp, dept;

 

이렇게

1) 조인 조건을 주지 않거나(omit join condition),

2) 잘못된 조인조건(incorrect join condition)을 기술하면 원하는 결과값이 나오지 않음.

→ 모든 조합을 다 구해 출력 -> Cartisian product

 

ename (14명) * loc (4지역) = 56개행

 

 

SELECT ename, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;

조인 조건을 통해 두 테이블을 잘 연결해줘야 함.

 

조인 조건의 갯수 = 테이블의 갯수 - 1 

(각 테이블을 조회해 테이블간 공통점(deptno)을 찾고 WHERE절에 작성)

 

공통점 (같은 점 '=')을 찾아 조인하므로 EQUI JOIN이라고 부름 

 

SELECT ename, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno -- 조인조건
	and ename = 'KING'; -- 검색 조건

문제 291. 사원번호, 이름, 월급, 부서위치, 입사일을 출력하시오

SELECT empno, ename, sal, loc, hiredate
FROM emp, dept
WHERE emp.deptno = dept.deptno;

문제 292. 사원이름, 월급, 부서위치, 부서번호를 출력하시오. 

SELECT ename, sal, loc, deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno;

오류 발생!

ORA-00918: 열의 정의가 애매합니다
00918. 00000 -  "column ambiguously defined"

 

조인할 때 컬럼명 앞에 테이블 명을 명시하지 않으면,
오라클이 내부적으로 어느 테이블에 있는 컬럼인지 찾는 쿼리를 자체적으로 수행함. (recursive SQL)

 

deptno 컬럼은 양쪽 테이블에 다 있기 때문에, 애매하다고 출력하는 것. 

명시해줘야 함. 

 

1. ORACLE이 찾는 작업을 수행하지 않도록 테이블명.컬럼명 이렇게 명시해줘야 함. (성능차원)

2. 테이블을 잘 모르는 다른 사용자가 코드를 이해하기 쉽게하기 위해서도 기술할 필요가 있음. 

 

SELECT ename, sal, loc, emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno;

이렇게 작성하면 됨.

하지만 테이블 명도 너무 길어서 매번 입력하기 번거로우므로, 테이블명도 별칭을 정해준다. 

SELECT e.ename, e.sal, d.loc, e.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno;

 

 

작성 방법 :

 

FROM 절에 테이블명(공백 한칸) 별칭 ex) emp e

 

 

FROM절에 별칭을 사용했다면, 다른 절에서도 반드시 별칭을 기술해줘야한다. 

테이블 명을 섞어서 써서는 안된다. (본 테이블 명과 별칭의 병기X)

SELECT e.ename, emp.sal, d.loc, e.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno;

↑ 잘못된 SQL문 

오류 발생!

ORA-00904: "EMP"."SAL": 부적합한 식별자
00904. 00000 -  "%s: invalid identifier"

 

FROM 절에서 emp의 별칭을 e로 먼저 지정해주었으므로,

SELECT절에서 e와 emp를 혼용하면 안됨! 

 

emp.sal → e.sal로 수정 필

문제 293. 이름, 월급, 부서위치, 직업을 출력하시오. 

SELECT e.ename, e.sal, d.loc, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno;

문제 294. 위의 결과에서 직업이 SALESMAN만 출력하시오. 

SELECT e.ename, e.sal, d.loc, e.job
FROM emp e, dept d
WHERE e.deptno = d.deptno -- 조인 조건
	AND e.job = 'SALESMAN'; -- 검색 조건

문제 295.월급이 2500이상인 사원들의 이름, 월급, 부서위치를 출력하시오.

SELECT e.ename, e.sal, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno -- 조인 조건
    AND e.sal >= 2500; -- 검색 조건

문제 296. DALLAS에서 근무하는 사원들의 이름과 부서 위치를 출력하시오.

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno 
	AND d.loc = 'DALLAS';

문제 297. 월급이 1000에서 3000 사이인 사원들의 이름, 월급, 부서위치를 출력하시오. 

SELECT e.ename, e.sal, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno 
    AND e.sal BETWEEN 1000 AND 3000;

문제 299. (복습문제) 부서번호, 부서번호별 토탈 월급을 출력하시오. 

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

문제 300. 부서 위치, 부서위치별 토탈 월급을 출력하시오.

select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno 
group by d.loc;

문제 301. 부서위치, 부서위치별 토탈월급을 출력하는데, DALLAS는 제외하고 출력하고, 부서위치별 토탈 월급이 5000이상인 것만 출력하되, 부서위치별 토탈 월급이 높은 것부터 출력하시오. 

SELECT d.loc, SUM(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno -- 조인조건
        AND d.loc != 'DALLAS' -- 검색조건
GROUP BY d.loc
HAVING SUM(e.sal) >= 5000
ORDER BY 2 DESC;

문제 303. 사원 이름, 월급,b_bonus를 출력하는데, 직업이 SALESMAN만 출력하시오.

SELECT e.ename, e.sal, b.b_bonus
FROM emp e, bonus b
WHERE e.empno = b.empno 
    And e.job ='SALESMAN';

문제 304. emp와 dept와 bonus테이블을 조인해서 이름, 월급, 부서위치, b_bonus를 출력하는데 DALLAS에서 근무하는 사원들만 출력하시오. 

SELECT e.ename, e.sal, d.loc, b.b_bonus
FROM emp e, dept d, bonus b
WHERE e.deptno = d.deptno -- 조인 조건 1 
    AND e.empno = b.empno -- 조인 조건 2
    AND d.loc = 'DALLAS';  -- 검색 조건

조인하는 테이블이 3개이므로, 조인 조건은 3 - 1 = 2개