카티션 곱(Cartesian product)
- 모든 가능한 행들의 조인
- 검색하고자 한 데이터 이외 조인엔 사용된 테이블들의 모든 데이터가 반환 //특별한 가치는 없다.
위와 같이 모든 행들의 조인이 일어나 사원 하나에 다수의 부서가 match됨을 확인할 수 있다.
조인(Join)
- 하나 이상의 테이블을 연결하여 데이터를 검색하는 방법
- 종류
1) 동등조인=내부조인(equi join)
: 조인 조건에서 '='을 사용해 값들이 정확하게 일치하는 경우에 사용되는 조인 //대부분 기본키와 외래키의 관계를 이용
ex) 급여가 3000 이상인 사원에 대해 사원번호, 사원이름, 업무, 부서명, 급여, 위치를 검색
※ 테이블 별칭(alias)을 이용해 긴 테이블 이름을 간단하게 사용할 수 있다. FROM 절에서 별칭이 사용되면 SELECT문 전체에서 사용 가능하다.
2) Non-equijoin
: 테이블의 어떤 컬럼도 조인할 테이블의 컬럼에 일치하지 않을 경우 사용된다.(BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN 등)
3) Self join
: 자체적으로 테이블을 조인하는 경우
ex) 각 사원의 관리자를 출력
4) 외부조인(outer join)
: 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 사용되며 조인시킬 값이 없는 조인측에 (+)표기
//양쪽에 (+)표기 시 에러발생
ex)
※ SELECT DISTINCT는 중복되는 컬럼값을 제외하고 출력할 수 있다.
서브 질의(Sub Query)
- 하나의 SQL문에 중첩된 SELECT문
- 종류
1) 단일 행(Single Row) 서브 질의 : 오직 하나의 행을 반환, 단일 행 연산자(=, >, >=, <, <=, <>, !=) 만 사용 가능
ex) Tom과 같은 직위를 갖는 사원의 이름과 사원번호, 업무 출력
2) 다중 행(Multiple Rows) 서브 질의 : 하나 이상의 행을 반환, 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS) 사용가능
ex1) 부서별 가장 급여를 많이 받는 사원의 정보 출력
※ GROUP BY는 원하는 컬럼별로 그룹을 만들 수 있다.
※ 'WHERE sal IN (SELECT max(sal) FROM emp GROUP BY deptno' 는 원하는 결과가 나오지 않을 수 있다. //단순히 각 부서별 최고 급여가 각 부서별 사원을 UNIQUE하게 구별할 수 없기 때문이다.
ex2) 4번 부서의 최소 급여를 받는 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 급여, 업무 출력(4번 부서 제외)
ex3) 4번 부서의 최고 급여를 받는 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 급여, 업무 출력(4번 부서 제외)
3) 다중 열(Multiple Columns) 서브 질의 : 서브 질의 결과값이 두개 이상의 컬럼을 반환하는 서브 질의
ex) 업무와 보너스가 부서 4에 있는 어떤 사원의 보너스와 업무에 일치하는 사원의 사원번호, 사원이름, 업무, 보너스 출력(4번 부서 제외)
※ NVL("값", "지정값") : 값이 NULL인 경우 지정값을 출력
ex) 업무별로 최소 급여를 받는 사원의 사원번호, 사원이름, 급여, 부서번호 출력
데이터 무결성 제약조건(Integrity Constraints)
- 분류
1) 컬럼 레벨 제약 조건 : 칼럼을 정의할 때 해당 컬럼별로 지정하는 제약조건, NOT NULL 제약조건은 무조건 컬럼 레벨로 작성해야 한다!!
CREATE TABLE table_name(
coloumn_name1 datatype1,
coloumn_name2 datatype2 [CONSTRAINT constraint_name] constraint_type,
....
);
2) 테이블 레벨 제약 조건 : 칼럼을 모두 정의하고 나서 테이블 정의를 마무리 하기 전에 따로 생성된 칼럼들에 대한 제약 조건을 한꺼번에 지정, NOT NULL 이외 모두 가능
CREATE TABLE table_name(
column_name1 datatype1,
column_name2 datatype2,
...
column_namek datatypek,
CONSTRAINT constraint_name constraint_type(column_name, ...)
);
- 종류
1) NOT NULL : 컬럼에서 null을 허용하지 않도록 한다. 제약조건을 명시하지 않으면 default로 null을 허용
2) PRIMARY KEY : 하나의 기본 키만이 각 테이블에 존재할 수 있으며 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합 //UNIQUE와 NOT NULL을 동시에 만족
3) FOREIGN KEY : 기본키를 참조하는 컬럼 또는 컬럼들의 집합, 외래 키를 가지는 컬럼의 데이터형은 외래 키가 참조하는 기본키의 컬럼과 데이터형이 일치해야 한다. //ON DELETE CASCADE 연산자와 함께 정의될 경우 기본키가 삭제될 때 외래키도 함께 삭제된다.
4) UNIQUE : 데이터의 우일성 보장, null을 허용
5) CHECK : 컬럼의 값을 어떤 특정 범위로 제한
- 제약조건 변경 : ADD 절 이용
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] constraint_type(column_name);
- 제약조건 삭제 : DROP 절 이용
ALTER TABLE table_name
DROP PRIMARY KEY | UNIQUE(column) | CONSTRAINT constraint_name [CASCADE]
//CASCADE 옵션 사용시 삭제하려는 제약조건을 참조하는 모든 제약 조건들도 함께 삭제
- 제약조건 확인
1) sys.user_cons_columns : 어떤 컬럼에 어떤 제약 조건들이 정의되어 있는지 보여주는 데이터 사전
2) sys.user_constraints 로 확인 가능한 목록
owner : 제약조건의 소유자
constraint_name : 제약 조건 이름
constraint_type : 제약 조건 유형(U, C, P, R) //U(=UNIQUE), C(=CHECK or NOT NULL), P(=PRIMARY KEY), R(=FOREIGN KEY)
table_name : 제약 조건이 속한 테이블
search_condition : constraint_type 이 C인 행에 대해 각각의 조건을 나타냄
r_constraint_name : 제약조건이 PK인 경우 이것이 참조하는 PK 표기
- 제약 조건의 적용 여부
1) ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name; //제약조건을 적용시키지 않음
2) ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name; //제약조건을 다시 적용