[database] SQL 용어 설명, 조인과 서브쿼리

SQL(Structured Query Language)은 관계형 데이터베이스(RDB)에서 데이터를 조작하고 검색하기 위해 사용하는 언어다. 

개념	설명
Table	Table은 RDBS의 기본적인 저장구조로, 한 개 이상의 Column과 0개 이상의 Row로 구성
Row	Column들의 값의 조합, 즉 자바에서는 iv들의 조합으로서 객체를 나타냄 (=레코드)
Field	Row와 Column의 교차점
뷰(View)	하나 또는 그 이상의 테이블로부터 논리적으로 데이터를 추출한 부분 집합으로서 논리적이고 가상적인 테이블
시퀀스(Sequence)	자동으로 고유한 숫자값 생성해 주며 주로 기본 키 값을 생성하기 위해 사용
인덱스(index)	쿼리 속도 향상
시노님(synonym)	Obejct에 대한 또 다른 이름으로 alias 역할
프로그램 유닛	SQL, PL/SQL 문으로 작성한 Proceduce, Function, Trigger, Package
PL/SQL	Procedural Language/SQL, SQL에 프로그램 로직을 추가하여 확장한 절차적인 프로그래밍 언어

 

이 중 테이블, 시퀀스, 인덱스,뷰, 시노님, 프로그램 유닛은 Object이다. 

데이터 타입	설명
VARCHAR2(size)	가변길이 문자값, 최소길이 1 ~ 최대길이 255
CHAR(size)	고정길이 문자값, 기본길이 1 ~ 최대길이 255
NUMBER	부동 소수점 숫자
NUMBER(p,s)	p의 자릿수까지 유효한 숫자값으로 전체 자릿수 p, 소수점 자릿수 s
DATE	날짜와 시간
LONG	아주 긴 문자, 가변길이 문자값, 테이블 당 한 개의 LONG 열만 허용
RAW, LONGRAW	각각 VARCHAR2와 LONG과 같지만 이진 데이터를 저장하는 데 사용

 

SQL 명령어 부류	명령어	기타
데이터 조회	SELECT
데이터 조작어 (DML)	INSERT, UPDATE, DELETE	ROLLBACK 가능
데이터 정의어 (DDL)	CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT	AUTO-COMMIT으로 ROLLBACK 불가능
Transaction Control (TCL)	COMMIT, ROLLBACK, SAVEPOINT
데이터 제어어 (DCL)	GRANT, REVOKE

 

함수 종류

• 단일 행 함수 - 문자형 함수, 숫자형 함수, 날짜형 함수, 변환형 함수
• 다중 행 함수 - 그룹 함수

 

SELECT 절에 그룹함수가 오면 SELECT 절의 나머지 칼럼은 GROUP BY 절에 기술되어야 한다. 즉 SELECT 절에 그룹함수가 오거나, GROUP BY절 이하에 기술된 칼럼이 오면 나머지 칼럼은 SELECT 절에 기술할 수 없다. 

 

Join 방법

• Equijoin, Non-Equijoin, Outer join, Self join
• 조인 조건이 생략되거나 잘못 기술되면 한 테이블에 있는 모든 행들이 다른 테이블에 있는 모든 행들과 join - Cartesian Product

조인 방법	설명
Equijoin	칼럼 간의 값들이 서로 정확히 일치하는 경우에 사용, 일반적으로 PK, FK 관계에 의함
Non-Equijoin	한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에 사용
Outer join	Join 조건을 만족하지 않는 경우에도 모든 행들을 다 보려는 경우에 사용
Self Join	같은 테이블에 있는 행들을 Join 하고자 하는 경우에 사용

 

1. Equijoin

• 일반적으로 PK, FK 관계에 의하여 JOIN이 성립
• ALIAS 지정하면 테이블명 대신에 반드시 ALIAS 지정

SELECT 테이블명.칼럼명, 테이블명.칼럼명 ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼1 = 테이블2.칼럼2;

 

 

-- 스칼라 서브쿼리 
-- 느릴 것 같지만 캐싱(메모이제이션) 기능 O -> 실행결과 map에 담음 -> 읽어서 가져다 사용

SELECT id, name
(SELECT name FROM s_region WHERE id = region_id)
FROM s_dept;

-- s_dept에 있는 region_id를 보고 s_region의 id와 일치하는 곳의 name을 반환

2. Non-Equijoin

• 등급
• 한 컬럼의 값이 다른 컬럼의 값과 정확히 일치하지 않는 경우 = 연산자 외의 다른 연산자를 사용하여 join

SELECT e.name, e.salary, s.grade
FROM s_emp e, salgrade s
WHERE e.salary between s.losal and s.hisal;

 

3. Outer Join

• Outer Join을 하는 경우 (+) 연산자를 사용
• 데이터를 모두 넣으려는 기준 데이터의 반대 쪽에 (+) 위치
• 예를 들어 아래의 경우 s_customer의 데이터를 다 넣음

SELECT e.name 직원, e.id 사번, c.name 담당고객
FROM s_emp e, s_customer c
WHERE e.id (+)= c.sales_rep_id 
ORDER BY e.id ASC;

4. Self Join

• 테이블에 대한 ALIAS 각기 다르게 지정
• 컬럼 이름 앞에 테이블 ALIAS 반드시 지정
• Self Join 횟수는 제한되어 있지 않음

SELECT e.name, e.title, e.salary, e.dept_id
FROM s_emp e, s_emp m
WHERE e.manager_id = m.id and e.title = (
    select title 
    from s_emp
    where name = '김정미'
);

 

SET 연산자

• UNION
• UNION ALL
  
  • 교집합이 없을 땐 UNION ALL로 그냥 합치는 게 효율적이다
• INTERSECT
• MINUS
• 첫 번째 SELECT 구문에서 기술된 칼럼들과 두 번째 SELECT 구문에서 기술된 칼럼들은 그 개수와 타입이 일치해야 한다

 

서브 쿼리

• 하나의 SELECT 문 안에 포함되어 있는 또 다른 SELECT 문
• 서브쿼리는 괄호로 묶여 있어야 한다
• 서브쿼리는 연산자의 오른쪽에 나타나야 한다
  • 연산자의 종류는 단일 행 연산자, 다중 행 연산자

1. 단일 행 서브쿼리 (Single Row SubQuery)

• 서브쿼리에서 메인 쿼리로 전달되는 행이 단 하나인 경우
• 단일 행 연산자 사용

2. 다중 행 서브쿼리 (Multi Row SubQuery)

• 서브쿼리에서 메인 쿼리로 전달되는 행이 여러 개인 경우
• 다중 행 연산자 사용(IN, NOT IN)

3. Multi Column SubQuery

• 서브쿼리 구문 작성할 때 WHERE 절에서 비교하는 칼럼이 하나가 아니라 여러 개의 칼럼을 동시에 비교하는 경우
• 이런 경우 pair-wise 되었다고 함

아래 코드 하나 정도는 암기해 두자

SELECT name, dept_id, salary
FROM s_emp
WHERE (salary, dept_id) IN
    (SELECT MIN(salary), dept_id 
    FROM s_emp
    GROUP BY dept_id);

 

- FROM 절에서의 서브쿼리

• 한 테이블에 데이터의 양이 많은 경우 FROM 절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있다. 이럴 때는 일부의 행과 컬럼을 선택하여 FROM 절에 서브쿼리로 효율적인 검색이 가능하다
• 이런 서브쿼리를 INLINE VIEW 라고 부른다
• 테이블 사이즈를 줄이는 게 튜닝의 일환

SELECT e.name, e.title, d.name
FROM (SELECT name, title, dept_id
     FROM s_emp
     WHERE title = '사원') e, s_dept d
WHERE e.dept_id = d.id;

 

- CREATE 절에서의 서브쿼리

• 테이블을 생성할 때 이미 만들어져 있는 기존의 테이블을 이용해서 특정 컬럼 또는 행만으로 원하는 테이블을 생성할 수 있다. 데이터는 제외하고 테이블만 생성하려면 조건절이 항상 거짓이게 하면 된다.
• 생성할 테이블의 컬럼명을 생략하면 SELECT 문의 컬럼명과 동일하게 된다

CREATE TABLE emp_113 (id, name, mailid, start_date)
AS SELECT id, name, mailid, start_date
    FROM s_emp
    WHERE dept_id = 113; -- 부서가 113인 직원 테이블

 

이외에도 HAVING 절에서도 쓸 수 있다. 

 

- DML문에서의 서브쿼리

INSERT INTO emp_113 (id, name, mailid, start_date)
    SELECT id, name, mailid, start_date
    FROM s_emp
    WHERE start_date < '16/01/01';

UPDATE s_emp
SET dept_id = (
    SELECT dept_id
    FROM s_emp
    WHERE title = '사장')
WHERE name = '안창환';