2023.10.16+ sql cross join 과 inner join

/*
 * 2023-10-16
 * 두개이상의 테이블에 대한 쿼리
 * - 조인(join)과 서브쿼리(Subquery)
 * */

-- 조인(join)
-- 1. CROSS JOIN (합집합 : *) <--> CARTESIAN JOIN  실시간 작업에서는 되도록 피하도록 한다.
-- 2. INNER JOIN (교집합) <--> EQUI-JOIN
-- 3. OUTER JOIN(교집합+교집합에 제외된 데이터) <--> OUTER JOIN
-- 4. SELF JOIN <--> SELF JOIN --> Subquery로 처리되는 경우가 많음
/*
 * A - 빨강색구슬, 노랑색구슬
 * B - 파랑색구슬, 보라색구슬,  + 빨강색구슬
 * A,B에 담긴 전체구슬 --> 빨,노,파,보 구슬 : 4개(2*2) --->
 * A,B에 담긴 구슬 중 같은 색깔의 구슬만 조회 : 빨강색
 * A,B에 담긴 구슬 중 같은 색깔 구슬은 1씩 꺼내고 , 다른 색깔도 함께 꺼냄 , B 주머니 에서만 
--> 빨강색(1),파랑색 , 보라 
 * */

-- hrdb2019 데이터 베이스의 모든 테이블 조회
-- 형식 : select 컬럼명 from information_schema.tables
-- where table_schema = '데이터베이스명'

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019';

DESC department;

DESC employee 

DESC unit 

DESC vacation 




/*
 * ERD
 * 부서(dept_id:pk) <---사원(dept_id:FK)
 * 사원은 하나 이상의 부서에 반드시 포함된다.
 * 
 * 한 학생은 하나의 과목을 바드시 수강해야한다.
 * 과목(sub_id : PK) <--- 학생(sub_id:FK)
 * 
 * 한명의 고객은 하나이상의 상품을 주문 할 수 있다
 * 고객() <--- 주문() <--- 상품()
 * 
 * Unit(unit:id pk) <-- department(unit_id FK)
 * 
 * Employee(emp_id:PK) <--- Vacation(emp_id:FK)
 * */

-- 정보시스템 부서에 속한 홍길동 사원이 사용한 휴가일수를 조회
department <--> employee <--> vacation

-- 1. CROSS 조인 : 테이블 * 테이블 ...
-- 형식 : select * from 테이블 join 테이블
--  employee 테이블과 dept 테이블을 cross 조인

SELECT *  FROM employee JOIN department; 
SELECT *  FROM department JOIN employee; -- 20*7 = 140
SELECT  count(*) FROM employee;
SELECT count(*) FROM department; 



-- 워크벤치 툴에서 데이터베이스 기준 ERD 생성
-- Database > REverser Engineering .. 메뉴 선택 후 next...




-- department 부서와 vacation 부서를 조인
SELECT count(*) FROM department; -- 7
SELECT  count(*) FROM vacation; -- 102

SELECT  count(*) FROM department JOIN vacation; -- 714
SELECT  count(*) FROM department, vacation; -- 714
-- department 부서와 vacation부서 employee 를 조인

SELECT  count(*) FROM department join vacation JOIN employee; -- 14280

SELECT  count(*) FROM department, vacation,employee; -- oracle 형식 14280

SELECT count(*)
FROM (SELECT  * FROM department JOIN vacation) AS a
JOIN 
(SELECT  * FROM department JOIN vacation) AS b;

-- 2. INNER 조인 : 테이블간의 기본키와 참조관계가 정의된 경우 사용
-- 형식 : select * from 테이블명1 INNER JOIN 테이블명2
--       Where 테이블1.기본키(참조키) ON 테이블2.참조키(기본키)
-- 오라클 형식 : SELECT * FROM 테이블1,테이블2 WHERE 테이블1.기본키 = 테이블2.참조키
-- employee 테이블과 departmetn 테이블을 INNER 조인

SELECT *  FROM employee e INNER JOIN department d on e.dept_id = d.dept_id;

SELECT count(*) FROM employee e ,department d WHERE d.dept_id = e.dept_id; 


-- 사원아이디,사원명,부서아이디, 부서명, 부서생성 날짜를 조회

SELECT  emp_id,d.dept_id ,emp_name,dept_name,start_date  FROM employee e INNER JOIN department d 
ON e.dept_id = d.dept_id
ORDER BY emp_id;


-- 홍길동 사원의 속한 부서의 이름과 부서id 입사일 ,연봉을 조회 

SELECT d.dept_name  ,d.dept_id,e.hire_date ,e.salary  FROM department d INNER JOIN employee e ON d.dept_id  = e.dept_id and  e.emp_name = '홍길동'; 

-- 영업부에 속해 있는 사원들의 입사일과 연봉, 사원명을 조회
SELECT e.hire_date ,e.salary ,e.emp_name,d.dept_name   
FROM employee e INNER JOIN department d
ON d.dept_id = e.dept_id   --  이부분 빠지면 카테이션 조인이랑 같아짐
AND d.dept_name ='영업' ;

-- 인사과에 속한 모든 사원의 정보를 조회
SELECT e.* FROM employee e INNER JOIN department d 
ON e.dept_id = d.dept_id 
AND d.dept_name = '인사';

-- 인사과에 속한 사원들 중에 휴가를 사용한 사원들의 리스트를 모두 조회

SELECT * 
FROM employee e INNER JOIN department d INNER JOIN vacation v 
ON e.dept_id = d.dept_id 
and e.emp_id = v.emp_id
AND d.dept_name = '인사';


SELECT * 
FROM employee e ,department d , vacation v 
WHERE  e.dept_id = d.dept_id 
and e.emp_id = v.emp_id
AND d.dept_name = '인사';

-- 인사과에 속한 사원들 중에 휴가를 사용한 사원들별로 휴가 사용 횟수 출력(서브쿼리)


-- 휴가 사용 이유가 '두통'인 사원들 중에 영업부서인 사원의 사원명 부서명 휴가사용 폰번호 이유 조회
-- 영업 부서가 속한 본부를 추가로 조회

SELECT e.emp_name ,d.dept_name , v.duration , e.phone , v.reason , u.unit_name 
FROM employee e INNER join department d INNER join vacation v INNER JOIN unit u
on  e.dept_id = d.dept_id
and e.emp_id = v.emp_id
AND u.unit_id  = d.unit_id 
AND v.reason = '두통'
AND d.dept_name = '영업';
-- 마지막 조건 둘 중 하나는 sql optimaizer 가 자체적으로 실행한다.