SQLD 2-1. SQL 기본

박준우·2023년 1월 18일

오라클성능고도화원리와해법

목록 보기
15/19

1. 관계형 데이터 베이스 개요

SQL의 종류

  • DDL (데이터 정의어)
    DDL은 테이블과 같은 데이터 구조를 정의하는데 사용하는 명령어들이다.

    CREATE: 테이블을 생성한다
    ALTER: 테이블을 수정한다
    DROP: 테이블을 삭제한다
    RENAME: 테이블 이름을 변경한다.

  • DML (데이터 조작어)
    테이블 안의 데이터를 조작하는 명령어 들이다.

    SELECT: 데이터를 조회한다.
    INSERT: 데이터를 입력한다.
    UPDATE: 데이터를 갱신한다.
    DELETE: 데이터를 삭제한다.

  • DCL (데이터 제어어)
    유저에게 데이터를 조작할 수 있도록 권한을 주거나 회수하는 명령어들이다. TCL까지 포함해 DCL로 부르기도 한다

    GRANT: 권한부여
    REVOKE: 권한회수

  • TCL (트랜잭션 제어어)
    트랜잭션별로 작업할 수 있도록 commit, 하거나, rollback할 수 있는 명령어.
    COMMIT: 작업을 저장
    ROOLBACK: 작업을 저장된 지점으로 되돌림

집합 연산자

  1. union: union 연산으로 구현
  2. intersection: intersect 기능으로 구현
  3. difference: 교집합 부문을 제외한부분만 추출 EXCEPT(oracle =MINUS)로 구현
  4. prouduct: CROSS JOIN으로 구현

순수 관계 연산자

  1. select: where로 구현
  2. project: select로 구현
  3. join: natural join으로 구현
  4. divid: 현재 사용 X

데이터의 유형(도메인)

  1. CHAR(N): 고정길이 문자열, N은 글자의 길이이며, N이하의 문자열을 입력할 경우 나머지를 공백으로 채운다. 즉, "AA" = "AA   "
    N사용시 주의사항: N은 엄밀히 말하면 바이트를 뜻하며, 영어는 1글자당 1바이트라 N의 길이와 글자길이가 같지만, 한국어를 사용할 경우 한글자에 1바이트 이상이기 때문에, N이 글자의 길이가 아니다.

  2. varchar(N): 변동길이 문자열, N은 문자의 최대 바이트 값 이다.
    속성마다 다르게 문자열 설정이 가능하기에, char과는 달리 "AA" != "AA  "가 성립한다.

  3. number(m,n): m은 전체 자리수이며, n은 소수점의 개수다.
    즉, m이 8이고, n이 2이면 6자리 정수, 2자리 소수로 유형을 설정한다.

2. select절

1. distinict는 option으로 사용가능하며, 중복된 데이터가 있을 경우 1건으로 처리해 출력한다.

select[distinict] 컬럼명, 컬럼명
from 테이블명


select distinict 성별
from 전국민

<결과>---------------------------
성별
---------------------------------
남성
여성
NULL
-----------------------------------
총 3건이 출력되었습니다.

2. 테이블 전체 보기
만약 테이블의 모든 컬럼을 보고 싶다면 *를 사용하면된다

select *
from 치킨맛집

3. 별명부여하기
칼럼명 뒤에 AS를 붙이면, 혹은 띄어쓰기를 하면 출력되는 결과집합에 별명을 붙여 출력할 수 있다. 


SELECT PLAYER_NAME AS 선수명, POSSION AS 포지션
from 축구선수

<결과>---------------------------
선수명     포지션
---------------------------------
김병지     수비수
박지성     공격수
메시       신
-----------------------------------
총 3건이 출력되었습니다.

AS에 "공백", "특수문자", "대소문자(defalt:영어 대문자)"를 구분해 줘야 할 경우 ""를 사용하여 별명을 지정해야 한다. 

SELECT PLAYER_NAME AS "선수 명", POSSION AS "Postion"

또한, 테이블명에 AS를 설정시 select에 원래 테이블명이 아닌 별명을 테이블명으로 지정해줘야한다.

SELECT A.PLAYER_NAME AS 선수명, A.POSSION AS 포지션
from 축구선수 A, 구단 B
where B.구단코드 = 01

4. number형이나 date형의 경우 사칙연산이 가능하다. 

select 선수명, 키-몸무게
from 축구선수

5. 합성연산자
칼럼과 문자 혹은 칼럼끼리 서로 연결해 출력할 수 있다. 이는 concat()으로 대체 가능하다. 

oracle : ||
sql surver: +

SELECT PLAYER_NAME || "선수,"|| HEIGHT ||"cm," || WEIGHT ||"KG" AS 선수스펙
from 축구선수

<결과>---------------------------
선수스펙     
---------------------------------
김병지선수,175cm,99kg    
박지성선수,185cm,75kg    
메시선수,9999cm,9999kg      
-----------------------------------
총 3건이 출력되었습니다.

3. 함수(oracle)

DB벤더에 내장된 함수에는 단일행함수와 다중행함수가 있다.
단일행함수: 행하나가 입력되면 하나가 출력되는 함수
다중행함수: 행여러개를 입력하여 하나가 출력되는 함수(집계함수, 그룹함수, 윈도우함수)

문자형함수

문자형함수함수설명
LOWER알파벳을 소문자로 바꾼다.
UPPER알파벳을 대문자로로 바꾼다.
CONCAT(m,n)문자열 m,n를 붙인다. ||를 사용해 대체가 가능하다.
SUBSTR(문자열,m,n)문자 m위치에서 시작해 n개의 길이에 해당하는 문자를 돌려준다.
ex) 1. substr('SQLP',-2,2) =LP
LENGTH()문자의 길이를 출력한다.
LTRIM(문자열,지정문자)문자열을 왼쪽부터 확인후 지정문자가 있으면 삭제하고 다른문자가 나오면 exit한다.
ex) 1. LTRIM('SQLP', 'SP') = 'QLP' ///   2. LTRIM('    SQLP','S')='    SQLP'
RTRIM(문자열,지정문자)문자열을 오른쪽부터 확인후 지정문자가 있으면 삭제하고 다른문자가 나오면 exit한다.
TRIM(지정문자 FROM 문자열)문자열을 양쪽 모두 확인후 지정문자가 있으면 삭제하고 다른문자가 나오면 중단한다.LTRIM + RTRIM의 합이다.
ex)1. TRIM('   SQLP ')=SQLP  2.TRIM('S' FROM 'SQLP ')=QLP  3.TRIM('S' FROM '  SQLP  ')=SQLP
REPLACE(문자열,변경대상,변경문자)문자열의 문자일부를 변경한다.
ex) replace('안녕하세요','하','안하')=안녕안하세요, relpace('안녕하세요', '하')=안녕세요

숫자형함수

숫자형함수함수설명
ABS숫자의 절대값을 출력한다.
SIGN숫자가 양수인지, 음수인지 구별한다.
MOD(a,b)a를 b로나누고 나머지를 출력한다.
ex) 1.MOD(15,0)=-15, 2.MOD(15,-4)=3, 3.MOD(-15,-4)=-3
1. 0이면 a그대로 출력3. 양쪽 모두 -면 결과도 -로 출력된다.
CEIL(a)a보다 크거나 같은 최소정수를 출력한다.
FLOOR(a)a보다 작거나 같은 최대정수를 출력한다.
ROUND(a,b)a를 소수점 b자리에서 반올림해 출력한다.
TRUNC(a,b)a를 소수점 b자리에서 잘라 버리고 출력한다.
POWER(a,b)a의 b제곱을 출력한다.
SORT(a)a의 제곱근을 출력한다.

날짜형함수

날짜형함수함수설명
SYSDATE현재날짜와 시간을 출력한다.
EXTRACT(시간단위 From sysdate)날짜데이터에서 시간단위를 골라 출력한다.
EXTRACT 시간단위는 YEAR, MONTH, DAY, Hours, Minutes, Seconds 등이 있다.
TO_NUMBER(TO_CHAR(sysdate, 'yyyymmdd')날짜데이터에서 시간단위를 골라 형식대로 출력한다.
TO_CHAR(SYSDATE, "YYYYMMDDHH24MISS")의 형태로 출력가능하다.

TO_CHAR에TO_NUMBER처리를 해주는 이유는, 사칙연산을 위해서다.

날짜형 함수의 사칙연산

계산시 +,-1은 1일을 더하거나 뺌을 뜻한다. 만약 1시간을 계산하고 싶다면, +,-1/24을 해주면 된다. 마찬가지로, 분을 계산하고싶다면 +,-1/24/60을 해주면 된다.

변환형함수

날짜형함수함수설명
TO_NUMBER(문자열)문자를 숫자로 변환해 출력한다.
TO_CHAR(숫자형, 'format')숫자를 format 형태의 문자로 변환해 출력한다.
예시)to_char(1000,'$999,999) => 9,999
TO_DATE(문자열, 'format')문자를 format 형태의 숫자로 변환해 출력한다.

CASE 표현

CASE표현은 아래처럼 두가지 경우로 사용한다.
경우 1. IF조건으로 하나의 변수로, =조건을 사용하는 경우

select 음식
CASE 음식
	WHEN (조건) THEN (조건과 일치할 때 출력할 것)
	WHEN (조건) THEN (조건과 일치할 때 출력할 것)
	ELSE (조건과 일치하는게 없을 때 출력)
END AS 별명

ex)----------------------------------------------

select 치킨집, 
CASE 주소
	WHEN (서울) THEN (서울치킨)
	WHEN (경기) THEN (경기치킨)
	ELSE (기타치킨)
END AS 치킨집주소

의미: 음식이 조건과 같은가?

경우 2. IF조건으로, 여러변수로 각종 비교 조건을 사용하는 경우


select 
CASE 
	WHEN (변수와 조건절) THEN (조건절이 참일때 출력할 것)
	WHEN (변수와 조건절) THEN (조건절이 참일때 출력할 것)
	ELSE (조건과 일치하는게 없을 때 출력)
END AS 별명

ex)----------------------------------------------

select 치킨집,
CASE 
	WHEN (치킨값>=20000) THEN (비싼치킨)
	WHEN (치킨값>=18000) THEN (보통치킨)
	ELSE (가성비치킨)
END AS 치킨평가

CASE 표현은 DECODE와 의미가 같다.

**DECODE(조건, 조건1, 출력1, 조건2, 출력2 ...)
조건과 조건1이 같을때 출력1을 출력한다.
조건과 조건2가 같을때 출력2을 출력한다.

NULL 함수

NULL은 공백이 아닌 하나의 결과행이다. NULL을 계산하려하면 결과는 NULL만이 나온다.
참조: 공집합은 결과건수가 전혀 없을 때 출력된다.

날짜형함수함수설명
NVL(a,b)a가 null이면 b를 출력한다. 단, 데이터타입이 서로 같아야한다.
NULLIF(a,b)a와 b가 같으면 NULL을, 같지 않으면 a를 출력한다.
COALESCE(a,b,c...)a부터 읽어서 null이 아닌 최초의 데이터를 출력한다.

공집합과 NULL

NVL함수는 NULL을 대체하는 함수지 공집합을 대체하는 함수가아니다.
결과값이 공집합일때 0을 출력하고자 한다고 가정할 때, NVL(a,0)계산 시, 0이 아니라 공집합을 출력한다.
사용자의 의도대로 0을 출력하고 싶다면, NVL(max(a),0)처럼 집계함수를 사용하면 0을 출력한다.

4. WHERE 절

where문 연산자의 종류

연산자의 우선순위

  1. ()
  2. 비교연산자(<,>,>=,<= 등), SQL 연산자(between a and b, in, like, is null)
  3. NOT연산자
  4. AND
  5. OR

연산자의 우선순위를 무시하면 벌어지는 일

예를 들어, A가 가 OR 나 이면서, B=다, D=라, E=마 를 출력하고자 한다.

select name
from table
where A = "가" OR A="나"
and B= "다"
and C= "라"
and D= "마"

위 문장은 잘못된 답을 낸다. 즉, 아래처럼 해석을 하는 것이다. 
where A = "가" OR (A="나" and B= "다" and D= "라" and E= "마")

즉, A를 "가"라고 가정하면, B= "다" and D= "라" and E= "마"를 만족하지 않은 값도 출력된다. 
따라서 쿼리를 아래처럼 () 해주어야 한다.

select name
from table
where (A = "가" OR A="나")
and B= "다"
and C= "라"
and D= "마"

LIKE연산자와 문자표현식

LIKE는 다음과 같이 사용할 수 있다.

_: 1글자를 말한다.
%: ~로 이어지는 문자열을 뜻한다.
where 이름 LIKE 'M%S'= M으로 시작하고 S로 끝난다.
where 이름 LIKE '%M%'= 중긴에 M이 들어간다.
where 이름 LIKE '_M_'= 3글자면서 2번째 칸에 M이 들어가는 글자를 찾는다. 

1. 특수문자(%,_)를 포함한 레코드 검색 = ESCAPE문자를 사용한다.
insert into 연습 values ('%A');
insert into 연습 values ('_A');

where 문자열 LIKE '#%%' ESCAPE '#'; - ESCAPE문자는 마음대로 지정이 가능하다.
= %로 시작하는 문자열을 출력한다.

where 문자열 LIKE '__A%' ESCAPE '_';
= _A로 시작하는 문자열을 출력한다.

만약 LIKE 문자표현식을 테이블에 담아 조인하는 형식으로 형식이 2개이상이면 어떻게할까?

A. 문자열 LIKE B.표현식;

이 때는 표현식 만큼 카테시안 곱해서 표현식을 만족하는 결과를 찾아 출력한다.

NOT연산자의 분배법칙

NOT연산시 괄호내의 값이 AND, OR일 경우 그 두 논리연산자 값이 서로 바뀐다.

where NOT(SAL <= 300 OR SAL>=500)
= SAL > 300 AND SAL <500

where NOT(SAL <= 300 AND SAL>=500)
= SAL > 300 OR SAL <500

where NOT(SAL BETWEEN 300 AND 500)
= NOT(SAL>=300 AND SAL<=500)
= SAL<300 OR SAL >500;

where NOT (이름 IN ('가가가', '나나나'))
= NOT(이름 = 가가가 OR 이름 = 나나나);
= 이름 != 가가가 AND 이름 != 나나나;

5. GROUP BY HAVING 절

집계함수

집계함수는 다음과 같은 곳에 사용할 수 있다. select절, having절, order by절
즉, where절에는 사용 불가능 하다.

날짜형함수함수설명
COUNT(*)NULL값을 포함한 모든 행의 수를 출력한다.
COUNT(표현식)NULL값을 제외하고 표현식을 만족하는 것만 출력한다. ex) COUNT(매장)
SUM(표현식)NULL값을 제외한 합계를 출력한다.
MAX(표현식)표현식의 최대값을 출력
MIN(표현식)표현식의 최소값을 출력
STDDEV(표현식)표현식의 표준편차를 출력한다.
VARIANCE/VAR(표현식)표현식의 분산을 출력한다.

COUNT를 제외한 위의 집계함수는 (DISTINICT,표현식)의 형태로도 사용할 수 있으며, 이는 같은 값을 하나의 데이터로 간주하여 집계한다. 는 뜻이다.

GROUP BY 절

데이터들을 작은 그룹으로 분류해 소그룹에 대한 "항목별"로 통계정보를 얻을 때 사용한다. HAVING절은 GROUP BY절의 기준항목이나 소그룹의 집계함수를 이용한 조건을 표시할 때 사용한다.

GROUP BY 를 사용한 테이블의 매커니즘

  1. FROM을 통해 테이블을 찾는다.
  2. where을 통해 조건을 확인한다.
    (이 때 where절을 통해 최대한 줄은 결과집합을 group by하는것이 좋다)
  3. GROUP BY를 기준으로 소그룹으로 묶어낸다.
  4. 묶어낸 소그룹들 중 HAVING 조건을 만족하는 부분만 가져온다.
  5. 가져온 데이터를 select 절의 집계함수에서 계산한다.

GROUP BY절과 HAVING절의 특징

  1. 집계함수 사용시 GROUP BY로 묶은 것을 기준으로 선택하여 집계함수를 사용한다.
  2. HAVING절은 GROUP BY한 결과에서 HAVING조건에 맞는것만 출력한다는 뜻이다.
  3. HAVING절은 GROUP BY절 뒤에온다.
  4. GROUP BY절에는 AS를 사용할 수 없다.
  5. 집계함수는 where절에 사용할 수 없다.
  6. 집계함수는 NULL을 제외하고 계산한다.

GROUP BY 사용법

예를 들어 포지션별 평균키를 찾고싶다. 
SELECT POSITTION AS 포지션, AVG(HEIGHT) AS 평균키
form 선수

위의 쿼리는 오류가 난다. 
왜냐하면 SELECT절에서 사용한 집계함수는 GROUP BY를 해주 않은 쿼리에서 쓰면 안되기 때문이다. 
아래는 GROUP BY에 별명을 사용하였기에 나타난 오류이다.

SELECT POSITTION AS 포지션, AVG(HEIGHT) AS 평균키
from 선수
GROUP BY 포지션

HAVING 사용법

예를 들어 포지션별 180cm 이상인 사람들의 평균키를 구할 떄 where절에 집계함수를 쓰면 오류(select, having만 가능)
가 나타난다. 이 때, select에 사용된 집계함수를 HAVING절에 사용할 수 있다.

SELECT POSITTION AS 포지션, AVG(HEIGHT) AS 평균키
from 선수
GROUP BY 포지션
HAVING AVG(HEIGHT) >= 180;

문제에서 SELECT절 집계함수에 사용한 AS를 HAVING절에 사용하고자 할 경우 having이 select보다 먼저 처리되기에 오류가 나타난다. 즉, 아래처럼 해서는 안된다.

where 평균키 >= 180;

HAVING 혼자서 사용하기

SELECT SUM(COL) FROM SAMPLE HAVING SUM(COL)>50;

위 문장은 GROUP BY가 없지만, 집계함수는 있다. 즉, SQL문 전체를 집계한것이다.
이 때는 group by없이 having 혼자서 사용할 수 있다.
만약 전체 sum(col)합계가 50이상이면 결과를 출력할 것이다.

CASE GROUP BY 사용법

CASE GROUP BY는 데이터를 melt,cast하는데 좋다.

예를 들어 부서별로 월별 입사자의 평균 급여를 알고 싶다.
1. 개별 데이터 확인
select 사원명, 부서번호, extract(month from 입사시간) as 입사월, 급여
from emp

2. 월별데이터 구분
select 사원명, 부서번호, 
CASE month WHEN 1 THEN sal END AS 1월입사,
CASE month WHEN 2 THEN sal END AS 2월입사,
.
.
CASE month WHEN 12 THEN sal END AS 12월입사
FROM (select 사원명, 부서번호, EXTRACT(MONTH FROM 입사시간) AS 입사월, 급여
	  from emp);
      
3, 부서별데이터 집계(GROUP BY)
select 사원명, 부서번호, 
CASE month WHEN 1 THEN sal END AS 1월입사,
CASE month WHEN 2 THEN sal END AS 2월입사,
.
.
CASE month WHEN 12 THEN sal END AS 12월입사
FROM (select 사원명, 부서번호, EXTRACT(MONTH FROM 입사시간) AS 입사월, 급여
	  from emp);
GROUP BY 부서번호

집계함수와 NULL처리

집계함수를 사용할 때 NULL에 대한 계산은 빼고 처리한다고 했다. 즉 100명중 10명이 NULL이면 90명에 대해서만 계산이 이루어진다. 이처럼 다중행함수를 사용하는 중에는 굳이 NVL함수를 사용할 필요가 없다. 예를 들어 NVL(a,0)이나, SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0)처럼 else절에서 0을 지정하면서까지 null을 0으로 굳이 바꿔줄 필요가 없다는 뜻이다.

6. ORDER BY 절

ORDER BY는 쿼리 가장 마지막에 사용한다. 즉, (from, where, group by, having, select, order by) 순이다. 주로 ORDER BY DESC(내림차순)를 사용하는데 가장 큰 값부터 출력해 점점 작은 값으로 정렬하며 출력한다.

이 때, null의 경우 오라클은 가장 크다고 생각하고 가장 먼저 출력되지만, sql surver는 null을 가장 작은값으로 생각하기에, 가장 나중에 마지막에 출력된다.

가장 최근의 날짜를 보고 싶다면 ORDER BY 날짜 DESC를 해주어야 한다.
DESC가 없으면 가장 오래된 날짜부터 출력된다.

인라인 뷰를 사용한 order by

  1. select절에 사용하지 않은 컬럼이라도 테이블을 참조 했다면 ORDER BY 할 수 있다. 
예를 들어 emp 테이블 안에 mgr이 있다면 아래 쿼리는 출력 가능하다.

select empno, ename
from emp
order by mgr;
  1. 인라인 뷰의 select절에서 정의한 컬럼은 메인쿼리에도 사용할 수 있다. 
즉, 아래처럼 인라인뷰의 emp테이블에서 정의한 empno는 정렬된채로 메인쿼리에서 그대로 가져다 쓸 수 있다.

select empno
from (select empno, ename
	  from emp
      order by mgr);
  1. 서브쿼리에서 선택되지 않은 서브쿼리의 컬럼들은 서브쿼리를 벗어나면 사용할 수 없게 된다.

select mgr
from (select empno, ename
	  from emp
      order by mgr);

메인쿼리에서 mgr을 원하지만, 서브쿼리는 mgr을 기준으로 정렬된 empno와 ename밖에 가지고 있지 않기에 오류가 나온다.
  1. 3번의 인라인뷰와 마찬가지로 group by또한 집계함수 사용을 위해 그룹핑된 새로운 임시 테이블을 만들어 낸다고 생각하면 된다. 이 때 개별데이터는 저장하지 않으며, order by에 개별데이터를 기준으로 정렬하려고 하면, 오류가 나온다. 
예를 들어
select job, sal
from emp
group by job
order by sal;
---------------------------
select: sal에서 오류발생

왜냐하면 order by보다 group by가 먼저 처리되는데, sal이란 개별데이터는 이미 존재하지 않기 때문이다. 
즉, 집계함수를 기준으로 정렬하여야 정상적으로 처리된다.

select job, sum(sal) 
from emp
group by job
having sum(sal) >= 5000
order by sum(sal);
----------------------------
정상적으로 출력

7. 조인

두 개 이상의 테이블들을 연결해 데이터를 출력하는 것

조인은 두개의 집합간에만 일어난다. 아래는 A,B,C table을 조인한 예이며, ((a = b) = c) OR ((b = c) = a)의 순서로 조인이 이루어진다. 

select 지점, 메뉴
from A, B, C
where A.지점 = B.지점
and B.지점 = C.지점

또한 위 예제는, EQUI(=)을 사용한 조인이다. 두 테이블이 PK-FK로 연관관계를 갖거나 논리적으로 칼럼의값이 정확히 같을 때 EQUI(=)을 사용해 조인한다.

Non EQUI JOIN

Non EQUI JOIN은 두개의 테이블 간에 연관관계가 있으나, 칼럼의 값들이 서로 일치하지 않는 경우에 사용한다. 이 때 EQUI(=)가 아닌, Non EQUI JOIN(>,<=,<,<=,between)을 사용해 조인한다. 예를 들어 어떤 사원이 받고있는 급여가 어느 등급에 속하는지 알고싶다.

급여등급 table 과 사원 table은 아래와 같다.

사원 table에서 급여가 급여등급으로 표시되기 위해서는 = 연산자로 조인할 수 없다. 대신 between연산자로 조인할 경우 아래와 같이 조인이 가능해진다.

select A.NAME, A.JOB, A,SAL, B.GRADE
from EMP A, GRADE B
where A.SAL BETWEEN B.LOSAL AND B.HISAL

NON EQUEL 조인시 주의할 점은 조인컬럼을 SELECT 절에 사용시 테이블명을 제대로 명시해야한다는 점이다.
EQUEL 조인은 조인컬럼에 대해 A.조인컬럼, B.조인컬럼의 값이 똑같이 출력된다.
NON EQUEL 조인시 A.조인컬럼, B.조인컬럼은 다른 결과를 출력한다.

OUTER JOIN

EQUI(=)이나 Non EQUI JOIN과 달리, 조인조건을 만족하지 않는 행들도 함께 출력하는 조인이다.
즉, 테이블 a와 테이블 b를 조인하면, 아래 그림처럼 조인에 성공한 것뿐만 아니라, 실패한 행들도 함께 출력한다.

사용법은 아래와 같으며 기준테이블은 (+)표시가 있는 반대편 테이블이다.

select a.경기장명 a.경기장코드 a.홈팀코드 b.팀명
from 경기장 a, 홈팀 b
where b.팀코드(+) = a.홈팀코드

기준 테이블인 a table의 레코드를 모두 가져온다. 그 후, b테이블과 조인하여, 홈팀과 경기장 팀코드가 일치하는 것을 출력하고, b 테이블에서 가져온 조인에 실패한 칼럼들은 모두 NULL로 채워놓는다.

추가로 3개의 테이블을 조인하는 경우에도 기준테이블은 그대로 출력되며, 나머지는 NULL로 채워놓는다.

8. 표준 조인(ANSI/ISO)

표준조인은 ANSI/ISO SQL에서 사용할 수 있는 조인이다. FROM절에서 ON,USING 조건절을 사용해 조인할 수 있도록 했으며, SQL SURVER의 경우 USING조건절은 사용할 수 없는 대신 ON 조건절을 사용하는 것으로 FROM절에서도 조인문을 사용할 수 있도록 지원한다.

표준 INNER JOIN

<where 조건절에서 사용>
select A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE B.DEPTNO = A.DEPTNO 

<from 조건절에서 사용>
select A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A INNER JOIN DEPT B = EMP A JOIN DEPT B
ON B.DEPTNO = A.DEPTNO 

이 때 INNER는 생략이 가능하다.

표준 NATURAL JOIN (SQL SURVER 지원 X)

동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=)조인을 하는 것을 의미한다.
즉, A테이블과 B테이블 사이에서 같은 이름을 가진 모든 속성들을 대상으로 정확히 일치시 조인시킨다는 뜻이다. 

select A.EMPNO, A.ENAME, DEPTNO, B.DNAME
FROM EMP A NATURAL JOIN DEPT B;

A테이블과 B테이블 중 같은 칼럼인 것을 대상으로 조인한다. 
이 때, 자연 조인 대상이 되는 컬럼은 select에 테이블명을 사용해서는 안되며  별명지정도 불가능하다. 
위 컬럼은 deptno가 조인컬럼이 되었다.

또 하나의 차이점은 출력 결과이다.

Natural 조인을 할 경우 from절에 기술된 순서대로 출력하는 것이 아닌, 두 테이블의 조인 컬럼이 가장 먼저 출력된다. 또한, inner조인과 달리 select * 사용시 deptno가 두번 출력되지 않고 1번만 출력된다. 또한,

natural join using 조건절 (SQL SURVER 지원 X)

NATURAL JOIN 중에 using조건절(조인컬럼)을 사용하면, 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN이 가능하다. 

select A.EMPNO, A.ENAME, DEPTNO, B.DNAME
FROM EMP A JOIN DEPT B;
USING (DEPTNO)

특이한 점은 USING 사용시 NAUTRAL 구문을 생략해 주어야 한다는 점이다.
즉 위의 join은 inner join이 아니라 natural조인이다.

on 조건절

on 조건절을 사용하면, 컬럼명이 서로 다르더라도 조인조건을 사용할 수 있는 장점이 있다.
이 때 반드시 테이블 명을 지정해 주어야 한다.

select A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A Natural JOIN DEPT B;
ON (B.DEPTNO = A.DEPTNO)
where A.dpatno = 300

표준 CROSS JOIN

모든 데이터의 조합을 의미한다. 예를 들어 x=(1,2,3) y=(A,B) 일 때 나올 수 있는 조합의 수는 6개이다. 아주 가끔 리포트작성이나 튜닝을 하기위해 사용한다.

select A.ENAME, B.DNAME
FROM EMP A CROSS JOIN DEPT B;

OR

select A.ENAME, B.DNAME
FROM EMP A, DEPT B;

아래처럼 where 없이 생략하면 cross join과 같은 카테시안 곱 결과를 출력한다.

표준 OUTER JOIN

오라클에서는 조인컬럼 뒤에 (+)를 사용하여 조인하였다. 이 때 (+)표시 반대편의 테이블이 곧 모든 데이터를 가져오는 기준테이블이라고 하였다. LEFT OUTER JOIN을 사용하면 왼쪽 테이블을 기준테이블로 정하겠다는 의미이며, RIGHT OUTER JOIN은 오른쪽 테이블을 기준 테이블로 사용하겠다는 의미이다. 

select A.STADIUM_ID, A.STADIUM_NAME, A.HOMETEAM_ID, B.TEAM_NAME 
FROM EMP A LEFT JOIN DEPT B;
on B.TEAM_ID = A.HOMETEAM_ID

*outer는 생략 가능하다.

이때 조인에 성공하지 못한 기준테이블 A는 그대로 출력하되 B테이블 출력속성들을 NULL 로 표기하여 출력한다.

표준 FULL OUTER JOIN

A 테이블과 B테이블의 합집합을 출력한다.

select A.STADIUM_ID, A.STADIUM_NAME, A.HOMETEAM_ID, B.TEAM_NAME 
FROM EMP A FULL JOIN DEPT B;
on B.TEAM_ID = A.HOMETEAM_ID

*outer는 생략 가능하다.
profile
박준우
DB가 좋아요
다음 포스트

SQLP 2-1 SQL 기본과 활용 문제풀이

0개의 댓글