JOIN

• 조인이란 ? 하나 이상의 테이블에서 원하는 행과 열을 참조하는 select문이다. 두개의 테이블 간 조인을 한 경우 공통칼럼이 있어야 한다.
• 두개만 조인가능하다. 여러개 조인 하려면 두개씩 조인해야 한다.

조인에는 여러가지 종류가 있다.

• cross join : 두개의 테이블 간 조인으로 한 테이블의 모든 행이 다른 테이블에 행과 각각 1:1 대응한다.
• 실행 총 결과는 두 테이블 간 행을 곱하기한 개수가 된다.
• cross join은 잘 사용하지 않음

SELECT jikwon_name, buser_name FROM jikwon,buser; -- 직원 한명마다 모든 부서가 나옴
SELECT jikwon_name, buser_name FROM jikwon cross join buser; -- 위와 같은 방법

SELECT a.jikwon_name, b.jikwon_jik FROM jikwon a, jikwonb; -- self 조인 (cross join의 일종)

• NON-EQUI JOIN : 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다.
• Non EQUI JOIN의 경우에는 “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행하는 것이다. 두 개의 테이블이 PK-FK로 연관관계를 가지거나 논리적으로 같은 값이 존재하는 경우에는 “=” 연산자를 이용하여 EQUI JOIN을 사용한다. 그러나 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에는 EQUI JOIN을 사용할 수 없다. 이런 경우 Non EQUI JOIN을 시도할 수 있으나 데이터 모델에 따라서 Non EQUI JOIN이 불가능한 경우도 있다.
  출처 : https://devfunny.tistory.com/316

먼저 조인을 하기 위해 테이블을 하나 만들어 주었다.

CREATE TABLE paygrade(grade INT PRIMARY KEY, lpay INT, hpay INT);
INSERT INTO paygrade VALUES(1,0,1999);
INSERT INTO paygrade VALUES(2,2000,2999);
INSERT INTO paygrade VALUES(3,3000,3999);
INSERT INTO paygrade VALUES(4,4000,4999);
INSERT INTO paygrade VALUES(5,5000,5999);

SELECT j.jikwon_name,j.jikwon_pay, p.grade FROM jikwon j, paygrade p
WHERE j.jikwon_pay >= p.lpay AND j.jikwon_pay <= p.hpay ; -- j,p는 테이블의 별명

• EQUI JOIN : 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법이다.
• 대부분 PK ↔ FK의 관계를 기반으로 한다. 그러나 일반적으로 테이블 설계 시에 나타난 PK ↔ FK의 관계를 이용하는 것이지 반드시 PK ↔ FK의 관계로만 EQUI JOIN이 성립하는 것은 아니다.이 기능은 계층형(Hierarchical)이나 망형(Network) 데이터베이스와 비교해서 관계형 데이터베이스의 큰 장점이다. JOIN의 조건은 WHERE 절에 기술하게 되는데 “=” 연산자를 사용해서 표현한다.
• EQUI JOIN vs NON-EQUI JOIN 차이점 : 둘의 차이점은 두개의 테이블 간에 칼럼 값이 일치하는가 일치하지 않는가로 나눌 수 있다. 또한 EQUI JOIN은 where절에 "=" 연산자를 사용하고 Non EQUI JOIN은 "=" 연산자가 아닌 다른 연산자를 사용한다.

SELECT FROM jikwon WHERE jikwon_no=1;
SELECT FROM buser WHERE buser_no=10; -- 직원 테이블의 buser_num과 부서 테이블의 buser_no은 공통칼럼
SELECT jikwon_name, buser_name FROM jikwon,buser WHERE buser_num = buser_no;
SELECT test.jikwon.jikwon_name, test.buser.buser_name FROM jikwon,buser WHERE jikwon.buser_num = buser.buser_no;
SELECT jikwon_no, jikwon_name, jikwon_jik, buser_name FROM jikwon, buser WHERE buser_num = buser_no;
-- 마지막 문장 출력

• inner join : 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.

• 조건에 맞는 자료가 한 테이블에만 있는 경우에는 출력에 참여하지 않는다.

• 방법1)

SELECT jikwon_no, jikwon_name, buser_name FROM jikwon, buser WHERE buser_num=buser_no; -- 6번 직원은 부서가 없기 때문에 제외 / oracle join 여기서도 가능하긴 함
SELECT jikwon_no, jikwon_name, buser_name FROM jikwon, buser WHERE buser_num=buser_no AND jikwon_gen = '남';

• 방법2)

SELECT jikwon_no, jikwon_name, buser_name FROM jikwon INNER JOIN buser ON buser_num=buser_no; -- ANSI JOIN (표준)
SELECT jikwon_no, jikwon_name, buser_name FROM jikwon INNER JOIN buser ON buser_num=buser_no WHERE jikwon_gen = '남';

inner join 출력

null인 값은 빼고 출력

• outer join : 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
• left outer join

SELECT jikwon_no, jikwon_name, buser_name FROM jikwon, buser WHERE buser_num=buser_no(+); -- oracle 에서만 쓸 수 있음
SELECT jikwon_no, jikwon_name, buser_name FROM jikwon LEFT OUTER JOIN buser ON buser_num=buser_no; -- ANSI JOIN (표준)

-- 오른쪽이 null인 값은 그냥 출력

• right outer join

SELECT jikwon_no, jikwon_name, buser_name FROM jikwon, buser WHERE buser_num(+)=buser_no; -- oracle 에서만 쓸 수 있음
SELECT jikwon_no, jikwon_name, buser_name FROM jikwon RIGHT OUTER JOIN buser ON buser_num=buser_no; -- ANSI JOIN (표준)

-- 왼쪽이 null인 값은 그냥 출력

• full outer join : Mariadb(mysql)은 full outer join 지원 x

SELECT jikwon_no, jikwon_name, buser_name FROM jikwon FULL OUTER JOIN buser ON buser_num=buser_no; -- oracle join
-- full outer join은 union을 이용
SELECT jikwon_no, jikwon_name, buser_name FROM jikwon LEFT OUTER JOIN buser ON buser_num=buser_no
UNION SELECT jikwon_no, jikwon_name, buser_name FROM jikwon RIGHT OUTER JOIN buser ON buser_num=buser_no;

• 활용

-- 각 부서 내 근무자 목록 (부서가없는 직원은 제외)
SELECT buser_name, jikwon_name, jikwon_jik, buser_tel FROM jikwon INNER JOIN buser ON jikwon.buser_num=buser.buser_no ORDER BY buser_name asc;
SELECT FROM gogoek WHERE gogek_no = 1; -- gogek.gogek_dasamo 와jikwon.jikwon_no 공통칼럼
-- 관리 고객이 있는 직원만 출럭
SELECT jikwon_no, jikwon_name, jikwon_jik, gogek_name, gogek_tel FROM jikwon INNER JOIN gogek ON jikwon.jikwon_no=gogek.gogek_damsano ORDER BY jikwon_name;
-- 관리 고객에 상관없이 모든 직원 출력
SELECT jikwon_no, jikwon_name, jikwon_jik, gogek_name, gogek_tel FROM jikwon left OUTER join gogek
on jikwon.jikwon_no = gogek.gogek_damsano ORDER BY jikwon_name;
-- 부서별 급여합, 급여 평균, 인원수(부서가 없는 직원은 계약직으로 출력)
SELECT nvl(buser_name, '계약직') AS 부서, SUM(jikwon_pay) AS 합, AVG(jikwon_pay) AS 평균,
COUNT() AS 인원수 FROM jikwon LEFT OUTER JOIN buser
ON jikwon.buser_num = buser.buser_no GROUP BY 부서;