Oracle 4일차 : 조인, 제약조건, 서브쿼리

조인

둘 이상의 테이블을 연결하여 데이타를 검색하는 방법

오라클 조인

종류	설명
outer join	조인 조건에 일치하는 데이타만 출력하는 경우가 inner join인데(아래) 조건에 일치하지 않아도 데이타를 출력해야 하는 경우 outer join을 사용한다(+)는 조인할 데이타가 없는쪽 where문 끝에 해주면 상대방 테이블의 모든 데이타가 출력된다
self join	자신의 테이블과 조인 / equi join과 같으나 테이블을 두개로 만들어 조인함from emp a, emp b
equi join	기본키(primary key)와 외부키(foreign key)를 사용하여 조인/반드시 일치할때만 조회from emp e, dept d where 테이블명1.컬럼명 = 테이블명2.컬럼명
non-equi join	두 테이블 간 일치하는 컬럼은 없으나 조인이 필요할 때/동등연산자(=)를 제외한 관계연산자 사용from emp e, dept d

ANSI 조인

ms-sql, db2 같은 오라클이 아닌 환경에서도 사용 가능한 표준화된 조인
cross join, natural join, using, outer join, self join, …

🐣 실습하기

SELECT last_name, 
       employees.department_id 
FROM   employees, 
       departments 
WHERE  employees.department_id = departments.department_id; 
-- 두 테이블에 공통으로 들어간 컬럼명은 그냥 쓰면 모호성 때문에 오류가 난다. 
-- 반드시 테이블명.컬럼명 과 같은 형태로 적어줄것.(테이블명이 길다면 별칭을 주자)
	
--myscott 계정에서 emp와 dept 테이블을 이용해서 조인, ename, job, deptno, dname, loc 출력
SELECT ename, 
       job, 
       emp.deptno, 
       dname, 
       loc 
FROM   emp, 
       dept 
WHERE  emp.deptno = dept.deptno; 

제약조건 설정하기

제약조건에 대한 이름을 준다. 추후 에러발생시 오류 파악이 쉬움
처음 생성 시 추가해도 되고 테이블을 만든 후 alter table로 추가해도 됨

-- 현재 테이블에 지정된 제약조건 확인
SELECT constraint_name 
FROM   user_constraints 
WHERE  table_name = 'CART'; 
-- sys_xxxx 이런 형태는 제약조건 이름을 안줬을 때 자동으로 생성되는 임시이름임

-- 방식1. 제약조건 추가하기
-- 제약 조건을 새로 추가하려면 이미 임시로 추가된 제약조건을 삭제 후 추가한다
ALTER TABLE cart 
  DROP CONSTRAINT sys_c008437; 

-- cart의 cart_num을 기본키로 설정
ALTER TABLE cart 
  ADD CONSTRAINT pk_cart_num PRIMARY KEY(cart_num); 
		
-- cart의 num을 shop.num의 외부키로 설정
ALTER TABLE cart 
  ADD CONSTRAINT fk_cart_num FOREIGN KEY(num) REFERENCES shop(num); 
		
--cart의 user_id을 client.user_id의 외부키로 설정
ALTER TABLE cart 
  ADD CONSTRAINT fk_cart_user_id FOREIGN KEY(user_id) REFERENCES client(user_id) 
; 

-- 방식2. 처음부터 주기
-- Primary key constraint
CREATE TABLE test01 
  ( 
     num NUMBER(5), 
     CONSTRAINT pk_num PRIMARY KEY(num) 
  ); 

CREATE TABLE test02 
  ( 
     num NUMBER(5) CONSTRAINT pk2_num PRIMARY KEY 
  ); 
-- constraint는 뒤에서 따로 한꺼번에 잡아줘도 되고, 컬럼 줄때마다 하나하나 줘도 됨

-- Unique constraint
CREATE TABLE test03 
  ( 
     id VARCHAR2(20) CONSTRAINT uk_id UNIQUE 
  ); 

CREATE TABLE test04 
  ( 
     id VARCHAR2(20), 
     CONSTRAINT uk2_id UNIQUE(id) 
  ); 
	
-- Check constraint
CREATE TABLE test05 
  ( 
     gender VARCHAR2(10) CONSTRAINT ck_gender CHECK (gender IN ('남자', 
     '여자')) 
  ); 
	
-- Foreign key
CREATE TABLE test07 
             ( 
                          num NUMBER(5) CONSTRAINT fk_num REFERENCES test01(num) 
             );
CREATE TABLE test07 
             ( 
                          num NUMBER(5), 
                          CONSTRAINT fk2_num foriegn KEY(num) REFERENCES test02(num) 
             );

서브쿼리

하나의 select 만으로는 원하는 결과를 얻을 수 없을 때 서브쿼리를 실행한다

SELECT select_list FROM 테이블1 WHERE 컬럼명 연산자 (SELECT select_list FROM 테이블2);

🐣 실습하기

-- 연봉이 Whelen보다 높은 사람
SELECT * 
FROM   employees 
WHERE  salary >= (SELECT salary 
                  FROM   employees 
                  WHERE  last_name = 'Whalen'); 

-- 연봉이 평균보다 높은 사람
SELECT last_name, 
       salary 
FROM   employees 
WHERE  salary >= (SELECT Avg(salary) 
                  FROM   employees); 

-- [부서번호가 100번인 사람 중 최고연봉을 받는 사람]과 같은 연봉을 가진 사람(부서번호 상관없음)의 명단 구하기
SELECT last_name, 
       salary 
FROM   employees 
WHERE  salary = (SELECT Max(salary) 
                 FROM   employees 
                 WHERE  department_id = 100); 

-- 100번 부서의 최대 월급보다 많은 모든 부서 정보를 출력하는 SQL작성
SELECT department_id, 
       Min(salary) 
FROM   employees 
GROUP  BY department_id 
HAVING Min(salary) > (SELECT Max(salary) 
                      FROM   employees 
                      WHERE  department_id = 100); 

복수행 서브쿼리

1. IN

해당 서브쿼리 내용에 포함되는 데이타를 출력

-- 부서별 최고급여에 해당하는 급여를 받는 사원의 정보 출력
SELECT last_name, 
       salary, 
       department_id 
FROM   employees 
WHERE  salary IN (SELECT Max(salary) 
                  FROM   employees 
                  GROUP  BY department_id); 
	
-- Whalen, Fay의 연봉과 같은 연봉을 받는 사원 목록 출력
SELECT last_name, 
       salary 
FROM   employees 
WHERE  salary IN (SELECT salary 
                  FROM   employees 
                  WHERE  last_name IN ( 'Whalen', 'Fay' )); 
	
-- 부서별 연봉왕
SELECT last_name, 
       salary, 
       department_id 
FROM   employees 
WHERE  ( department_id, salary ) IN (SELECT department_id, 
                                            Max(salary) 
                                     FROM   employees 
                                     GROUP  BY department_id) 
ORDER  BY department_id; 

2. ALL

서브쿼리의 결과 모두와 조건이 맞는것만 출력

-- IT_PROG의 그 어떤 사원보다 월급이 작은 사원 목록 출력
SELECT last_name, 
       department_id, 
       salary 
FROM   employees 
WHERE  salary < ALL (SELECT salary 
                     FROM   employees 
                     WHERE  job_id = 'IT_PROG'); 

3. ANY

서브쿼리의 결과 중 어느 하나와 조건이 맞아도 출력

4. EXISTS

서브쿼리의 결과값이 테이블에 존재하는지 여부에 따라 결과를 출력

-- 서브쿼리 내용은 참거짓만 출력하므로 select 뒤에 컬럼명은 사실 뭐가와도 상관없다
-- IF문 같은건가?
SELECT last_name, 
       department_id, 
       salary 
FROM   employees 
WHERE  EXISTS (SELECT employee_id 
               FROM   employees 
               WHERE  commission_pct IS NOT NULL); 

-- 커미션 받는 사원이 하나라도 있다면 메인 쿼리가 실행됨

💡SQL 문 가독성 좋게 포맷팅 해주는 사이트

Twitter Facebook LinkedIn

Jenny Lee

Oracle 4일차 : 조인, 제약조건, 서브쿼리

조인

오라클 조인

ANSI 조인

🐣 실습하기

제약조건 설정하기

서브쿼리

🐣 실습하기

복수행 서브쿼리

1. IN

2. ALL

3. ANY

4. EXISTS

Comments

You May Also Enjoy

[LeetCode] 1074 Number of Submatrices That Sum to Target

[CodeJam] Qualification Round 2020

[LeetCode] 4 Median of Two Sorted Arrays

[LeetCode] 3 Longest Substring Without Repeating Characters