[Oracl] 다른 테이블간의 결합을 통해 데이터를 조회하는 UNION | JOIN

작업환경 : Live Oracle

---

▶ UNION 

- UNION조건 절은 두 테이블 간의 데이터를 비교해서 조회함

- 두 테이블을 합친 데이터를 반환합니다 (옵션에 따라서 중복을 제거하거나 중복을 함께 출력합니다)

- (필수조건) UNION조건 절은 *(모든값)을 조회시 두 테이블간의 컬럼수와 자료형이 동일해야한다.

 

샘플로 하나 보자면

SELECT MANAGER_ID, FIRST_NAME FROM EMPLOYEES;

SELECT MANAGER_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

 

 

 

위 두개의 테이블을 보려고 한다.계속 에러가 떠서 보니까 라이브 환경에서는 권한자인 HR.  을 해주어야하는데 

이 부분을 항상 도구를 사용하다보니 잊었었다.

 

이 두개를 합쳐서 한번에 출력을 하고싶은거죠.

FIRST_NAME, DEPARTMENT_NAME를 알고싶을때 사용하는데 

 

SELECT MANAGER_ID, FIRST_NAME FROM EMPLOYEES UNION SELECT MANAGER_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

 

이렇게 중간에 UNION만 결합해주면 됩니다.

UNION앞에 있는 TABEL뒤에 뒤에있는 테이블이 붙어서 출력됩니다.

단, 중복값은 제외됩니다.

 

▶그러면 중복값포함해서 출력하고싶다면?

SELECT MANAGER_ID, FIRST_NAME FROM EMPLOYEES UNION ALL SELECT MANAGER_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

 

중간에 ALL을 추가해주면 중복 포함해서 같이 출력이 되는것을 확인할 수 있습니다.

 

▶  중복되는값만 출력하기

SELECT DEPARTMENT_ID FROM EMPLOYEES INTERSECT SELECT DEPARTMENT_ID FROM DEPARTMENTS;

 

 

▶  두개의 테이블에서 비교하고 중복되지 않는 값만 조회하기

 

SELECT MANAGER_ID FROM EMPLOYEES MINUS SELECT MANAGER_ID FROM DEPARTMENTS;​

연결어는 MINUS입니다.

두개의 테이블에서는 중복되는 값이 없는것을 확인할 수 있습니다.

 

 

---

■ 예제

1. EMPLOYEES테이블과 JOB_HISTORY테이블을 비교하여 EMPLOYEE_ID 와 JOB_ID가 동일한 값을 조회하여라.

SELECT EMPLOYEE_ID, JOB_ID FROM HR.EMPLOYEES INTERSECT
SELECT EMPLOYEE_ID, JOB_ID FROM HR.JOB_HISTORY;

2. EMPLOYEES테이블과 JOB_HISTORY테이블을 비교하여 EMPLOYEE_ID 와 JOB_ID가 동일하지 않는 값을 조회하여라.

SELECT EMPLOYEE_ID, JOB_ID FROM HR.EMPLOYEES MINUS
SELECT EMPLOYEE_ID, JOB_ID FROM HR.JOB_HISTORY

3. EMPLOYEES테이블과 JOB_HISTORY테이블에 존재하는 데이터 중 EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID를 기준으로 중복값을 제외하고 모두 조회하여라.

SELECT EMPLOYEE_ID, JOB_ID FROM HR.EMPLOYEES UNION
SELECT EMPLOYEE_ID, JOB_ID FROM HR.JOB_HISTORY

4. EMPLOYEES테이블과 JOB_HISTORY테이블에 존재하는 데이터 중 EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID를 기준으로 중복값을 포함하여 모두 조회하여라.

SELECT EMPLOYEE_ID, JOB_ID FROM HR.EMPLOYEES UNION ALL
SELECT EMPLOYEE_ID, JOB_ID FROM HR.JOB_HISTORY

---

▶ Replace

job_id 'CLERK'라는 이름이 존재합니다. 그런데 얘기를 'ASSISTANT'로 변경해서 출력할 수 있습니다.

 

■ 예제

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, REPLACE(JOB_ID, 'CLERK', 'ASSISTANT') AS 수정내역
FROM HR.EMPLOYEES;

 

이렇게 잘 바뀌는지 확인가능합니다.

 

또한 IT를 DBMS로 변경해야하는 테이블에서는

SELECT D.*, REPLACE(DEPARTMENT_NAME, 'IT', 'DBMS') AS 수정내역
FROM HR.DEPARTMENTS D;

 

여기서 SELECT 문에서 여러개를 선택할때는 꼭 , 컴마를 작성해야합니다.

그리고 LIVE에서는 HR의 권한을 꼭 부여해야 제대로 된 DB조회가 가능합니다.

---

▶ JOIN

- 여러 테이블에서 한꺼번에 연결시켜서 사용자가 원하는 데이터를 조회함

- 3가지 종류와 각각의 특징이 존재함

INNER JOIN

Non-Equit조인 <동등하지 않다는 조인>

OUTER JOIN

 

- join을 상요하면 속도가 조금 느린 단점이 존재한다.

---

▶ 1. INNER JOIN == 내부조인 == 등가조인

-여러 테이블을 한꺼번에 조회해서 사용자가 원하는 조건으로 조회(PK==FK간의 연결관계)

-조건이 정확히 일치하는 값만 정리해서 조회

- 두 테이블 간의 공통된 행을 반환하는 기능

- 정규화를 통해서 분리된 테이블을 사용자의 편의성을 고려하여 제공해주는 기능

- FROM 테이블명A [INNER] JOIN 테이블명B ON 조건

>> AS(알리아스)를 꼭!! 지정해줘야함

>> INNER은 생력가능하다 기본 디폴드값!

>> 조건 : 같다 라고 생각하면 됨.

 

- ON절에 같다(=)라는 등호가 있고, 두 테이블간의 관계가 연결되어있는 경우

EX) A.번호(PK) = B.번호(FK)인 경우, 반드시 두 테이블간의 일치하는 값만 조회!

 

-명령어 순서

FROM -> ON -> WHERE -> GROUP BY -> HAVING ->SELECT -> ORDER BY

외우는 방식은 FOWG HSO

 

---

■ 예제

EMP테이블에서 사원번호로 DEPT 테이블의 지역을 검색

SELECT * FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

> 해석을 간단하게 하자면 

1. EMP테이블에서 모두 출력을 할 것이다

2. DEPT와 EMP를 비교회서 조회를 할 것이다.

3. 비교하는 대상자는 E.DEPTNO(알리아스로 명칭지정) D.DEPTNO

기본키 외래키로 연결이 되어있습니다.

 

왼쪽의 DEPTNO는 EMP꺼고 오른쪽은 DPTNO는 DEPTNO는 그 외것이다.

PK,FK이 같은것만 출력된것으로 확인됩니다.

 

그런데 이번에는 

SELECT EMPNO, ENAME, LOC FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

DEPTNO만 동일한 값을 조회하고 3개의 컬럼만 출력합니다.

 

 

 

하나의 쿼리로 접근이 모두 가능하다는점이 포인트입니다.

 

---

 

 

사용 TABLE -> SELECT * FROM PLAYER; 

 

01. jobs테이블에서 job_id로 직원들의 job_title, email, first_name, last_name을 조회 

SELECT JOB_TITLE, EMAIL, LAST_NAME, FIRST_NAME FROM JOBS J JOIN EMPLOYEES E ON J.JOB_ID = E.JOB_ID;

를 했는데 자꾸 ORA-00942: table or view does not exist 라는에러가 떴다 

해당 에러는 테이블이나 뷰가 없다는건데 어디서 문제인지 도출을 해보려고 한다.

 

스키마를 확인해보았는데 데이터는 있었다 

다만 권한이 계속 문제돼서 조회가 되지 않는것같다.결국 오류는 권한 부족이었다.

권한 부여 하기

GRANT SELECT ON HR.EMPLOYEES TO YOUR_USER;

 

✅ 권한 부여 후 해야 할 일

1️⃣ 권한이 부여되었는지 확인

sql

 

SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = 'EMPLOYEES';

→ 이번에는 NO DATA FOUND가 아니라 결과가 나와야 합니다.

 

2️⃣ HR 테이블 접근 테스트

sql

 

SELECT * FROM HR.EMPLOYEES;

→ 데이터가 나오면 정상적으로 접근 가능 🎉
→ 여전히 ORA-00942 오류가 발생하면, DBA가 권한을 제대로 부여했는지 다시 확인 요청해야 합니다.

 

3️⃣ 이제 조인 SQL 실행 가능

sql

 

SELECT JOB_TITLE, EMAIL, LAST_NAME, FIRST_NAME FROM JOBS J JOIN HR.EMPLOYEES E ON J.JOB_ID = E.JOB_ID;

02. employees 테이블에서 hiredate(고용일) 2003 ~ 2004년까지인 사원의 정보와 부서명을 검색.
    단, 사원의 정보는 이름과 성이 띄어쓰기로 연결되어 있다. (EMPLOYEES테이블과 DEPARTMENTS테이블을 이용)

 

>> DEPARTMENT_ID를 통해서 컬럼을 비교함

>> 조인으로 연결이 된다면, 조건절, 정렬 등.. 모든 쿼리문을 이용가능

 

SELECT E.HIRE_DATE, E.FIRST_NAME || ' ' || E.LAST_NAME "사원의 이름",
       D.DEPARTMENT_NAME "부서명"
FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE HIRE_DATE BETWEEN TO_DATE('2000-01-01', 'YYYY-MM-DD') AND TO_DATE('2020-12-31', 'YYYY-MM-DD')
ORDER BY HIRE_DATE;

 

>> BETWEEN ~와~사이! AND조건절을 넣어서 해도 상관은 없음!

명령어 순서를 보면 FROM >  JOIN > ON > WHERE > SELECT > ORDERBY

즉 문제의 핵심은 JOIN을 하게되면 조건절을 모두 다 이용할 수 있다는점이 포인트다.

03. player테이블에서 송종국 선수가 속한 팀의 전화번호를 검색

SELECT PLAYER_NAME, T.TEAM_ID, TEL
FROM TEAM t JOIN PLAYER p ON T.TEAM_ID = P.TEAM_ID
WHERE P.PLAYER_NAME = '송종국';

 

 

 

---

 

 

▶ 2. OUTER JOIN(외부조인)

-외부조인은 내부 조인과 다르게 한쪽에만 값이 있어도 테이블을 합쳐서 조회한다.

-종류

A. RIGHT JOIN(RIGHT OUTER JOIN)

A-1. 오른쪽이 왼쪽보다 적을 때 : 오른쪽 테이블의 모든 행을 반환, 왼쪽 테이블은 미출력

A-2. 오른쪽이 왼쪽보다 많을 때 : 오른쪽 테이블의 모든 행을 반환, 왼쪽 테이블에 일치하는 행이 없으면 NULL출력

 

B. LEFT JOIN(LEFG OUTER JOIN)

B-1. 왼쪽이 오른쪽 보다 많을 때 : 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에 일치하는 행이 없으면 NULL출력

B-2. 왼쪽이 오른쪽 보다 적을 때 : 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블은 미출력

 

C. FULL JOIN (FULL OUTER JOIN)

양쪽 테이블의 모든 행을 반환하고, 일치하는 행이 없는 방향은 NULL값으로 조회

---

 

헷갈리니까 RIGT면 오른쪽 기준으로 생각하기, LEFT는 왼쪽 기준으로 생각하기.

전제조건은 A,B중에서 B테이블의 행의 개수가 더 적다는 전제조건이다.

 

 

B테이블이 A테이블보다 적을때 RIGHT라면 다 출력한다.

 

이건 굉장히 헷갈릴 수 밖에 없다.

일단 RIGHT면 오른쪽 기준(LIST)으로 생각하고 오른쪽에서 좌측은 다 쪼인하고 번호 맞춰서 잘려서 출력됨!

 

반대인 경우 RIGHT조인인데 GRADE가 더 컬럼이 많다면 출력을 하고 NULL로 채운다.

---

■ 실습

RIGT 조인 기준

오른쪽이 왼쪽보다 적을 때

SELECT * FROM TBL_GRADE TG RIGHT OUTER JOIN TBL_USER_LIST TUL
ON TG.GRADE_NUM = TUL.GRADE_NUM;

 

오른쪽 기준으로 출력이 된다.

 

오른쪽이 왼쪽보다 많을때

SELECT * FROM TBL_USER_LIST TUL RIGHT OUTER JOIN TBL_GRADE TG
ON TG.GRADE_NUM = TUL.GRADE_NUM;

 

윗줄과 보면 TBL, TG가 순서가 바뀌어있다는점이 조금 다르다.

 

LEFT는 왼쪽기준으로 생각하느냐 오른쪽기준으로 생각하느냐 차이입니다.

---

 

▶ UNION 과 JOIN의 차이점

01. 유니온

- 두개 이상의 SELECT문을 이용해서 결과 내용을 하나의 테이블로 조회함

- UNION 은 컬럼의 수와 타입만 일치하면 다른 테이블 간의 조회도 가능

- 컬럼의 수와 타입이 일치하지 않으면 UNION할 수 없다.

 

02. 조인

- 두 테이블에서 하나의 공통 컬럼을 비교 및 결합하고자 할 때 이용

-WHERE 조건으로 두 개 이상의 테이블에서 원하는 컬럼을 선택하여 조회

- 조인은 관계가 맺어진 상태에서 데이터를 조회함.

---

* DECODE(대상컬럼, search1, result1,

                                   serch2, result2,

                                    serch3, result3,

                                  deafult값)

 

↑ 이건 알아두면 편한 폼인데 

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID,
       DECODE(JOB_ID, 'IT_PROG', '프로그래머',
                     'SA_REP', '영업사원',
                     'FI_ACCOUNT', '회계사',
                     '기타') AS JOB_TITLE
FROM HR.EMPLOYEES;

 

 

이런식으로 잘 쓰면  정말 편할것같은 툴입니다.

단 실수하는 케이스가있는데 컬럼 뒤에 컴마를 꼭! 붙여주어야합니다.

---

■ 실습

employees 테이블에서 salary 값에 따라 다음과 같이 직원 등급을 변환하여 출력하는 쿼리를 작성.

- 20000  : '고급'
- 10000 : '중급'
- 나머지 : ‘추가 확인 필요'

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY,
       DECODE(SALARY, '20000', '고급',
                     '10000', '중급',
                     '추가확인필요') AS EMPLOYEE_LEVEL
FROM HR.EMPLOYEES;

---

* CASE표현식

 

CASE

             WHEN 조건1 THEN 값1

             WHEN 조건2 THEN 값2

             WHEN 조건3 THEN 값3

             .........

             ELSE 나머지 값

END

 

↑ 이건 알아두면 편한 폼인데

SELECT ENAME, DEPTNO, SAL,
CASE
    WHEN DEPTNO = 20 THEN SAL * 1.1
    WHEN ENAME = 'SMITH' THEN SAL * 20
    WHEN DEPTNO = 30 THEN SAL * 1.3
END AS "연봉 인상 결과"
FROM EMP;

한 예시로 보면 이렇게 코드를 작성해 볼 수 있다.

 

근데 여기서 보면 SMITH는 20을 곱해야하는데 연봉 인상 결과를 보면 1.1를 곱한 값이 도출되는데

그 이유는 첫번째 순서먼저 진행이 되기때문에 그렇습니다.

즉 WHEN절에서는 순차적으로 진행되고 중복으로 적용되지 않는다는것을 알 수 있습니다.

---

01.employees테이블에서 성이 A와 J로 시작하는 경우 전체이름(성 + 이름)으로 작성하고, 

그 외에는 이름으로만 입력한다.

SELECT FIRST_NAME 성, LAST_NAME 이름,
  CASE
    WHEN FIRST_NAME LIKE 'A%' THEN FIRST_NAME || ' ' || LAST_NAME
    WHEN FIRST_NAME LIKE 'J%' THEN FIRST_NAME || ' ' || LAST_NAME
    ELSE LAST_NAME
  END AS 변경이름
FROM EMPLOYEES
ORDER BY 성;

 

02. EMP테이블의 사원정보를 가져오며, SAL이 높은순으로 정렬하고 ‘비고’ 컬럼을 추가한다.
    - ’비고’ 컬럼에는 급여 순위가 1 ~ 5등이면 상
    - 6 ~ 10등이면 중 
    - 나머지는 하를 입력한다. 
결과열에서는 ROWNUM, EMP  전체 열, 비고 컬럼이 조회된다.

 

SELECT ROWNUM, E.*,
       CASE
             WHEN ROWNUM BETWEEN 1 AND 5 THEN '상'
             WHEN ROWNUM BETWEEN 6 AND 10 THEN '중'
             ELSE '하'
        END 비고
FROM (SELECT *FROM EMP ORDER BY SAL DESC) E;