2022_04_25_DB_3일

조인

서로 다른 테이블간에 설정된 관계가 결합하여 1개 이상의 테이블에서 데이터를 조회하기 위해서 사용합니다.

• 하나 이상의 테이블로부터 데이터를 질의하기 위해서 조인을 사용
• 하나 이상의 테이블에 똑같은 열 이름이 있을 때 열 이름 앞에 테이블 이름을 붙입니다
• 오라클 조인 구문과 ANSI 조인 구문이 있습니다.

 -- 오라클 조인 문법 
SELECT  
	컬럼리스트
FROM 
	조인대상이되는 테이블(1개 이상) 
WHERE 조인 조건

--ANSI 조인 문법
SELECT
	컬럼리스트
FROM 
	테이블  조인종류 JOIN  테이블    --INNER , LEFT OUTTER ,RIGHT OUTER ,......   
ON 
	조인 조건

중요 조인의 종류

1. INNER JOIN - 내부 조인(EQUALS JOIN)
2. OUTER JOIN - 외부 조인
   1. LEFT
   2. RIGHT
   3. FULL OUTER

그 밖의 조인의 종류

1. CROSS JOIN
2. SELF JOIN

 

 

8_조인해보기_0425.sql
/*
# 조인 : 서로 다른 테이블간에 설정된 관계가 결합하여 
1개 이상의 테이블에서 데이터를 조회하기 위해서 사용합니다.
-SELECT 컬럼리스트 FROM 조인대상이 되는 테이블 (1개 이상)
 WHERE 조인 조건(오라클 조인 문법)

*/

--employees 테이블의 부서 id와 일치하는 departments 테이블의 부서 id를 찾아서
--SELECT 이하에 있는 컬럼들을 출력하는 쿼리문.

--ORACLE의 JOIN 문법 사용법 
SELECT 
    e.first_name,e.last_name,e.hire_date,
    e.salary,e.job_id,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

--ANSI JOIN 문법 :: 표준 문법 --다른 DBMS에서도 정상적으로 동작
SELECT
    e.first_name,e.last_name,e.hire_date,
    e.salary,e.job_id,e.department_id,d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id=d.department_id;

/*  테이블이름에 별칭을 붙일 때에는 AS를 안쓴다(employees e....)
각각의 테이블에 독립적으로 존재하는 컬럼의 경우에는 별칭을 생략을 해도 무방합니다(first_name,..)
그러나 , 해석의 명확성을 위해 테이블 이름을 작성하셔서 소속을 표현해 주는것이 바람직합니다.(e.first_name,..)
테이블 이름이 너무 길 시에는 ALIAS 를 작성하여  칭합니다.
*****두 테이블 모두 가지고 있는 컬럼의 경우  반드시 명시해 주셔야 합니다.*****(e.department_id)

*/

--3개의 테이블을 이용한 내부 조인(INNER JOIN)
--내부 조인 :두 테이블의 모두에서 일치하는 값을 가진 행만 반환합니다.
--EX) ON ( 10,20,30,...100  = 10,20,30,....200) -->일치하는 '10,20,30,...100' 만 반환 한다.
SELECT 
    e.first_name,e.last_name,e.department_id,
    d.department_name,
    j.job_title
FROM employees e ,departments d,jobs j
WHERE e.department_id=d.department_id 
AND e.job_id =j.job_id;

--ORACLE의 where은  조인 조건 먼저 쓰고 , 마지막에 일반 조건 작성
-
SELECT 
    e.first_name,e.last_name,e.department_id,
    d.department_name,e.job_id,j.job_title,loc.city
FROM
    employees e,
    departments d,
    jobs j,
    locations loc
WHERE e.department_id=d.department_id          -- ::조인 조건
AND e.job_id=j.job_id               --3,4         ::조인 조건
AND d.location_id=loc.location_id   --2           ::조인 조건
AND loc.state_province='California';  --해석순서 1  ::일반조건
/*
실행 순서 -- 마지막에 작성한 일반조건 먼저 해석 ->이후 조인 조건 해석  
1.loc 테이블의 province ='California' 조건에 맞는 값을 대상으로 
2.location_id 값과 같은 값을 가지는 데이터를
departments에서 찾아서 조인
3.위의 결과와 동일한 department_id를 가진
employees테이블의 데이터를 찾아 조인
4.위의 결과와 jobs테이블을 비교하여 조인하고 최종 결과를 출력.
*/


--외부 조인
/*
외부조인 : 상호 테이블간에 일치되는 값으로 연결되는 내부 조인과는 다르게 
어느 한 테이블에 공통 값이 없더라도 해당 row들이 조회 결과에
모두 포함되는 조인을 말합니다.
*/

--오라클 외부 조인 문법
SELECT 
    e.first_name,e.last_name,
    e.department_id,d.department_name
FROM employees e,departments d,locations loc
WHERE e.department_id=d.department_id(+) --외부 조인
AND d.location_id=loc.location_id;  -- 내부 조인 
-- 기준으로 잡은 테이블( (+)가 없는게 기준)에 대한 모든 데이터를 가져 옴 .
/*
WHERE e.department_id=d.department_id(+)
employees 테이블에는 존재하고 ,departments 테이블에는 존재하지 않아도
(+)가 붙지 않은 테이블을 기준으로 하여 departments 테이블이 조인에
참여하라는 의미를 부여하기 위해 기호를 붙입니다.  
::employees가 기반이되어 employees 테이블의 데이터 모두 조회된다

외부조인- 내부조인  동시에 작성 , 내부조인 -> 외부조인 순서로 해석 된다.
외부조인을 사용했더라도 ,이후에 내부 조인을 사용하면 내부조인을 우선적으로 인식합니다.
WHERE e.department_id=d.department_id(+) --외부 조인
AND d.location_id=loc.location_id;  -- 내부 조인 
*/

--외부 조인 진행 시 모든 조건에 (+)를 붙여야 하며
--일반 조건에도 (+)를 붙이지 않으면 데이터가 누락되는 현상이 발생.
--일반 조건에 (+)붙이는 경우에, 내가 원하는 데이터가 정상적으로 나오지 않을 수 있다.
SELECT 
    e.employee_id,e.first_name,
    e.department_id,
    j.start_date,j.end_date,j.job_id
FROM employees e,job_history j
WHERE e.employee_id=j.employee_id(+)  --외부 조인 조건
AND j.department_id(+)=80;  --일반 조건 
--외부조인에 값 없을 시 NULL 값 들어 감 
--외부조인을 사용할 때 ,일반 조건에도 (+) 기호를 붙여서 사용해야 한다.

 

 

 

8_조인예제테이블_0425.sql

-- 테이블 생성
CREATE TABLE info (
    id NUMBER NOT NULL,
    title VARCHAR2(100),
    content VARCHAR2(100),
    regdate DATE DEFAULT sysdate NOT NULL,
    CONSTRAINT info_pk PRIMARY KEY
    (
    id
    )
    ENABLE
);

CREATE TABLE auth (
    auth_id NUMBER NOT NULL,
    name VARCHAR2(30),
    job VARCHAR2(30),
    CONSTRAINT table1_pk PRIMARY KEY
    (
        auth_id
    )
    ENABLE
);
-- 시퀀스 생성
CREATE SEQUENCE seq_info;
CREATE SEQUENCE seq_auth;

INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'java', 'java is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'jsp', 'jsp is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'spring', 'spring is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'oracle', 'oracle is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'mysql', 'mysql is');             
INSERT INTO info(id, title, content) VALUES(SEQ_INFO.nextval, 'c', 'c is');             

INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '이경민', 'developer');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '홍길자', 'DBA');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '이순신', 'designer');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '고길동', 'scientist');
INSERT INTO auth(auth_id, name, job) values(SEQ_AUTH.nextval, '이경민', 'teacher');

SELECT * FROM info;
SELECT * FROM auth;

-- 컬럼 추가
ALTER TABLE info
ADD(auth_id NUMBER);

-- info 테이블의 auth_id에 auth테이블의 auth_id를 추가하는 작업.
UPDATE info
SET auth_id = 3           
WHERE id = 5;

update auth set auth_id=5 WHERE auth_id=30;

 

 

 

8_조인해보기2_0425.sql

--INNER JOIN : 내부 조인 
-- INNER JOIN에서 INNER 빼도, 기본적으로 INNER JOIN으로 동작
SELECT *
FROM info i JOIN auth a
ON i.auth_id=a.auth_id;

--오라클 문법(잘 사용 안합니다)
SELECT *
FROM info ,auth
WHERE info.auth_id=auth.auth_id;

--auth_id 컬럼을 그냥 쓰면 모호하다 라고 뜹니다.
--그 이유는 양쪽 테이블에 모두 존재하기 때문입니다.
--이럴 때 컬럼에 테이블 이름을 붙이던지, 별칭을 쓰셔서
--지목을 확실하게 해 주세요 .
SELECT  info.auth_id,title,content,name
FROM info INNER JOIN auth
ON info.auth_id=auth.auth_id;

--필요한 데이터만 조회하겠다 라고 한다면
--WHERE 구문을 통해 일반 조건을 걸어주시면 됩니다.
SELECT
    i.auth_id,i.title,i.content,
    a.name
FROM info i
INNER JOIN auth a
ON i.auth_id=a.auth_id
WHERE a.name='홍길자';


--외부조인 : OUTER JOIN  :빈 자리 null로 채워짐 
SELECT *
FROM info i LEFT OUTER JOIN auth a -- LEFT JOIN라고 해도 동작
ON i.auth_id=a.auth_id;

--오라클 문법
SELECT *
FROM info i,auth a
WHERE i.auth_id=a.auth_id(+);


SELECT *
FROM info i RIGHT OUTER JOIN auth a -- RIGHT JOIN라고 해도 동작
ON i.auth_id=a.auth_id;

--좌측 테이블과 우측테이블 데이터를 모두 읽어서 중복된 데이터는 삭제되는 외부 조인.
SELECT *
FROM info i FULL OUTER JOIN auth a -- FULL JOIN라고 해도 동작
ON i.auth_id=a.auth_id;

--CROSS JOIN 은 JOIN조건을 설정하지 않기 때문에
--모든 컬럼에 대해 JOIN을 진행합니다
--실제로는 거의 사용하지 않습니다.
SELECT *
FROM info 
CROSS JOIN auth
ORDER BY id ASC;

--여러 개 테이블 조인 -> 공통된 키 값만 찾아서 구문을 연결해서 쓰면 됩니다.
SELECT * 
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id=d.department_id
LEFT OUTER JOIN locations loc ON d.department_id=loc.location_id;


/*
-테이블 별칭 a , i를 이용하여 LEFT OUTTER JOIN 사용
-info, auth 테이블 사용
-job 컬럼이 scientis인 사람의 id,title,content,job을 출력.
*/
-- info  : id ,title , content, regdate ,auth_id
-- auth : auth_id,name,job
--주로 기준이 되는 테이블은 왼쪽에 작성.
SELECT 
    i.id,i.title,i.content,a.job
FROM auth a
LEFT OUTER JOIN info i  --scientis는 info에 존재하지 않기 때문에 
ON i.auth_id=a.auth_id  --기준을 적절히 설정해야 한다.
WHERE a.job='scientist';
--조인의 기준에 따라 결과가 달라질 수 있다.


--SELF JOIN : 조인 조건에 일치하는 조건 아니면 조회 대상에 포함되지않음(삭제됨)
--여기서 Steven삭제 되었음
SELECT 
    e1.employee_id,e1.first_name,e1.manager_id,
    e2.first_name,e2.employee_id
FROM 
    employees e1 
JOIN 
    employees e2 
ON e1.manager_id=e2.employee_id;

8_조인연습문제_0425.sql
--문제 1 
SELECT * FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;
SELECT * FROM employees e LEFT OUTER JOIN departments d ON e.department_id=d.department_id;
SELECT * FROM employees e RIGHT OUTER  JOIN departments d ON e.department_id=d.department_id;
SELECT * FROM employees e FULL OUTER JOIN departments d ON e.department_id=d.department_id;
--INNER 106 / LEFT 107 /RIGHT 122 / FULL 123


--문제 2
SELECT
    e.first_name ||e.last_name,d.department_id
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
WHERE e.employee_id=200;

--문제3
SELECT
    e.first_name,e.job_id,j.job_title
FROM employees e 
INNER JOIN jobs j 
ON e.job_id=j.job_id
ORDER BY e.first_name ASC;



--문제4 
SELECT *
FROM jobs LEFT JOIN job_history 
ON jobs.job_id=job_history.job_id;

--문제5
SELECT
    d.department_name
FROM employees e INNER JOIN departments d 
ON e.department_id=d.department_id
WHERE concat(e.first_name,e.last_name)='StevenKing';

--강사 문제 5번
SELECT 
    e.first_name || '' || e.last_name,
    d.deparment_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE e.first_name='Steven'
AND e.last_name='King';
--처음 데이터 접근할때에는 OUTTER JOIN으로 접근하는게 좋다.(NULL 값 들어가 있을 수 있어서)


--문제 6
SELECT *
FROM employees CROSS JOIN departments;


select * from departments;
select * from employees;
select * from jobs;
select * from job_history; 
select * from locations;

--문제 7
SELECT
    e.employee_id,e.first_name || ' '|| last_name,
    d.department_name,
    loc.country_id
FROM employees e 
INNER JOIN departments d 
ON e.department_id=d.department_id
INNER JOIN locations loc
ON d.location_id=loc.location_id
WHERE e.job_id='SA_MAN';

--문제 8
SELECT *
FROM employees e INNER JOIN jobs j ON e.job_id=j.job_id
WHERE j.job_title='Stock Manager' OR j.job_title='Stock Clerk'; 

--문제 9
SELECT *
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE d.manager_id iS NULL;

--문제 10
SELECT 
    e1.first_name AS 사원의이름,
    e2.first_name AS 사원의매니저이름
FROM 
    employees e1 
JOIN 
    employees e2 
ON e1.manager_id=e2.employee_id;


--문제 11
select * from employees;
-- employee_id ,first_name,last_name,email,phone,hire,
--job_id,salary,commision_pct,mager_id,department_id

SELECT 
    e1.first_name AS 사원의이름,
    e2.first_name AS 사원의매니저이름,e2.salary
FROM 
    employees e1 
LEFT JOIN 
    employees e2 
ON e1.manager_id=e2.employee_id
WHERE e2.manager_id is NOT NUll
ORDER BY e2.salary DESC;