SQLD ROWNUM, LIKE, UNION, INTERSECT, MINUS 예제

예제 테이블 및 데이터 insertion

CREATE TABLE emp 
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2)
);

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'yyyy-mm-dd'), 5000, NULL, 10);
INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'yyyy-mm-dd'), 2850, NULL, 30);
INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'yyyy-mm-dd'), 2450, NULL, 10);
INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'yyyy-mm-dd'), 2975, NULL, 20);
INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987-04-19', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'yyyy-mm-dd'), 800, NULL, 20);
INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'yyyy-mm-dd'), 1600, 300, 30);
INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'yyyy-mm-dd'), 1250, 500, 30);
INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'yyyy-mm-dd'), 1250, 1400, 30);
INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'yyyy-mm-dd'), 1500, 0, 30);
INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1987-05-23', 'yyyy-mm-dd'), 1100, NULL, 20);
INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'yyyy-mm-dd'), 950, NULL, 30);
INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'yyyy-mm-dd'), 1300, NULL, 10);
INSERT INTO emp VALUES(7940, 'X', 'CLERK', 7790, TO_DATE('1982-02-25', 'yyyy-mm-dd'), 1346, NULL, 10);

1. 부분 범위 처리

ROWNUM (Oracle)

# nest 구조문을 통해 미리 정렬한 뒤에 ROWNUM 적용해야 함
SELECT ROWNUM, 
A.*
FROM (
SELECT *
FROM emp
ORDER BY hiredate
)A;

TOP (SQL Server=MS SQL) 

# 나이 순서대로(내림차순) 정렬 후 탑 3 row 리턴
SELECT TOP 3 * 
FROM TABLE 
ORDER BY AGE DESC; 

# 상위 50% row 리턴
SELECT TOP 50 PERCENT * 
FROM TABLE 
ORDER BY AGE DESC; 

2. SQL 연산자 입력값을 비교하여 논리값 출력

LIKE '문자열'

# '%' -> 0개 이상 문자
SELECT * 
FROM emp
WHERE ename LIKE '%';

# '_' -> 1개 단일 문자
SELECT * 
FROM emp
WHERE ename LIKE '_';

# '%문자열%' -> 단순히 문자열이 포함되면 가져옴
SELECT * 
FROM emp
WHERE ename LIKE '%IN%';

# '%문자열' -> 해당 문자열로 끝나는 경우만 가져옴
SELECT * 
FROM emp
WHERE ename LIKE '%N';

# '문자열%' -> 해당 문자열로 시작하는 경우만 가져옴
SELECT * 
FROM emp
WHERE ename LIKE 'J%';

3. 함수

날짜형 함수
SYSDATE: 현재 시각 출력 (년, 월, 일, 시, 분, 초)
EXTRACT: 날짜형 부분 추출 

SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR
,EXTRACT(MONTH FROM SYSDATE) AS MONTH
,EXTRACT(DAY FROM SYSDATE) AS DAY
FROM DUAL;

NEXT_DAY(기준일자, 지정요일): 지정된 요일 첫 날짜 출력

SELECT SYSDATE 
, NEXT_DAY(SYSDATE, 'FRI') AS next_day
FROM DUAL;

조건문 IF-THEN-ELSE 형태
1. DECODE(컬럼, 기준값1, 출력값1, 기준값2, 출력값2, ..., 기본값)
2. CASE WHEN 조건절1 THEN 출력값1 ... ELSE 기본값 END

SELECT A.ename, A.job, DECODE(job, 'ANALYST', '분석가', 'SALESMAN', '영업맨', '-') job_translation
FROM emp A;

SELECT A.ename, A.job,
CASE 
    WHEN sal >= 2500 THEN '고연봉'
    WHEN sal >= 1500 THEN '저연봉'
    ELSE '뭐지?'
END AS standard
FROM emp A;

# ELSE 생략 시 NULL 출력
SELECT A.ename, A.job,
CASE 
    WHEN sal >= 2500 THEN '고연봉'
    WHEN sal >= 1500 THEN '저연봉'
END AS standard
FROM emp A;

집합연산자
UNION(합집합): 컬럼 수와 데이터 타입이 모두 동일한 테이블 간 연산
SELECT 컬럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 컬럼명 FROM 테이블명 WHERE 조건절;

# 테이블 emp2 생성
CREATE TABLE emp2
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2)
);

INSERT INTO emp2 VALUES(1234, 'JUNG', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'yyyy-mm-dd'), 5000, NULL, 10);
INSERT INTO emp2 VALUES(7698, 'JUN', 'MANAGER', 7839, TO_DATE('1981-05-01', 'yyyy-mm-dd'), 2850, NULL, 30);
INSERT INTO emp2 VALUES(5235, 'DOCO', 'MANAGER', 7839, TO_DATE('1981-06-09', 'yyyy-mm-dd'), 2450, NULL, 10);
INSERT INTO emp2 VALUES(7566, 'HYAMA', 'MANAGER', 7839, TO_DATE('1981-04-02', 'yyyy-mm-dd'), 2975, NULL, 20);
INSERT INTO emp2 VALUES(4577, 'BOND', 'ANALYST', 7566, TO_DATE('1987-04-19', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO emp2 VALUES(7902, 'PAST', 'ANALYST', 7566, TO_DATE('1981-12-03', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO emp2 VALUES(9893, 'GINGER', 'CLERK', 7902, TO_DATE('1980-12-17', 'yyyy-mm-dd'), 800, NULL, 20);
INSERT INTO emp2 VALUES(7499, 'LUKE', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'yyyy-mm-dd'), 1600, 300, 30);
INSERT INTO emp2 VALUES(1960, 'PHILL', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'yyyy-mm-dd'), 1250, 500, 30);
INSERT INTO emp2 VALUES(7654, 'JAY', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'yyyy-mm-dd'), 1250, 1400, 30);
INSERT INTO emp2 VALUES(0796, 'MANNY', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'yyyy-mm-dd'), 1500, 0, 30);
INSERT INTO emp2 VALUES(7876, 'HALEY', 'CLERK', 7788, TO_DATE('1987-05-23', 'yyyy-mm-dd'), 1100, NULL, 20);
INSERT INTO emp2 VALUES(7900, 'ALEX', 'CLERK', 7698, TO_DATE('1981-12-03', 'yyyy-mm-dd'), 950, NULL, 30);
INSERT INTO emp2 VALUES(1222, 'CLAIRE', 'CLERK', 7782, TO_DATE('1982-01-23', 'yyyy-mm-dd'), 1300, NULL, 10);
INSERT INTO emp2 VALUES(9753, 'Y', 'CLERK', 7790, TO_DATE('1982-02-25', 'yyyy-mm-dd'), 1346, NULL, 10);

아래의 경우, 테이블 emp와 emp2의 ename 컬럼은 겹치는 것이 없다. 따라서 UNION을 사용하든, UNION ALL을 사용하든 결과는 똑같을 것이다. 

# 중복 제거
SELECT A.ename
FROM emp A
UNION
SELECT B.ename
FROM emp2 B;

테이블 A: 15 rows / 테이블 B: 15 rows

# 중복 포함
# 정렬하지 않음 -> 그대로 갖다 붙임
SELECT A.ename
FROM emp A
UNION ALL
SELECT B.ename
FROM emp2 B;

INTERSECT(교집합)
SELECT 컬럼명 FROM 테이블명 A WHERE 조건절 INTERSECT SELECT 컬럼명 FROM 테이블명 B WHERE 조건절
MINUS(차집합) # EXCEPT(sql server)
SELECT 컬럼명 FROM 테이블명 A WHERE 조건절 MNIUS SELECT 컬럼명 FROM 테이블명 B WHERE 조건절

# 위에서 empno만 겹치게 테이블을 구성했음
SELECT A.empno
FROM emp A
INTERSECT
SELECT B.empno
FROM emp2 B;

# 전체 집합에서 교집합 뺀 부분
SELECT A.empno
FROM emp A
MINUS
SELECT B.empno
FROM emp2 B;