OUTER JOIN, INNER JOIN, SUBQUERY (EXISTS, IN)

OUTER JOIN

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

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT  
FROM CUSTOM A,  COMPANY B 
ON A.USERID(+)=B.USERID -- (+)의 대각선 방향인 COMPANY가 기준이 되어 나옴 
ORDER BY B.COMPANY; -- 494개의 데이터가 나옴. COMPANY가 기준이니깐

 

---

INNER JOIN (EQUI JOIN)

• 두 개의 테이블을 하나로 결합하는 구문

SELECT A.USERID,USERNAME,JUMIN,ADDR1,COMPANY,DEPT,POSIT,PAY
FROM CUSTOM A INNER JOIN COMPANY B 
ON A.USERID = B.USERID;

--별칭을 쓰기로 했으면 나머지 부분(컬럼)들도 테이블명으로 쓰는게 아니라 별칭으로 무조건 고쳐줘야함
--별칭 줄 때, CUSTOM AS A 로 쓰면 에러 남!  테이블에 별칭을 쓸 때는 AS를 쓰면 안 되고 값만 써야 함

 

• 테이블 3개 이상 묶기

 SELECT A.USERID, A.USERNAME, B.COMPANY, B.DEPT, C.PRODUCT, C.LOGIN
 	FROM CUSTOM A 
    	INNER JOIN COMPANY B
		 ON A.USERID=B.USERID 
         INNER JOIN POINT C
	       ON A.USERID=C.USERID;

 

• 만약 조인 조건 컬럼이 두 테이블 모두 동일하다면 ON 대신 USING 절을 사용할 수 있다. 이때는 이 USING 내에 기술된 컬럼명을 테이블명.컬럼명형 이 아닌 컬럼명만 기술해야 한다.

 SELECT USERID, A.USERNAME, B.COMPANY, B.DEPT, C.PRODUCT, C.LOGIN
 	FROM CUSTOM A 
    	INNER JOIN COMPANY B
		USING (USERID);

---

그 외 JOIN들 (중요도 낮음)

SELF JOIN

하나의 테이블을 메모리상에 두번 로딩해놓은 다음에 그 두개를 JOIN 맺는다.

SELECT A.*
	FROM CUSTOM A, CUSTOM B
  WHERE A.USERNAME = B.USERNAME  --USERNAME에 동명이인이 있는지를 찾으려 한다
 ORDER BY A.USERNAME; --보기 쉽게끔 정렬

 

CROSS JOIN (카타시안 조인)

카타시안 조인을 ANSI 문법에서는 CROSS JOIN이라고 한다.

SELECT a.employee_id, b.department_id
  FROM employees a
 CROSS JOIN departments b;

 

---

특정 컬럼에 똑같은 값(중복되는 데이터가 있는지 없는지)을 찾아낼 때 사용하는 쿼리

USERNAME에 동명이인이 있는지를 찾으려 한다

SELECT DISTINCT A.*
FROM CUSTOM A, CUSTOM B
WHERE A.USERNAME = B.USERNAME 
AND A.USERID<>B.USERID
ORDER BY A.USERNAME;

 

같은 JUMIN을 가진 사람이 있는지를 찾으려 한다

SELECT DISTINCT A.*
FROM CUSTOM A, CUSTOM B
WHERE A.JUMIN = B.JUMIN 
AND A.USERID<>B.USERID
ORDER BY A.JUMIN;

 

제주도에 사는 사람 중 동명이인을 찾으려 한다

SELECT DISTINCT A.* -- 여기다 이어서 특정 컬럼 못 씀 
 FROM CUSTOM A, CUSTOM B
 WHERE A.ADDR1='제주도' AND B.ADDR1='제주도' 
 AND A.USERNAME = B.USERNAME
 AND A.USERID <> B.USERID
 ORDER BY A.USERNAME; 
 -- 제주도에 사는 사람 중에 동명이인이 다른 도에 있으면 같이 나와 버리므로
 -- A.ADDR1, B.ADDR1 둘다 조건에 줘야함 (공식)

 

 

---

SUBQUERY (IN, EXISTS 등..)

 

서브 쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인 방법으로, IN과 EXISTS 연산자를 사용한 조인이다.

똑같은 결과를 내놓는 쿼리를 EXISTS, IN 연산자를 이용해 각각 표현해보겠다.

 

(1) EXISTS 연산자 이용

SELECT department_id, department_name
  FROM departments a
 WHERE EXISTS(SELECT * FROM employees b
               WHERE a.department_id = b.department_id)
ORDER BY a.department_name;

 

(2) IN 연산자 이용

SELECT department_id, department_name
  FROM departments a
 WHERE a.department_id IN (SELECT b.department_id FROM employees b)
ORDER BY department_name;

 

둘의 차이를 보면, EXISTS 연산자는 두 테이블의 조인 조건을 서브쿼리에서 명시하였고

IN 연산자는 두 테이블의 조인조건이 서브쿼리 내에 없다.

-> IN 연산자는 OR 조건으로 변환할 수 있고(이것이거나 저것이거나), EXISTS 연산자는 조건에 만족하는 데이터가 한 건이라도 있으면 결과를 즉시 반환하기 때문에 이런 구조가 나오는 것이다. 

 

---

서브쿼리와 JOIN 차이점

 

4회이상 판매 기록이 있는 고객 정보 (HAVING) - 하위쿼리 이용

SELECT * FROM CUSTOM 
WHERE USERID IN (
SELECT USERID FROM SALES
GROUP BY USERID
HAVING COUNT(*) >= 4);

* 하위쿼리는 () 안에 있는 쿼리 자체로도 실행이 되야함.

 

 

 

4회이상 판매 기록이 있는 고객 정보 (HAVING) - JOIN문 이용

SELECT A.* -- USERID,USERNAME,JOB,CNT;
FROM CUSTOM A,
(SELECT USERID,COUNT(*) CNT FROM SALES
GROUP BY USERID
HAVING COUNT(*) >= 4) B
WHERE A.USERID=B.USERID;

SUBQUERY와 JOIN 다른 점: JOIN이 좀 더 데이터를 풍부하게 찾아낼 수 있음

 

 

 

 

ROWNUM 이용

SELECT * FROM (
SELECT ROWNUM,USERID,USERNAME FROM CUSTOM) 
WHERE ROWNUM<=10; 

* 웹에서 페이징 할 때(1,2,3,4,5) 많이 사용하는 쿼리

 

---

VIEW 란?

계정B가 A의 SALES를 접근할때

SALES가 VIEW라는 걸 모르고 TABLE인줄 알고 쓰게 됨
- 컬럼은 PNO,PNAME,PAY만 나오게 됨 (행제한이 걸려있는 채로 만들어진 VIEW이기 때문) -> 보안이 유지된다!

CREATE OR REPLACE VIEW SALES
AS 
SELECT PNO,PNAME,PAY FROM PERSONNEL WHERE JOB = 'SALESMAN';

- VIEW를 통해서 INSERT.UPDATE,DELETE 도 가능하다 (실제테이블에서도 I,U,D되는 것이다

 

 

VIEW 수정
- 보통 테이블을 만들때는 CREATE -> ALTER 이다.

VIEW 수정법은 CREATE OR REPLACE 이용.

(VIEW가 있으면 수정하고 없으면 만들어라)

 

 

부서번호가 10번인 VIEW를 만드는데, 이 범위를 수정해선 안되게끔 CK OPTION 걸어놓는다

CREATE OR REPLACE VIEW PER10_VU 
AS
SELECT * FROM PERSONNEL WHERE DNO=10 --절대 부서번호가 10번을 벗어나지 못하게끔 한게 CK OPTION
WITH CHECK OPTION CONSTRAINT DNO10_VU_CK;

 

아래와 같이 하면 CHECK OPTION이 걸려있으므로 에러가 뜬다

SELECT * FROM USER_VIEWS;
UPDATE PER10_VU SET DNO=20 WHERE PNO=1234;
-- 에러) 뷰의 WITH CHECK OPTION의 조건에 위배됩니다