[SQLD] SQL 활용 - 1

💡 SQLD 자격증 시험 대비 학습한 내용을 요약정리합니다.

표준 조인

SQL 관계형 대수 (8가지)

• 일반 집합 연산자(4가지)
• 순수 관계 연산자(4가지)

일반 집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법으로 여러 개의 질의의 결과를 연결하여 하나의 결합하는 방식을 사용한다. 즉, 2개 이상의 질의 결과를 하나의 결과로 만들어주는 것이다.

일반 집합 연산자를 사용하는 상황

1. 서로 다른 테이블에서 유사한 형태의 결과를반환하는 것을 하나의 결과로 합치고자 할 때
2. 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때

일반 집합 연산자의 제약 조건

• SELECT 절의 칼럼수가 동일해야 함
• SELECT절의 동일 위치에 존재하는 컬럼의 데이터 타입이 상호 호환 가능해야 함

집합 연산자의 종류

• UNION : 합집합(중복된 행은 하나로)
• UNION ALL : 합집합(중복된 행 모두 결과로 표시)
• INTERSECT : 교집합(중복된 행은 하나로)
• EXCEPT(MINUS) : 차집합(중복된 행은 하나로)
• CROSS JOIN : 곱집합(PRODUCT), JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합, m*n건의 데이터 조합 발생

💡 출력 결과가 같다면 UNION과 UNION ALL 중 UNION ALL 사용을 권고한다.

순수 관계 연산자

관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자

• SELECT 연산은 WHERE 절로 구현
• PROJECT 연산은 SELECT절로 구현
• JOIN 연산은 다양한 JOIN 기능으로 구현
• DIVIDE(나눗셈)연산은 현재 사용 x

💡 SELECT 연산과 SELECT절은 서로 다른 의미

FROM절 JOIN 형태

• INNER JOIN
• NATURAL JOIN
• USING 조건절
• ON 조건절
• CROSS JOIN
• OUTER JOIN

INNER JOIN(내부)

• JOIN 조건에서 동일한 값이 있는 행만 반환.

• WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시로 USING 조건절이나 ON 조건절을 필수로 사용.

• 기본옵션이므로 생략이 가능

• CROSS JOIN, OUTER JOIN과 같이 사용 불가

• SELECT 컬럼명 FROM 테이블명 WHERE 조건;

• SELECT 컬럼명 INNER JOIN 테이블명 ON 조건;

• SELECT 컬럼명 FROM 테이블명 JOIN 테이블명 ON 조건;

NATURAL JOIN(INNER JOIN 하위 개념)

두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI(=) JOIN을 수행

• USING 조건절, ON 조건절, WHERE절에서 JOIN 조건을 정의할 수 없다
• JOIND이 되는 테이블의 데이터 성격(도메인)과 컬럼명 등이 동일 해야 한다.
• SQL Server에서 지원 X

SELECT 컬럼명 FROM 테이블명 NATURAL JOIN 테이블명;

USING 조건절

FROM 절의 해당 조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 EQUI(=) JOIN할 수 있다.

• JOIN 컬럼에 대해 별칭, 테이블 명과 같은 접두사 사용 금지.
• SQL Server에서 지원 X

SELECT * FROM 테이블명 JOIN 테이블명 USING (컬럼명);

ON 조건절

• JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)을 분리하여 이해가 쉽다.
• 컬럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.
• 별칭이나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 컬럼을 논리적으로 명확하게 지정해야 한다.

ON 조건절 + 데이터 검증 조건 추가
ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수 있으나, 검색 조건 목적인 경우 WHERE 절에 사용할 것을 권고.

CROSS JOIN

일반 집합 연산자의 PRODUCT의 개념, 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합.

• 양쪽 집합의 M*N건의 데이터 조합 발생.

OUTER JOIN(외부 조인)

JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용.

• JOIN 조건을 FROM 절에서 정의-> USING, ON 조건절 필수 사용

LEFT OUTER JOIN(좌 -> 우)

조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다.

• 우측 JOIN 컬럼에서 같은 값이 없는 경우 : 우측 테이블에서 가져오는 컬럼들을 null 값으로 채움

RIGHT (OUTER) JOIN(우 -> 좌)

조인 수행 시 먼저 표기된 우측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 좌측 테이블에서 JOIN 대상 데이터를 읽어온다.

• 좌측 JOIN 컬럼에서 같은 값이 없는 경우 : 좌측 테이블에서 가져오는 컬럼들을 null 값으로 채움

FULL (OUTER) JOIN

좌, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성.

• LEFT, RIGHT OUTER JOIN 결과의 합집합
• 중복 데이터는 삭제

---

계층형 질의와 셀프 조인

계층형 데이터

동일 테이블에서 계층적으로 상위, 하위 데이터가 포함된 데이터로 엔티티를 순환관계 데이터 모델로 설계할 경우 발생.

오라클 계층형 질의 구문

• START WITH 절 : 계층 구조 전개의 시작 위치 지정, 루트 데이터 지정
• CONNECT BY 절: 다음 전개될 자식 데이터를 지정, 자식 데이터는 해당 절에 주어진 조건을 만족해야 한다.
  • PRIOR : 현재 읽은 컬럼 지정.
    PRIOR 자식 = 부모 : 자식->부모, 순방향
    PRIOR 부모 = 자식 : 부모 -> 자식, 역방향
  • NOCYCLE : 사이클이 발생한 이후의 데이터 전개 X

💡 사이클(CYCLE) : 이미 나타났던 동일한 데이터가 전개 중에 다시 나타나는 현상. -> 런타임에러 발생!

• ORDER SIBLINGS BY 절 : 형제 노드(동일 레벨) 사이에서 정렬 수행
• WHERE : 모든 전개를 수행한 후, 지정된 조건을 만족하는 데이터만 추출.

계층형 질의의 가상 컬럼

• LEVEL : 루트 데이터 - 1, 그 하위 데이터 - 2, 리프 데이터까지 1씩 증가한다.
• CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1, 그렇지 않느면 0
• CONNECT_BY_ISCYCLE : 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0 (CYCLE 옵션을 사용했을 때만 사용)

계층형 질의에서 사용되는 함수

사용자 편의성을 제공하기 위해 사용한다.

• SYS_CONNECT_BY_PATH : 루트 - 현재 데이터까지의 경로
• CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시. 단항 연산자

셀프 조인

• 동일 테이블 사이의 조인.
• FROM 절에 동일 테이블이 두 번 이상 나타남
• 식별을 위해 반드시 테이블 별칭을 사용

---

서브쿼리

서브쿼리

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
• 알려지지 않은 기준을 이용한 검색을 위해 사용
• 메인쿼리가 서브쿼리를 포함하는 종속 관계
• 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
• 서브쿼리는 서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성

서브쿼리 사용의 주의사항

1. 서브쿼리를 괄호로 감싸서 사용
2. 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
   단일 행 비교 연산자 : 서브쿼리 결과 <= 1건
   복수 행 비교 연산자 : 서브쿼리 결과 건수와 무관
3. 서브쿼리에서 ORDER BY 사용 불가

💡 ORDER BY절은 SELECT 절에서 오직 한 개만 올 수 있음 -> 메인쿼리 마지막 문장에 위치해야 함

서브쿼리 사용 가능한 곳

• SELECT 절
• FROM 절
• WHERE 절
• ORDER BY 절
• INSERT문의 VALUES 절
• UPDATE문의 SET절

서브쿼리의 종류

• Un-correlated(비연관) : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태
• Correlated(연관) : 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태

반환하는 데이터 형태에 따른 서브쿼리 분류

1. Single Row(단일 행) 서브쿼리
2. Multi Row(다중 행) 서브쿼리
3. Multi Column(다중 컬럼) 서브쿼리

단일 행 서브쿼리

• 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리
• 단일 행 비교 연산자(=, <. <=, >, >=, <>)와 함께 사용

다중 행 서브쿼리

• 서브쿼리의 실행 결과가 여러 건인 서브쿼리
• 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용
• IN (서브쿼리) : 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건
• 비교 연산자 ALL (서브쿼리) : 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건
• 비교 연산자 ANY (서브쿼리) : 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건(SOME 과 동일)
• EXISTS (서브쿼리) : 서브쿼리의 결과를 만족하는 값이 존재 여부 확인하는 조건

다중 컬럼 서브쿼리

• 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교 되는 것을 의미
• SQL Server에서 지원 X

연관 서브쿼리

• 서브쿼리 내에 메인쿼리 컬럼이 사용된 서브쿼리

스칼라 서브쿼리

• SELECT 절에서 사용하는 서브쿼리
• 한 행, 한 컬럼만을 반환
• 단일 행 서브쿼리, 2건 이상 반환 X

인라인 뷰(Inline View)

• FROM 절에서 사용되는 서브쿼리.
• 서브쿼리의 결과가 마치 실행 시 동적으로 생성된 테이블 인 것 처럼 사용된다.
• SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰 -> DB에 저장 X
• 인라인 뷰는 테이블 명이 올 수 있는 곳에서 사용
• JOIN 방식을 사용하는 것과 같으며 인라인 뷰의 컬럼은 SQL문에 자유롭게 참조 가능

뷰(View)

• 실제 데이터를 가지고 있지 않고, 뷰 정의만을 가지고 있다.
• 뷰 정의를 참조하여 DBMS 내부적으로 질의를 재작성하여 질의를 수행
• 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블이라고도 한다.

뷰 사용의 장점

• 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
• 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성. 해당 형태의 SQL문을 자주 사용할 떄 뷰를 이용하면 편리하게 사용 가능
• 보안성 : 숨기고 싶은 정보가 있다면 뷰를 생성할 때 해당 컬럼을 빼고 생성하여 정보를 숨길 수 있다.