12/4 SQL week 1 학습 정리

1. Sub Query

// 서브 쿼리 예시
// 해당 쿼리는 B 테이블에서 age가 20보다 큰 a_id들을 통해 A 테이블의 데이터를 검색한다
SELECT * FROM `A` WHERE `id` in (SELECT `a_id` FROM `B` WHERE `age`>'20')

• 서브 쿼리란

  서브 쿼리란 다른 쿼리 내부에 포함된 SELECT 문을 의미한다(엄밀히는 SELECT문 만 가능하다). 서브 쿼리는 여러 테이블을 함께 참조하여 DB에 질의를 해야할 때 많이 쓰이는 방법이다. 쿼리문에서 서브 쿼리를 ‘내부 쿼리’라고 부르며, 서브 쿼리가 담겨 있는 쿼리를 ‘외부 쿼리’라고 부른다. 서브 쿼리는 괄호(())를 이용해서 구분한다.

• 서브 쿼리의 작동 순서

  서브 쿼리는 우선 외부 쿼리가 작동하기 전에 먼저 작동하게 되며, 그 후에 외부 쿼리가 실행된다. 서브 쿼리가 먼저 작동하기는 하지만 서브 쿼리 내부에서 외부 쿼리의 변수나 칼럼을 사용하는 것도 가능하다. 다만 외부 쿼리에서 내부 쿼리의 칼럼을 참조하는 것은 불가능하다.

• 서브 쿼리의 장단

  서브 쿼리를 이용하면, 무엇보다 구문의 가독성이 증가한다. 괄호를 통해 구문을 구조화 하기 때문에 일반적으로 사용하는 JOIN보다 읽기 편하다.
  다만, 서브 쿼리는 일반적인 경우에서 JOIN보다 성능 상 뒤쳐진다. 그 이유는 서브 쿼리의 결과를 하나의 변수에 저장하고 이를 계속 재활용하는 것이 아닌, 매 행마다 서브 쿼리를 다시 실행하기 때문이다. 이로 인해, 최신 MySql에서는 서브 쿼리를 자동으로 JOIN 문으로 변환하여 실행한다.

• 그 외

  서브 쿼리는 예시처럼 사용하는 것이 일반적이지만, WHERE 절 말고 칼럼 대용으로 쓰이기도 하고(스칼라 서브 쿼리), FROM 절의 테이블 대용으로 쓰이기도 한다.(인라인 뷰) 인라인 뷰로 사용할 때는, 꼭 AS 를 통해 alias를 설정해주어야한다.

2. JOIN

• 조인이란

  조인이란 2개 이상의 테이블을 옵션에 따라 하나의 테이블 혹은 결과물로 합치는 것을 의미한다. 조인은 옵션에 따라 다양한 방식으로 이루어지며, 그 종류에는 inner join, left outer join, right outer join 등이 있다.

• INNER JOIN

  이너 조인이란 특정 테이블에 어떤 테이블을 조인시킬 경우 양 테이블의 데이터 중 조건에 부합하는 데이터만을 남기는 방식이다. 일종의 교집합으로 보면 되고, 해당 방식은 두 테이블 중 어떤 테이블을 기준으로 잡더라도 결과물이 동일하다는 특징이 있다.

• LEFT (OUTER) JOIN

  엄밀히 말하면 left outer join이지만 보통은 left join으로 이야기한다. 해당 방식은 JOIN문을 기준으로 왼쪽의 테이블을 기준으로 삼고, 기준 테이블의 데이터는 모두 남기고 조인할 테이블의 데이터를 이에 갖다 붙이는 방식이다. 기준 테이블의 데이터는 모두 남겼기 때문에 조인이 되지 않는 행이 발생하며, 이런 행에는 데이터 구조를 유지하기 위한 더미 데이터(null)가 붙게 된다. 이를 이용하면 차집합을 구현할 수 있는데, 바로 더미 데이터가 붙은 행만을 추출하는 방법이다.

• RIGHT (OUTER) JOIN

  이는 위에서 기술한 LEFT JOIN에서 기준 테이블만 바뀐 형태이므로 따로 설명은 하지 않겠다.

• FULL OUTER JOIN

  해당 방식은 대부분의 DBMS에서 직접적으로 지원하지는 않지만 간접적으로 이를 구현할 방법은 존재한다.

Thx CM LEE.

3. 함수 정리

1) 기본적인 JOIN 문법

SELECT TABLE_A.COLUMN_NAME
FROM TABLE_A
LEFT JOIN TABLE_B ON TABLE_A.KEY = TABLE_B.KEY

// TABLE_A에 TABLE_B를 조인하는 것을 의미 (KEY값을 기준으로)
// (추가) TABLE_B 의 KEY가 TABLE_A의 외래키인 경우

2) %와 _ 의 차이

ex) WHERE TABLEA.COLUMN_NAME LIKE'fruit%' 인 경우 : 한 컬럼에서 fruit로 시작하는 문자 모두 추출
WHERE TABLE_B.COLUMN_NAME LIKE 'fruit'인 경우 : 한 컬럼에서 fruitA와 같이 뒤에 문자가 하나만 나오는 경우

3) 정렬 기준

ORDER BY COLUMN_NAME1 DESC, COLUMN_NAME2
정렬하고자 하는 컬럼 순서대로 작성할 것. 내림차순인 경우 컬럼명 뒤에 DESC 작성

4) 날짜 형태 변경 하기

ex) 2022-12-07 00:00:00 >> 2022-12-07로 변경하는 방법

4-1) LEFT함수 사용
LEFT(COLUMN_NAME, 10)

4-2) DATE_FORMAT 함수 사용
DATE_FORMAT(COLUMN_NAME, '%Y-%m-%d') as COLUMN_NAME

5) 특정 컬럼 결측치인 경우 NULL처리 방법 - IFNULL 함수 사용
SELECT 문에서 IFNULL(COLUMN_NAME, NULL) AS COLUMN_NAME