[오라클] JOIN, OPERATOR

JOIN : 2개이상의 테이블에서 데이터 검색하는 방법

1. INNER JOIN(EQUI JOIN)

• 가장 일반적으로 사용하는 Equality Condition(=)에 의한 조인이다.
• Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

2. NON-EQUI JOIN(오라클에만)

• 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 조인조건은 동등(=)이외의 연산자를 갖는다.
• BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN
• 거의 사용하지 않는다

3. OUTER JOIN

• Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 데이터를 반환하지 못한다.
• 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용.
• Outer Join 연산자는 "(+)" 이다.
• 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.
• Outer Join 연산자는 표현식의 한 편에만 올 수 있다.

4. CROSS JOIN

• 많은 양의 데이터가 필요할 때 사용한다. 특별한 이유는 없다.
• 두 테이블의 데이터값을 곱해준 양이 생성된다.

5. SELF JOIN

• Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다.
• 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.
• alias : 명령어

---

 

 

- INNER JOIN(EQUI JOIN)  

--EQUI JOIN(동등조인)
SELECT 
A.USERID,USERNAME,AGE,JOB, 
B.USERID,COMPANY,POSIT,PAY 
FROM CUSTOM A,COMPANY B
WHERE A.USERID = B.USERID ; 

--INNER JOIN 
SELECT 
A.USERID,USERNAME,AGE,JOB, 
B.USERID,COMPANY,POSIT,PAY 
FROM CUSTOM A INNER JOIN COMPANY B --이렇게 작성하는게 표준
ON A.USERID = B.USERID 
WHERE A.USERID = '953075'; 

SELECT 
A.USERID,USERNAME,AGE,JOB, 
B.USERID,COMPANY,POSIT,PAY 
FROM CUSTOM A JOIN COMPANY B	   --INNER를 생략해도 문제는 없음
ON A.USERID = B.USERID AND A.USERID = '953075'; 

--A.USERID = '953075' 조건을 AND 나 WHERE로 추가해줘도 문제없음

--오라클에서의 조건문은 총 3개. WHERE, GROUP BY ~ HAVING, JOIN ~ ON

--CUSTOM 테이블과 POINT테이블에서 데이터 출력
SELECT * FROM CUSTOM;
SELECT * FROM POINT;

 

- OUTER JOIN

--OUTER JOIN(외부조인)
--어느 테이블이 기준이냐에 따라서 데이터의 수가 달라짐
SELECT COUNT(*) FROM CUSTOM; --494
SELECT COUNT(*) FROM COMPANY; --490

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT
FROM CUSTOM A, COMPANY B
WHERE A.USERID(+) = B.USERID; --(+)의 대각선에 있는 B기준

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT
FROM CUSTOM A, COMPANY B
WHERE A.USERID = B.USERID(+) AND COMPANY IS NULL;--(+)의 대각선에 있는 A기준

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT
FROM CUSTOM A RIGHT OUTER JOIN COMPANY B
ON A.USERID = B.USERID; --490(COMPANY기준)

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT
FROM CUSTOM A LEFT OUTER JOIN COMPANY B
ON A.USERID = B.USERID; --494(CUSTOM기준)

 

 

- CROSS JOIN

--CROSS JOIN(상호조인)
--많은 양의 데이터가 필요할 때 사용. 더미 데이터를 만들 때 활용 가능할 듯.
--490*496  (두 테이블의 데이터값을 곱해준 양이 생성)
SELECT
A.USERID,USERNAME,AGE,JOB,
B.USERID,DEPT,POSIT,PAY
FROM CUSTOM A, COMPANY B;

SELECT
A.USERID,USERNAME,AGE,JOB,
B.USERID,DEPT,POSIT,PAY
FROM CUSTOM A CROSS JOIN COMPANY B;

SELECT
A.USERID,USERNAME,AGE,JOB,
B.USERID,DEPT,POSIT,PAY
FROM CUSTOM A CROSS JOIN COMPANY B
WHERE A.USERID = B.USERID;--CROSS JOIN에 이 조건문을 작성하면 INNER JOIN으로 바뀐다

 

 

- SELF JOIN

--SELF JOIN
--자기자신과의 조인
--동명이인을 검색하는 경우 
SELECT DISTINCT A.* FROM CUSTOM A, CUSTOM B
WHERE A.USERNAME = B.USERNAME --같은컬럼 중복하는 내용 설정
AND A.USERID <> B.USERID --아이디가 같으면 동일인물
ORDER BY A.USERNAME;

--CUSTOM 테이블에서 둘 다 제주도에 살고있는 사람중에 동명이인을 검색
SELECT DISTINCT A.* FROM CUSTOM A, CUSTOM B
WHERE A.USERNAME = B.USERNAME 
AND A.USERID <> B.USERID 
AND A.ADDR1 = '제주도' AND B.ADDR1 = '제주도';

--SELF JOIN
--자기자신과의 조인
--동명이인을 검색
SELECT DISTINCT A.* FROM CUSTOM A, CUSTOM B
WHERE A.USERNAME = B.USERNAME --같은컬럼 중복하는 내용 설정
AND A.USERID <> B.USERID --아이디가 같으면 동일인물
ORDER BY A.USERNAME;

 

- NON-EQUI JOIN

 

- 3개 이상의 테이블 조인(CUSTOM테이블- COMPANY테이블 - POINT테이블)

--3개 이상의 테이블 조인(CUSTOM테이블, COMPANY테이블, POINT테이블)
SELECT A.USERID,USERNAME,COMPANY,DEPT,PRODUCT,LOGIN
FROM CUSTOM A, COMPANY B, POINT C
WHERE A.USERID = B.USERID AND A.USERID = C.USERID;

SELECT A.USERID,USERNAME,COMPANY,DEPT,PRODUCT,LOGIN
FROM CUSTOM A INNER JOIN COMPANY B
ON A.USERID = B.USERID INNER JOIN POINT C --테이블A와 B를 INNER JOIN 한 결과테이블에 C와 INNER JOIN 진행
ON A.USERID = C.USERID;

 

- 파생테이블과 조인(일반적으로 많이 사용 파생테이블-파생테이블을 조인하는 경우가 태반이기 때문)

SELECT * FROM SALES ORDER BY USERID;

SELECT A.USERID,USERNAME,AGE,ADDR1,NCOUNT,HAP
FROM CUSTOM A,
(SELECT USERID,COUNT(*) NCOUNT, SUM(PRICE) HAP FROM SALES GROUP BY USERID HAVING SUM(PRICE)>1600000) B
WHERE A.USERID = B.USERID;

--160만원 이상 구매한 고객의 아이디별 구매횟수와 총구매액 조회
SELECT USERID,COUNT(*) NCOUNT, SUM(PRICE) HAP FROM SALES GROUP BY USERID HAVING SUM(PRICE)>1600000;

 

- OPERATOR

A=[1,2,5,6], B=[1,2,4] 두 집합이 존재할 때 

중복포함 합집합: A UNION ALL B = [1,2,5,6,1,2,4]

합집합              : A UNION B = [1,2,5,6,4]       

차집합              : A MINUS B = [5,6]         

교집합              : A INTERSECT B = [1,2]  

--UNION/ALL
SELECT * FROM PERSONNEL UNION ALL SELECT * FROM PERSONNEL;

--UNION
SELECT * FROM PERSONNEL UNION SELECT * FROM PERSONNEL;

--MINUS
--DIVISION.DNO = 10,20,30,40, PERSONNEL.DNO = 10,20,30
SELECT DNO FROM DIVISION MINUS SELECT DNO FROM PERSONNEL; --40

--INTERSECT
SELECT DNO FROM DIVISION INTERSECT SELECT DNO FROM PERSONNEL; --10,20,30

--데이터 복사하여 테이블 생성가능
CREATE TABLE SEOUL
AS 
SELECT * FROM CUSTOM WHERE ADDR1='서울특별시';

CREATE TABLE JEJU
AS 
SELECT * FROM CUSTOM WHERE ADDR1='제주도';

CREATE TABLE KKI
AS 
SELECT * FROM CUSTOM WHERE ADDR1='경기도';

--UNION 사용시 첫번째 컬럼 기준으로 정렬되어 나옴
SELECT * FROM SEOUL
UNION
SELECT * FROM JEJU
UNION
SELECT * FROM KKI;

 

 

---

 

인터넷 쇼핑몰에서의 카테고리 브랜드, 패션, 유아동, 식품 이 구분 하나하나마다 데이터베이스 서버가 있다.
우리가 보기엔 하나의 서버지만 각각의 DB서버가 존재하고 클러스터로 묶여있는 것이다. 가장 많이 쓰는 서버가 AS400. MIPS.. 등 등이 있다. 우리가 아는 일반적인 인텔 칩이 아니라 서버 전용이라 생각하면 된다.