2)Cartesian JOIN
- 모든 가능한 행들의 조합을 결과로 반환
- 조인조건이 없거나 잘못 정의된 경우
- 반드시 필요한 경우가 아니면 사용하지 말것
- ANSI의 CROSS JOIN 과 같은 JOIN
ex) A 테이블(100행 20열), B테이블(30행 10열)을 카타시안 조인하면 결과는(3000행 30열)
사용예)
SELECT 'CART' AS 테이블명, COUNT(*) AS 자료수
FROM CART
UNION
SELECT 'CART', COUNT(*)
FROM CART
UNION
SELECT 'BUYPROD', COUNT(*)
FROM BUYPROD
UNION
SELECT 'PROD', COUNT(*)
FROM PROD;
SELECT COUNT(*) FROM BUYPROD,CART,PROD;
(ANSI-CROSS JOIN)
SELECT COUNT(*)
FROM BUYPROD
CROSS JOIN CART
CROSS JOIN PROD;
3) SELF JOIN
하나의 테이블에 2개이상의 별칭을 부여하여 자신의 테이블 사이에 발생되는 조인
사용예) 사원테이블에서 사원번호 120번/ 사원의 급여보다 더 많은 급여를 받는 사원의
사원번호, 사원명, 부서번호, 부서명, 급여를 조회하시오
SELECT C.EMPLOYEE_ID AS 사원번호,
C.EMP_NAME AS 사원명,
C.DEPARTMENT_ID AS 부서번호, --C도되고 B도됨
B.DEPARTMENT_NAME AS 부서명,
C.SALARY AS 급여
FROM HR.EMP A, DEPT B, HR.EMP C
WHERE A.EMPLOYEE_ID=120
AND A.SALARY<=C.SALARY --조인조건
AND C.DEPARTMENT_ID= B.DEPARTMENT_ID --조인조건
ORDER BY 3, 5 DESC;
4) Non-Equi Join
- 조인조건절에 동등 연산자(=)가 아닌 다른 연산자가 사용되는 경우
- 부등호, IN, ANY 등의 연산자가 사용되는 경우
사용예) 사원테이블에서 30번 부서의 관리자보다 입사일이 빠른 사원정보룰 조회하시오
Alias는 사원번호, 사원명, 직책명, 입사일
-- 비식별관계(점선):
-- 식별자관계(직선): 부모테이블이 없어지면 자식테이블의 존립이유없는것ex. 결제테이블과 주문테이블 결제가 취소되면 주문도 없음
SELECT C.EMPLOYEE_ID AS 사원번호,
C.EMP_NAME AS 사원명,
B.JOB_TITLE AS 직책명,
C.HIRE_DATE AS 입사일
FROM HR.EMP A,-- 30번 부서의 관리자 한사람에 대한 정보
HR.JOBS B,
HR.EMP C, -- 사원
HR.DEPT D
WHERE D.DEPARTMENT_ID=30
AND A.EMPLOYEE_ID=D.MANAGER_ID
-- EMP테이블에서 MANAGER_ID가 EMPLOYEE_ID의 부모
AND C.HIRE_DATE<=A.HIRE_DATE
--해당하는 사람 여러명 있을텐데
AND A.DEPARTMENT_ID=C.DEPARTMENT_ID
-- A.그 사람들중에서도 = C.30번 부서에 속한사람들
AND C.JOB_ID=B.JOB_ID
ORDER BY 4;
사용예) 회원테이블에서 40대회원(서브쿼리)의 평균마일리지보다 많은 마일리지를 보유한 회원정보를 조회하시오
Alias는 회원번호, 회원명, 연령대, 마일리지
```
(서브쿼리: 40대회원의 평균마일리지)
SELECT ROUND(AVG(MEM_MILEAGE))
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
-- TRUNC(27,-1) = 20 첫째자리에서 모두 버리시오
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대,
A.MEM_MILEAGE AS 마일리지
FROM MEMBER A, (SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40)B
WHERE B.AMILE <= A.MEM_MILEAGE; --조인조건
(ANSI JOIN)
SELECT ROUND(AVG(MEM_MILEAGE))
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
-- TRUNC(27,-1) = 20
첫째자리에서 모두 버리시오
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대,
A.MEM_MILEAGE AS 마일리지
FROM MEMBER A, (SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40)B
WHERE B.AMILE <= A.MEM_MILEAGE; --조인조건
(조인문을 쓰지 않은경우 - 속도 더느림)
SELECT ROUND(AVG(MEM_MILEAGE))
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40;
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM A.MEM_BIR),-1) AS 연령대,
A.MEM_MILEAGE AS 마일리지
FROM MEMBER A
WHERE A.MEM_MILEAGE>=(SELECT ROUND(AVG(MEM_MILEAGE))AS AMILE
FROM MEMBER
WHERE TRUNC(EXTRACT(YEAR FROM SYSDATE)
- EXTRACT(YEAR FROM MEM_BIR),-1) = 40);