[프로그래머스 SQL 스터디 2기] 5주 차 TIL/후기

1. [프로그래머스 SQL 스터디 2기] 1주 차 TIL/후기

2. [프로그래머스 SQL 스터디 2기] 2주 차 TIL/후기​

3. [프로그래머스 SQL 스터디 2기] 3주 차 TIL/후기

4. [프로그래머스 SQL 스터디 2기] 4주 차 TIL/후기

5. [프로그래머스 SQL 스터디 2기] 5주 차 TIL/후기

6. [프로그래머스 SQL 스터디 2기] 6주 차 TIL/후기

 

5주 차 서브쿼리와 DDL 무결성 제약조건에 대해서 학습했다.

 

단테님이 4주 차를 진행하면서 이번 강의에서 서브쿼리도 중요하지만, DDL 무결성 제약조건 수업이 정말 중요하다고 하셨다.

 

DDL 제약조건은 데이터베이스의 근간이기 때문에 그렇게 말씀하신 거 같다!

 

개인적으로 서브쿼리는 지금도 어렵긴 하지만 정리하면서 다시 리마인드를 하자!!!

 

서브쿼리

서브쿼리란 메인쿼리안에 포함되어 있는 쿼리를 서브쿼리라고 한다.

하단은 메인쿼리와 서브쿼리가 어떻게 이루어지는 보여주기 위해 표현한 WHERE절 서브쿼리의 구조이다.

 

SELECT   *  /* 메인 쿼리 */
FROM     main_query
WHERE	 main_query_column IN  (    /* 서브 쿼리 */
                                    SELECT   sub_query_column
                                    FROM     sub_query
                                )

  

서브쿼리는 다양한 질의문(SELECT, WHERE, FROM , HAVING, ORDER BY)과 갱신문(INSERT의 VALUES절, UPDATE의 SET절)에서 사용이 가능하다.

 

1. SELECT 절 ( Scalar SubQuery )

• scalar값은 단일 값을 말하며, 하나의 값을 리턴하는 형태이다.
• 집단 함수(COUNT, SUM...)를 통해서 새로운 컬럼을 생성하는 것이 주목적

2. WHERE 절 ( Where절 SubQuery )

• 서브쿼리의 결과로 메인쿼리의 테이블을 필터링하는 것이 주목적

3. FROM   절 ( Inline View SubQuery )

• 다른 서브쿼리들과는 다르게 메인쿼리에서 서브쿼리의 컬럼들을 자유롭게 사용이 가능하다.
• WITH절과 동일하게 임시 테이블을 생성해서 사용하는 것이 주목적

 

서브쿼리 파트에서는 하단 리스트들을 다뤄본다.

 

1. 단일 값, 다중 값, 다중행 서브쿼리란?
2. 연관 서브쿼리와 비연관 서브쿼리란?
3. 연관 서브쿼리와 조인의 차이점은?

 

단일 값, 다중 값, 다중행 서브쿼리란?

WHERE절 서브쿼리에서는 결괏값을 3가지 방식으로 반환한다.

 

• 단일 값(Single value subQuery)
  • scalar subQuery
  • 서브 쿼리의 결과가 1개 이하인 단일 값
  • 메인 쿼리의 WHERE문에서 단일 값 비교 연산자(=, <>, <...)로 비교가 가능
• 다중값(Multi-value subQuery) 
  • column subQuery
  • 서브 쿼리의 결과가 scalar 값 혹은 1-tuple의 집합인 값, 즉 여러 개의 행과 한 개의 열을 반환하는 형태
  • 메인 쿼리의 WHERE문에서 단일값 비교 연산자 + 한정자(ANY, SOME, ALL)로 비교가 가능
• 다중행(Multi-row subQuery)
  • table subQuery
  • 서브 쿼리의 결과가 n-tupled의 집합, 즉 여러개의 행, 열을 반환하는 형태
  • 메인 쿼리의 WHERE문에서 멤버쉽 연산자(IN), 존재 정량자(EXISTS)로 비교가 가능

 

 

단일값 예시 - Ex) 고객 테이블에서 대출한도(creditLimit)의 최댓값을 출력해라.

하단 쿼리를 실행하면 하나의 단일 값(scalar value)이 리턴된다.

 

SELECT   MAX(creditLimit)
FROM	 customers;

 

다중 값 예시 - Ex) 고객 테이블에서 국가(country)가 'USA'에 사는 고객의 id를 출력해라.

한 개의 열과 여러 개의 행을 가진 다중 값이 리턴된다.

scalar 값의 집합이 리턴된다.

 

SELECT   customerId
FROM     customers
WHERE    country = 'USA';

 

다중행 예시 - Ex) 고객 테이블에서 국가(country)가 'USA'에 사는 고객의 id와 도시(city)를 출력해라.

여러 개의 행, 열의 값인 table 형태의 값인 다중 행이 리턴된다.

 

SELECT   customerId,
         city
FROM     customers
WHERE    country = 'USA';

 

 

연관 서브쿼리와 비연관 서브쿼리란?

• 연관 서브쿼리
  • 서브쿼리에서 메인쿼리의 컬럼을 사용
  • 메인쿼리의 튜플 수만큼 서브쿼리가 실행된다.
• 비연관 서브쿼리
  • 서브쿼리에서 메인쿼리의 컬럼을 사용하지 X
  • 서브쿼리는 단 한 번 실행된다.

 

연관 서브쿼리는 메인쿼리 테이블의 튜플을 필터링하는 역할을 한다.

 

연관 서브쿼리의 동작

1. 메인쿼리의 튜플의 개수만큼 반복한다. 

• 메인쿼리(n) 튜플의 개수만큼 서브쿼리(m) 전체 튜플을 차례로 순회한다. ( n * m )
• 중첩 반복문(nested loop)과 동일 

2. 서브쿼리에서 사용한 메인쿼리의 컬럼의 상수 화해서 조건에 맞는 튜플을 검색한다.

•  sub.price= main.price(10000)

3. 조건에 맞는 튜플들로 서브쿼리를 진행한다.

4. 서브쿼리에서 나온 결과로 메인쿼리의 WHERE 절에 비교해서 일치하면 해당 메인쿼리의 튜플을 선택한다.

 

연관 서브쿼리와 비연관 서브쿼리중에서 누가 더 성능이 좋을까?

연관 서브쿼리는 메인쿼리의 컬럼을 서브쿼리의 WHERE절에서 사용해서 비교하기에 메인쿼리의 튜플 수 만큼 서브쿼리가 실행이 되지만,

비연관 서브쿼리는 메인쿼리의 컬럼을 서브쿼리에서 사용하지 않기에 단 한 번만 실행이 된다. 보통 비연관 서브쿼리가 성능이 더 좋다.

 

 

연관 서브쿼리와 조인의 차이점은?

• 연관 서브쿼리
  • 메인 쿼리 테이블의 부분 집합을 리턴
  • 메인 서브쿼리에서 서브쿼리의 컬럼을 사용불가
• 조인
  • 두 테이블의 Cartesian product의 부분 집합을 리턴
  • 조인 한 테이블끼리 컬럼을 자유롭게 사용이 가능

 

TIL : 서브쿼리

5주차 TIL : 서브쿼리

 

DDL 무결성 제약조건

데이터베이스에는 데이터의 무결성을 보장하기 위한 제약조건들이 있다.

사용자가 데이터베이스의 데이터의 무결성을 깨지 않도록 하기 위해서 만들어졌다.

 

무결성 제약조건에는 기본키(PRIMARY KEY) 제약조건인 개체 무결성과 외래 키(FOREIGEN KEY) 제약조건인 참조 무결성이 있다.

 

해당 파트에서 중요한 참조 무결성 옵션에 따른 DELETE/UPDATE 전파도 복습한다.

 

1. PRIMARY KEY 제약조건과 FOREIGN KEY 제약조건이란?
2. 참조 무결성 옵션에 따른 연속 삭제 / 수정

 

PRIMARY KEY 제약조건과 FOREIGN KEY 제약조건이란?

PRIMARY KEY 제약조건

기본키 제약조건은 PK 값은 UNIQUE && NOT NULL

 

• INSERT/UPDATE시
  • PK값으로 기존 PK값을 시도 -> 실행 거부
  • PK값으로 NULL값을 시도 -> 실행 거부

 

FOREIGN KEY 제약조건

외래키 제약조건은 FK값은 PK값을 가지거나 || NULL을 가지거나

 

• 자식 테이블에서 INSERT, UPDATE시
  • 부모 테이블의 PK에 존재하지 않는 값 시도 -> 실행 거부
• 부모 테이블에서 DELETE, UPDATE시 부모 테이블에서 삭제할 PK가 자식 테이블에서 FK로 존재한다면 3가지 동작 중 하나 실행
  • NO ACTION, RESTRICT
    • DELETE, UPDATE시 실행 거부
  • SET NULL, SET DEFAULT
    • DELETE, UPDATE시 자식 테이블의 FK NULL 또는 DEFAULT로 설정
  • CASCADE
    • DELETE시 자식 테이블 연속 삭제(하위 테이블의 관계가 CASCADE이면 하위 테이블까지 모두 연속 삭제)
    • UPDATE시 자식 테이블 연속 삭제(하위 테이블의 관계가 CASCADE여도 자식까지 삭제)

 

참조 무결성 옵션에 따른 연속 삭제 / 수정

• CASCADE
  • ON DELETE CASECADE 
    • 삭제 연산에서 자식, 손자, 증손자 등 자손 테이블의 FK가 모두 CASCADE이면 연속으로 삭제가 전파된다.
    • 파급 효과가 여러 레벨에 영향
  • ON UPDATE CASCADE
    • 파급 효과가 자식 테이블에만 영향
• SET NULL | SET DEFAULT
  • ON DELETE SET NULL | SET DEFAULT
    •  파급 효과가 자식 테이블에만 영향

 

CASCADE일 때 DELETE의 파급 효과는 자식, 손자, 증손자가 CASCADE로 설정되어 있으면 조심해야 한다. 테이블을 설계할 때 어떤 대응을 할지 미리 잘 정해야 한다!

 

 

TIL : DDL

5주차 TIL : DDL

 

5주 차 후기

서브쿼리는 많이 작성해보지 않아서 어려움이 있었다.

조인으로 풀 수 있는 대부분은 서브쿼리로도 풀 수가 있다. 그렇지 않은 문제도 있긴 하다ㅎㅎ

꾸준히 쿼리를 짜 보고 조인을 서브쿼리로 바꿔보는 연습을 해보면 익숙해질 거 같다.

 

연관 서브쿼리는 서브쿼리에서 메인 쿼리의 컬럼을 상수로 사용한다고 생각을 하니 이해가 조금 더 수월했다.

서브쿼리로 해결을 할 때는 노트에 테이블을 간단하게 그려보는 게 좋을 거 같다.

 

DDL 제약 조건 파트에서는 기본키 제약조건, 외래 키 제약조건에 대해서 다시 학습할 수 있어서 좋았다.

ON DELETE | UPDATE CASCADE의 파급효과에 대해서도 확실하게 알 수 있어서 좋았다.

 

추후 프로젝트의 DB 설계를 할 때 DELETE/UPDATE를 소극적, 적극적으로 대응할 지에 대해서도 생각을 해볼 수 있을 거 같다.

 

마지막 6주 차에는  CTE & 통계 쿼리와 윈도우 함수 & 그룹 함수를 학습한다.