SELECT
행(Column)을 선택할때 사용
AS(Alias), DISTINCT 키워드 및 산술/연결 연산자를 사용할 수 있다.
select * from emp;
// emp 테이블의 모든 열을 선택
select name, age from emp;
// emp 테이블의 name, age 열을 선택
select id, sal 급여 from emp;
select id, sal as "Salary" from emp;
// emp 테이블의 id, sal 열을 선택 (sal 열은 급여로 표시)
// emp 테이블의 id, sal 열을 선택 (sal 열은 Salary로 표시)
// 별칭(Alias) 기능 (as 키워드는 생략 가능, 대소문자를 구분하려면 큰따옴표 사용)
select id, sal * 5 as "Bonus" from emp;
// emp 테이블의 id, sal 열을 선택하여 sal에는 *5한 값을 표시
// select문에 산술 연산자 사용 가능
select name || 님 from emp;
// emp 테이블의 name 열을 선택하고 값 뒤에 "님"을 붙인다.
// select문에 연결 연산자 사용 가능
select distinct groupId from emp;
// emp 테이블의 groupId 열의 중복된 값을 제거한 뒤 표시
// distinct 키워드는 select 바로 뒤에 사용!
// 여러 열이 오는 경우 조합된 값으로 중복 여부 결정Where
조건을 이용하여 참/거짓인 행을 선택할 수 있음
From 바로 뒤에 사용하며, 비교연산자(>, >=, =, <>, <=, <)를 사용하여 조건을 만들고 복합연산자(OR, AND)를 사용하여 복합 조건을 사용할 수 있다.
BETWEEN, IN, LIKE, NULL, NOT 키워드를 사용할 수 있다.
select name, sal, groupId from emp where name = 'SCOTT';
// 문자열은 따옴표를 사용해주어야 하고, 숫자는 따옴표를 사용하지 않아도 된다.
select name, position, height from player
where position = 'GK' or height > 190;
// or 연산자를 사용하여 2개 조건 중 1개만 일치하더라도 값을 가져온다.
select name, position, sal from player
where position = 'GK' and sal > 2000;
// and 연산자를 사용하여 2개 조건 모두 일치하는 경우 값을 가져온다.
// and 연산자가 or 보다 우선순위가 높기 때문에, () 괄호를 사용하여 우선순위를 바꿀 수 있다.
select name, team, weight from player
where weight between 100 and 120;
// weight가 100 ~ 120인 행을 가져온다. (100, 120 포함)
// between을 사용할땐 앞에 작은 값이 나와야한다. (반대로 하는 경우 값 추출 X)
// 날짜, 문자, 숫자 데이터 타입 모두 사용 가능
select name, team, position from player
where position in('GK','MF','ST');
// in 안에 해당하는 값이 있는 행을 추출한다.
// 날짜, 문자, 숫자 데이터 타입 모두 사용 가능
select name, team, position from player
where name like '김%';
// 특정 문자 또는 문자열이 포함되어 있는 행을 추출한다.
// 와일드 카드 문자 (%, _)를 이용할 수 있다.
// % : 0개 문자 또는 1개 이상의 문자열과 매칭
// _ : 1개 문자와 매칭
select name, team, bonus from player
where bonus is null;
// 값이 없는 경우, 즉 null인 행을 추출한다.
// null은 비교연산자를 사용했을때 false를 반환하기 때문에 is null을 이용한다.
select name, bonus from player
where not bonus is null;
// 조건 앞에 not 연산자를 사용하면 해당 조건의 반대되는 값을 추출한다.
// 키워드 바로 앞에 사용하거나 column 앞에 사용하면 된다.Order By
Select 문의 제일 마지막에 작성하여 사용한다.
기본 정렬은 오름차순이며, Null 값은 오름차순에서 마지막에 표시된다.
DISTINCT를 사용하지 않는 경우, SELECT 문에 없는 컬럼 기준으로 정렬할 수 있다.
NULLS FIRST, NULLS LAST 키워드를 사용할 수 있다.
select name, groupId, sal from emp
order by sal;
// sal을 오름차순으로 정렬하여 행을 표시한다.
select name, groupId, sal from emp
order by groupId, sal desc;
// groupId는 오름차순, sal은 내림차순으로 정렬하여 행을 표시한다.
select name, groupId, sal from emp
order by 2, 3 desc;
// 컬럼명 대신 index를 이용하여 행을 정렬할 수 있다.
// "order by 2,3 desc" 는 "order by groupId, sal desc" 와 같다.
select name, groupId, sal from emp
order by hiredate;
// hiredate를 오름차순으로 정렬하여 행을 표시한다.
// select 문에 해당 컬럼이 없어도 정렬에 사용할 수 있다.
select name, sal, bonus, overtime from emp
order by bonus nulls first, overtime desc nulls last;
// bonus는 오름차순으로 정렬하되 null 값은 처음에
// overtime은 내림차순으로 정렬하되 null 값은 마지막에 표시한다.Group By
테이블의 행을 더 작은 그룹으로 나눈다.
Group By절을 이용하면 SELECT절에는 Group By에서 사용한 속성과 집계함수만 나올 수 있다.
❗️Where 조건절에 사용된 컬럼이나 값은 Group By절에 사용한 컬럼이나 값이 달라도 됨.
❗️Where 조건 처리 → Group By 및 집계 함수 처리
❗️Order By 사용 시 Grouping 된 결과가 정렬됨.
select job, deptno, avg(sal), sum(bonus) from emp
group by job, deptno;
// job, deptno를 그룹화하며 sal의 평균, bonus의 합계를 반환한다.
select team_id, count(*) from player
where position = 'DF'
group by team_id
order by count(*) desc;
// 각 팀의 수비수들의 수를 내림차순으로 반환한다.Having
Group By 절의 기준항목이나 소그룹의 집계 함수를 이용한 조건 부여 가능.
(Where 절은 그룹화 이전, Having 절은 그룹화 이후 Filtering 작업 수행)
Having 절의 조건을 만족하는 내용만 결과로 반환함.
❗️그룹화 → 그룹 함수 적용 → Having 절 적용
❗️Where 절에는 그룹 함수를 이용한 조건 사용 불가 (ex. avg sum...)
❗️Order by는 Having 절 이후 사용
select team_id, count(*), avg(weight) from player
group by team_id
having count(*) > 47
order by count(*) desc;
// 팀의 소속 선수가 47명보다 많은 경우 소속 선수 수를 내림차순으로 팀, 인원, 평균몸무게를 결과로 반환한다.
함수
단일행함수
행마다 하나의 결과를 반환하는 함수이며, 함수를 중첩하여 사용할 수 있다.
SELECT / WHERE / ORDER BY 절에 사용할 수 있다.
1. 문자 함수
| 함수명 | 함수 설명 |
|---|---|
| LOWER | 모든 문자를 소문자로 변환 |
| UPPER | 모든 문자를 대문자로 변환 |
| INITCAP | 문자열의 첫 문자는 대문자, 나머지는 소문자로 변환 |
| CONCAT(s1,s2) | 두 문자열을 연결 |
| SUBSTR(s, m, n) | 문자열을 지정한 위치에서부터 지정 길이만큼 추출 |
| LENGTH(s) | 문자열의 길이 반환 |
| CHR(n) | ASCII 코드값이 n인 문자 반환 |
| ASCII(s) | s 문자의 ASCII 코드값 반환 |
| LPAD(s1, n, s2) | 문자열의 왼쪽에 원하는 길이만큼 특정 문자를 채워줌 |
| RPAD(s1, n, s2) | 문자열의 오른쪽에 원하는 길이만큼 특정 문자를 채워줌 |
| LTRIM(s, c) | 문자열의 왼쪽에 있는 지정 문자를 모두 제거 |
| RTRIM(s, c) | 문자열의 오른쪽에 있는 지정 문자를 모두 제거 |
| TRIM(c from s) | 문자열의 양쪽에 있는 지정 문자를 모두 제거 |
| TRANSLATE(s, from, to) | 문자열의 특정 문자를 지정 단어로 변환, 문자별로 치환함 |
| REPLACE(s, p, r) | 문자열의 특정 문자를 지정 단어로 변환 |
2. 숫자 함수
| 함수 | 함수 내용 |
|---|---|
| ROUND | 지정된 자리로 반올림 |
| TRUNC | 지정된 자리로 버림 |
| MOD(M, N) | M을 N으로 나눔 |
| ABS | 절댓값 반환 |
| FLOOR | 버림 |
| CEIL | 올림 |
| SIGN | 부호 반환 (양수 1, 음수 -1) |
| POWER(M, N) | M의 N승 |
3. 날짜 함수
기본 Date Format에 따라 화면에 표시되고, 세기/연도/월/일/시/분/초 형태로 Format 설정 가능하다.
날짜표시를 변경하고 싶은 경우,
alter session set nls_date_format = 'yyyy-mm-dd:hh24:mi:ss';
오늘 날짜 보고 싶은 경우,
select sysdate from dual;
날짜형 데이터 간 연산 가능
- DATE + NUMBER : 날짜에 해당 일수를 더함
- DATE - NUMBER : 날짜에 해당 일수를 뺌
- DATE1 - DATE2 : 두 날짜 사이의 경과 일수 숫자로 리턴 (더하는 건 안됨)
| 함수 | 함수 내용 |
|---|---|
| ADD_MONTHS(D1, N) | D1에 N개월을 더한 날짜 반환 |
| MONTHS_BETWEEN(D1, D2) | 두개 날짜 간의 경과 개월 수 반환 |
| LAST_DAY | 해당 월의 마지막 날짜 반환 (숫자가 아닌 날짜 반환) |
| ROUND | 날짜를 년,월 단위로 반올림 (기준 뒤는 0으로 초기화) |
| TRUNC | 날짜를 년,월 단위로 버림 (기준 뒤는 0으로 초기화) |
| EXTRACT | 날짜에서 지정한 날짜 필드 값 반환 |
| INTERVAL | 날짜에서 지정한 날짜 필드 값 연산하여 반환 |
// ROUND, TRUNC 예제
// round는 월 기준으로 연 반올림, trunc는 일 버림.
select name, birth_date,
round(birth_date, 'YY'), trunc(birth_date, 'MM')
from player where positon = 'GK';
// EXTRACT 예제
select sysdate, extract(year from sysdate) from dual;
// 날짜의 년도만 반환함.
// INTERVAL 예제
select name, birth_date, birth_date + interval '50' year
from player where position = 'GK';
// birth_date + 50년 더해진 값 반환됨
select name, birth_date, birth_date + interval '01:30' hour to minute
from player where position = 'GK';
// birth_date + 1시간 30분 더해진 값 반환됨
select name, birth_date, birth_date + interval '7:3' minute to second
from player where position = 'GK';
// birth_date + 7분 3초 더해진 값 반환됨4. 변환 함수
Number ↔︎ Character ↔︎ Date 간 형변환이 가능하다.
Oracle에서는 암시적 형변환 지원, 명시적 변환은 아래 함수를 사용해서 한다.
| 함수 | 함수 내용 |
|---|---|
| TO_NUMBER | Char → Num 변환할 때 사용 |
| TO_CHAR | Num | Date → Char 변환할 때 사용 |
| TO_DATE | Char → Date 변환할 때 사용 |
// TO_NUMBER 예제
select to_number('000123') from dual;
// 123 숫자를 반환한다.
// TO_CHAR 예제
select to_char(sysdate, 'DD') from dual;
// 현재 날짜의 일자를 문자로 변환한다. (format 형태로 문자를 반환)
// TO_DATE 예제
select to_date('12/11/07', 'yy/mm/dd') from dual;
// 입력된 문자열을 2012년 11월 07일의 날짜로 변환.
// 년도/월은 sysdate를 기본값, 일자는 1, 시/분/초는 0을 기본값으로 한다.
5. 기타 함수
| 함수 | 함수 내용 |
|---|---|
| NVL(exp1, exp2) | exp1=NULL일때 exp2 반환, 아니면 exp1 반환 |
| NVL2(exp1, exp2, exp3) | exp1=NULL일때 exp3 반환, 아니면 exp2 반환 |
| NULLIF(exp1,exp2) | exp1=exp2이면 NULL 반환, 아니면 exp1 반환 |
| COALESCE(exp1,,expN) | 순차적으로 exp을 체크하여 NULL이 아닌 인수 반환 |
| DECODE(VALUE,IF1,THEN1,,default) | VALUE=IF1일때 THEN1 반환, 전부 일치하지 않으면 default 반환 |
| CASE, WHEN, THEN, ELSE, END | 조건문을 작성할때 유용 |
// NVL 예제
select name, sal, bonus, sal+NVL(bonus, 0) 연봉 from emp;
// 연봉 Column에서 bonus가 NULL인 경우 sal+0, 아니면 sal+bonus를 반환한다.
// NVL2 예제
select name, position, NVL2(position, position||'담당', '없음') role from player;
// role Column은 position이 NULL인 경우 '없음'을, 아니면 position+'담당'을 반환한다.
// exp2, exp3은 데이터 타입이 같아야한다.
// NULLIF 예제
select name, nullif(position, 'GK') from player;
// position이 'GK'인 경우 NULL 반환, 아니면 postion 반환.
// COALESCE 예제
select name, sal, bonus, coalesce(sal, bonus, 100) from emp;
// sal, bonus가 모두 NULL 이면 100 반환 (모든 인수가 NULL인 경우 NULL 반환)
// DECODE 예제
select name, sal, decode(sal, 3000, 'A', 5000, 'B', 'C') from emp;
// sal = 3000이면 'A', = 5000이면 'B', 모두 아니면 'C' 반환
// Oracle에서 default값 생략되면 NULL 반환
// CASE 함수
select name, sal, case when sal < 1000 then sal*1.5
when sal < 2000 then sal*1.4
when sal < 5000 then sal*1.1
else sal end 신규급여
from emp;
그룹함수
집합 또는 그룹으로 분류된 집합에 작용하여 그룹당 하나의 결과를 만들어낸다.
통계 정보(최댓값, 최솟값, 평균 등)를 나타내기에 좋다.
| 함수 | 함수 내용 |
|---|---|
| COUNT(*) | NULL값을 포함한 행의 수 |
| COUNT(표현식) | 표현식의 값이 NULL인 행을 제외한 행의 수 |
| SUM([DISTINCT|ALL] 표현식) | 합계 |
| AVG([DISTINCT|ALL] 표현식) | 평균 |
| MAX([DISTINCT|ALL] 표현식) | 최대값 (문자,날짜 데이터도 사용 가능) |
| MIN([DISTINCT|ALL] 표현식) | 최소값 (문자,날짜 데이터도 사용 가능) |
| STDDEV([DISTINCT|ALL] 표현식) | 표준편차 |
| VARIANCE([DISTINCT|ALL] 표현식) | 분산 |
❗️COUNT(*)를 제외하고 나머지 함수는 모두 NULL값을 제외한 후 결과를 산출한다.
❗️DISTINCT 는 중복되지 않는 값만 검토, ALL 은 중복 값을 포함한 값을 검토 (기본값 : ALL)
❗️NULL 값을 특정 값으로 치환하여 계산하려면 NVL 함수 사용
❗️AVG, SUM, STDDEV, VARIANCE 는 숫자 데이터 타입만 사용 가능
select count(distinct team_id) 팀개수 from player;
// distinct를 사용해 중복되지 않은 team_id 개수를 반환한다.
select avg(bonus), avg(nvl(comm, 0)) from emp;
// null을 제외한 bonus 평균값, null을 0으로 치환한 뒤의 bonus 평균값을 반환한다.윈도우함수
SELECT 다음에만 윈도우 함수 사용 가능
사용방법
SELECT ANALYTIC_FUNCTION(arguments)
OVER ( [Partition By 컬럼List]
[Order By 컬럼List]
[Windowing절 (Rows | Range Between)]
)
FROM 테이블- Analytic Function : 분석함수명(입력인자)
- OVER : 분석함수임을 나타내는 키워드 (필수적으로 써줘야함)
- Partition By : 계산 대상 그룹
- Order By : 대상 그룹에 대한 정렬
- Windowing절 : 분석함수 계산 대상 범위 (Order By 같이 사용해야함)
함수 처리 단계
Joins, Where, Group By, Having → 윈도우 함수 → Order By
❗️Order By 절에 종속적
| 함수 | 함수 내용 |
|---|---|
| RANK | Partition 내에서 순위 부여 (동일 순위인 경우 그 수만큼 순위를 건너뜀) |
| DENSE_RANK | Partition 내에서 순위 부여 (동일 순위인 경우가 있더라도 순위 순서 유지) |
| ROW_NUMBER | 정렬된 순서로 값을 반환 |
| NTILE | Partition을 동일한 수만큼 그룹으로 나눔 |
| SUM, AVG, MAX | Partiton별 SUM,AVG,MAX 값 반환 |
| LAG | 지정된 offset 값만큼의 이전값 조회 |
| LEAD | 지정된 offset 값만큼의 이후값 조회 |
// RANK()
select name, deptno, sal, rank() over (order by sal desc)
from emp;
// 급여가 많은 순으로 순위 결과 반환
// DENSE_RANK()
select name, deptno, sal, dense_rank() over
(partition by deptno order by sal desc)
from emp;
// 급여가 많은 순으로 순위 결과 반환 (동일 순위가 있더라도 순서 유지)
// ROW_NUMBER()
select name, sal, hiredate, row_number() over
(order by sal desc, hiredate asc) as "순번"
from emp;
// 급여가 많은 순, 같은 경우 입사일이 빠른 순으로 번호 부여
// NTILE()
select name, sal, ntile(4) over (order by sal) from emp;
// 급여가 작은 순으로 4그룹으로 나누어 결과 반환
// SUM(), AVG(), MAX()
select name, deptno, sal, sum(sal) over(), sum(sal) over
(partition by deptno)
from emp;
// 각 행마다 급여의 전체 합계를 출력
select name, job, sal,
avg(sal) over (partition by job),
max(sal) over (partition by job)
from emp;
// 각 행마다 직업별 평균 급여, 직업별 최대 급여를 출력
// LAG(컬럼, offset, 기본값)
select name, deptno, sal,
lag(sal, 1, 0) over (order by sal),
lag(sal, 1, sal) over (order by sal),
from emp;
// 1개 이전 행 급여 출력 (없다면 0), 1개 이전 행 급여 출력 (없다면 본인값)
// LEAD(컬럼, offset, 기본값)
select name, deptno, sal,
lead(sal, 1, 0) over (order by sal),
lead(sal, 1, sal) over (order by sal),
from emp;
// 1개 이후 행 급여 출력 (없다면 0), 1개 이후 행 급여 출력 (없다면 본인값)
// ROWS 옵션
select name, deptno, sal,
sum(sal) over (order by sal rows
between 1 preceding and 1 following)
sum(sal) over (order by sal rows unbounded preceding)
from emp;
// 앞줄 1개부터 뒷줄 1개까지의 급여 합, 이전 Row의 급여 누적합계를 출력JOIN
데이터베이스에서 여러개의 테이블에서 데이터를 연결해 조회하고 싶을 때 사용한다.
JOIN 사용 방법
- From 절에 사용할 테이블들을
,로 구분하여 모두 입력 - Where 절에 적절한 Join 조건 입력
- 일반적으로 테이블 - 1개의 조건 필요, PK/FK 간 조인인 경우 조건 붙는 경우 많음.
JOIN 방식
1.Nautred Loop whau
select ename, dname from emp, dept
where emp.deptno = dept.deptno order by ename;
// 이름과 부서명을 부서번호를 기준으로 매칭하여 데이터를 조회한다.❗️카타시안곱, 조인에 대한 조건이 없거나 부적합한 경우
1번 테이블에 2번 테이블의 모든행이 조인되어 결과를 반환한다.
JOIN 방식
- Nested Loop Join
다른 프로그래밍 언어의 이중 반복문과 비슷하게 동작한다.
선행 테이블에서 조건을 확인하여 일치하는 레코드만 후위 테이블에서 검사한다
❓테이블을 검색할때 인덱스를 사용한다.
❗️결과''데이터의 양이 적을 때 사용
테이블 별칭 지정하기
select ename, dname from emp e, dept d
where e.deptno = d.deptno order by ename;
// 별칭을 사용했을땐 별칭이 아닌 본래 이름을 사용하면 오류 발생
// emp → e로 별칭 사용했는데 emp.deptno로 호출하면 오류 발생범위 검색을 위한 조인 조건
select e.ename, e.sal, s.grade from emp e, salgrade s
where e.sal between s.losal and s.hisal order by grade
// e.sal의 값이 s.losal과 s.hisal 값 사이에 있는 경우 해당 grade 조회
__Outer 조인__
조인 조건을 만족하지 못해도 모든 행들을 보고 싶은 경우에 사용
`(+)` 연산자를 사용하여 OuterJoin 테이블 `ex) 기준테이블 = Outer
Join 테이블(+)`
Outer Join 시, 조건을 만족하지 못하는 기준 테이블의 행도 모두 조회된다.
Join 조건을 여러개 사용하는 경우, OuterJoin 테이블의 모든 컬럼에 `(+)` 연산자를 사용해야 한다.
❗️`(+)` 연산자를 양쪽 테이블 모두 사용하는 경우 오류 발생
```sql
select ename, dname from emp e, dept d
where e.deptno = d.deptno(+) order by ename;집합 연산자 (SET 연산자)
여러개의 테이블에 집합 연산자를 사용 가능하다.
집합 연산자들의 우선순위는 모두 같다 (좌에서 우로 실행).
| 연산자 | 연산자 설명 |
|---|---|
| intersect | 교집합 |
| minus | 차집합 |
| union | 합집합 |
| union all | 합집합 (중복을 제거하지 않음) |
// intersect 예제
select deptno from dept
intersect
select deptno from emp;
// minus 예제
select deptno from dept
minus
select deptno from emp;
// union 예제
select deptno from dept
union
select deptno from emp;
// union all 예제
select deptno from dept
union all
select deptno from emp;주의 사항
1. Select 절에서 조회하는 Column 개수와 데이터 유형이 같아야 한다.
2. Order By 절에서 사용된 열 이름이나 별칭은 첫번째 Select 절에 있어야 한다.
ANSI SQL
DB 표준 JOIN 방법 (오라클에서도 지원)
JOIN 조건
- 카타시안곱, 조인 조건을 지정하지 않는 경우
// Oracle
select e.ename, d.dname from emp e, dept d;
// ANISI SQL
select e.ename, d.dname from emp e CROSS JOIN dept d;- JOIN 조건 자동 생성 (동일한 이름을 가진 모든 컬럼을 기준으로 조인)
// Oracle
select e.ename, d.dname, e.deptno from emp e, dept d
where e.deptno = d.deptno;
// ANISI SQL
select e.ename, d.dname, deptno from emp e NATURAL JOIN dept d;
// 같은 이름을 가진 컬럼(ex. deptno)를 호출할때, ANSI SQL에서는 테이블 이름을 붙이면 안된다.- 특정 컬럼을 정하여 JOIN 조건 설정
// Oracle
select e.ename, d.dname, e.deptno from emp e, dept d
where e.deptno = d.deptno;
// ANISI SQL
select e.ename, d.dname, deptno
from emp e JOIN dept USING (deptno);
// 추가로 일반 조건(ex. 이름에 A가 있는 행)을 설정하고 싶은 경우 Where 절 사용
// USING 뒤에 콤마로 여러 컬럼 지정 가능- 특정 JOIN 조건 설정
// Oracle
select e.ename, e.sal, s.grade from emp e, salgrade s
where e.sal between s.losal and s.hisal order by grade
// ANISI SQL
select e.ename, e.sal, s.grade
from emp e JOIN salgrade s ON ( e.sal between s.losal and s.hisal);
// 가로 생략 가능 JOIN 범위
Inner Join
→ 두 테이블을 Join하여 일치하는 열만 반환
Left/Right Outer Join
→ 두 테이블을 Join한 결과와 조건에 일치하지 않는 왼쪽/오른쪽 테이블의 행 같이 반환
Full Outer Join
→ 두 테이블을 Join한 결과와 조건에 일치하지 않는 행 모두 반환
- Inner Join
Inner 키워드는 생략 가능하다. (Join 기본값 : Inner Join)
select d.dname, l.city
from dept d inner JOIN locations l ON (d.loc_code = l.loc_code);- LEFT/RIGHT Outer Join
Outer 키워드는 생략 가능하다.
select d.dname, l.city
from dept d LEFT outer JOIN locations l ON (d.loc_code = l.loc_code);
select d.dname, l.city
from locations l RIGHT outer JOIN dept d ON (d.loc_code = l.loc_code);- Full Outer Join
select d.dname, l.city
from locations l FULL outer JOIN dept d ON (d.loc_code = l.loc_code);서브쿼리
Select 절 내에 Select 절을 사용하는 것
(ex. 특정인의 급여보다 많은 급여를 받는 사람을 조회해야 할 때)
괄호 안에 있는 쿼리를 서브쿼리(Inner 쿼리), 괄호 바깥에 있는 쿼리를 메인쿼리(Outer 쿼리)라고 한다.
일반적으로 서브 쿼리가 먼저 실행됨.
사용 방법
- 서브쿼리를 괄호로 묶어야 함
- 일반적인 서브쿼리(Top-N 분석 제외)에서는 Order By절 사용 안 함
- 단일행 연산자 (=,>,>=,<=,<) 또는 복수행 연산자(IN, ANY, ALL) 사용
- Select, From, Where, Having, Order By, Update의 SET, Insert의 Into 절에 사용 가능
복수행 연산자
- IN 목록에 있는 임의의 값과 동일
- ANY 반환되는 각각의 값과 개별 비교
- ALL 반환되는 모든 값과 비교
// 단일행 연산자
select ename, sal from emp
where sal > ( select sal from emp where ename = 'BLAKE');
// 복수행 연산자
// IN
select name, position from player
where position IN (select position from player where name = '홍길동' or name = '이몽룡');
// 이름이 홍길동 또는 이몽룡인 선수의 포지션과 같은 포지션인 모든 선수 조회
// ANY
select name, team_id, height from player
where height > ANY( select height from player where team_id = 'JK');
// JK팀 선수들의 키 최솟값보다 큰 선수들을 조회.
// ANY는 하나의 조건이라도 만족하는 경우, 즉 최솟값을 만족하는 경우.
select name, team_id, height from player
where height > ( select mean(height) from player where team_id = 'JK');
// 위 문장과 동일 결과 조회
// ALL
select name, team_id, height from player
where height > ALL( select height from player where team_id = 'JK');
// JK팀 선수들의 키 최댓값보다 큰 선수들을 조회.
// ALL은 모든 값에 대해 조건을 만족하는 경우, 즉 최댓값을 만족하는 경우.
select name, team_id, height from player
where height > ( select max(height) from player where team_id = 'JK');
// 위 문장과 동일 결과 조회다중 컬럼 서브쿼리
서브 쿼리 사용에 컬럼 수 제한은 없다.
컬럼의 순서를 동일하게 해주어야 서브 쿼리 사용이 가능하다.
select name, team_id, position, height from player
where (team_id, position) in ( select team_id, position from player where name in('이천수','안정환'));
// 위 문장과 동일 결과 조회From절 서브쿼리 (Inline View)
From절에 사용되는 서브쿼리
select * from (select name, sal from emp order by desc)
where rownum < 6;
// 급여를 많이 받는 순서로 5명 조회.스칼라 서브쿼리
Column 하나 / Row 하나만 추출해내는 서브쿼리
Select절에는 Column 하나 / Row 하나만 사용해야 하기 때문에 이 경우에 사용
Select 조회 시 스칼라 서브쿼리 반환값으로 모든 행을 채움
상호연산 서브쿼리
스칼라 서브쿼리와 다르게 행마다 조건에 맞는 다른 값을 부여해주고 싶을때 사용
서브쿼리에서 메인쿼리의 컬럼명 사용가능, 반대는 불가
select name, sal, deptno, (select dname from dept where deptno = A.deptno) dname
from emp A;
select deptno, dname, (select count(*) from emp where deptno = D.deptno) a1
from dept D
where 3 < (select count(*) from emp where deptno = D.deptno);
// 각 부서의 인원수룰 조회하고 부서 인원이 3명 초과되는 경우만 출력EXISTS
서브쿼리의 값이 하나의 행 이상을 반환하면 True, 없으면 False 반환
즉, 서브쿼리의 값이 하나의 행 이상이면 메인쿼리 결과를 반환하고 그렇지 않은경우 반환하지 않는다.
서브쿼리에서 하나의 행이 반환되면 행을 더 조회하지 않고 다음으로 넘어간다. (연산 속도)
select deptno, dname from dept d
where exists (select 1 from emp where deptno = d.deptno);INSERT
INSERT INTO 새로운 데이터를 추가할 때 사용
create table sawon
(id number(5),
name varchar2(20),
salary number(10));
insert into sawon values (100, '홍길동', 3300);
insert into sawon (name,id) values ('이몽룡', 4000);
// 특정 컬럼에만 값을 넣고 싶은 경우 values 앞에 컬럼을 설정해준다.
insert into sawon
select empno, ename, sal*2 from emp where deptno = 10;
// 서브쿼리를 이용한 INSERT
// 특정 테이블에서 조회한 값을 원하는 테이블에 INSERT 할 수 있다.UPDATE
UPDATE 기존 데이터를 변경할 때 사용
Where 절을 생략하면 테이블의 모든 행이 변경되기 때문에 주의
update sawon set salary = 5500, name = '김길동' where id = 100;
// id가 100번인 사원의 이름과 급여를 변경함.
update sawon set salary = (select sum(sal) from emp where deptno = 10);
where id = 200
// 서브쿼리를 이용한 UPDATE
// id가 200번인 사원 급여를 emp 테이블의 10번 부서 급여 합계값으로 변경DELETE
DELETE 기존 데이터를 삭제할 때 사용
Where 절을 생략하면 테이블의 모든 행이 삭제되기 때문에 주의
delete from sawon where id = 100;
// from 키워드는 아래와 같이 생략 가능
delete sawon where id = 100;
// id가 100번인 사원 행 삭제
delete emp
where deptno = (select deptno from dept where dname="SALES");
// 서브쿼리를 이용한 DELETEMERGE
행을 조건에 따라 테이블에 갱신하거나 입력하는 기능 (UPDATE를 여러번 사용하지 않아도 됨)
테이블에 대한 수정/입력 여부는 ON절의 조건에서 결정됨
MERGE = INSERT , UPDATE 기능을 결합한 명령어
merge into emp_mast t
using emp e
on (t.empno = e.empno)
when matched then
update set t.sal = t.sal + e.sal,
t.comm = e.comm
// update 다음에 테이블 이름을 따로 안 적어줘도 됨
when not matched then
insert (empno, ename, sal, comm)
values (e.empno, e.ename, e.sal, e.comm);
// into (테이블) 키워드를 사용하지 않아도 됨Transaction
DML(INSERT, UPDATE, DELETE)의 집합
DDL, DCL은 한 문장이 Transaction으로 처리됨.
COMMIT 모든 데이터의 변경사항을 영구히 저장하고 현재의 Transaction 종료
ROLLBACK 모든 데이터의 변경사항을 버리고 현재의 Transaction 종료
Pivot
Pivot 함수를 이용하면 행/열 전환을 보다 쉽게 할 수 있다.
Oracle 11g 버전부터 지원하는데, 이전 버전은 Decode Case를 이용하여 행을 반전시켰다.
select ...
from ...
pivot (
sum(revenue) Revenue -- pivot 절 (그룹에 적용할 연산)
for month -- pivot for (그룹핑 할 컬럼)
in ('Jan' as Jan, 'Feb', 'Mar', 'Apr') -- pivot in (그룹핑 컬럼에서 필터링 할 값들)
)Pivot 절에서 Alias는 생략 가능, Alias 있는 경우 그룹핑 컬럼 이름 + Alias = 컬럼명
개발자가 되고 싶은 공장장이🛠