🤔 SQL의 꽃 🌼

• 오늘은 SQL에서 JOIN의 유형들, 서브쿼리, CTE 관련 내용들을 배우며 실습을 진행했고, 후반부에는 sakila DB를 통해 데이터 분석 프로젝트를 수행하였습니다.

 

---

👨🏻‍👩🏻‍👧🏻‍👦🏻 JOIN

• SQL에서 JOIN은 테이블 A의 열을 테이블 B에 포함하여 조회하고 싶을 때 사용되는 아주 중요한 요소입니다!
• 데이터를 모델링할 때는 데이터의 중복을 최소화하는 것이 중요하기 때문에 조인을 사용해서 테이블 2개 이상을 조합한 결과를 조회하는 것이 효과적이라고 할 수 있습니다.

 

✨ JOIN의 종류

• 이런 JOIN에 어떤 종류가 있는지 알아보겠습니다.

 

💡 INNER JOIN

SELECT
    [열]
FROM [테이블 1]
    INNER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]
WHERE [검색 조건] (생략 가능)

• INNER JOIN은 두 테이블 모두 조건에 정확히 맞는 열을 조회할 때 사용합니다.
• 쿼리가 실행되는 순서는 JOIN - WHERE - SELECT 순이고,
• 여기서 ON은 JOIN할 때 JOIN 조건을 위해 사용되는 친구, 반면 WHERE 절은 JOIN을 완료한 상태에서 조건에 맞는 데이터를 가져오기 위해 사용되는 아이입니다.
• JOIN 시에 열 이름은 유일해야 하며, 이 유일성을 지키기 위해서 별칭을 사용하여 열 이름을 구분할 수도 있습니다.
• JOIN 조건으로 열이 달라도 상관은 없지만, 비교를 하기 위해서는 데이터 유형이 같아야 합니다.
  • 데이터 유형이 달라도 암시적 형 변환을 할 수 있는 경우라면, 형 변환과 JOIN 모두 문제없이 진행되지만,
  • 데이터베이스 시스템 성능 저하의 요인이 될 수 있기 때문에 주의해야 합니다.

 

💡 OUTER JOIN

SELECT
    [열]
FROM [테이블 1]
    [LEFT | RIGHT | FULL] OUTER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]
WHERE [검색 조건] (생략 가능)

• OUTER JOIN은 간단히 예를 들자면, 상품을 주문한 고객과 상품을 주문하지 않은 고객 데이터를 모두 조회하는 경우 등에 사용되는 JOIN입니다.
• 열의 일치 여부를 고려하지 않고 한 쪽 테이블과 다른 쪽 테이블에 JOIN하는 경우에 사용됩니다.
• LEFT OUTER JOIN을 하면 왼쪽에 놓인 테이블이 우선 조회 결과에 포함이 되고,
  • ON에 걸린 조건으로 열을 비교한 뒤 JOIN 후, 결과에 포함시키고 없는 값은 NULL로 처리하는 방식으로 동작합니다.
• RIGHT OUTER JOIN도 LEFT와 동일한 방식이지만 오른쪽 테이블이 기준이 됩니다.
• FULL OUTER JOIN은 LEFT와 RIGHT OUTER JOIN을 합친 방식으로, 양쪽 테이블에서 일치하지 않는 행도 모두 조회하게 됩니다.
• 이 FULL OUTER는 실제 사용하는 경우가 드물고, 데이터의 누락이나 오류를 찾아낼 때 가끔 사용됩니다.
• 또한, MySQL에서는 FULL OUTER JOIN을 지원하지 않기 때문에 LEFT와 OUTER의 결과를 합치기 위한 UNION을 써서 FULL OUTER JOIN을 구현합니다.

 

💡 CROSS JOIN

SELECT
    [열]
FROM [테이블 1]
    CROSS JOIN [테이블 2]
WHERE [검색 조건] (생략 가능)

• 위 FROM 절에 JOIN 조건(ON)이 없는 이유는 한 테이블에서 모든 행이 다른 테이블의 모든 행을 JOIN하기 때문입니다.
• 거의 사용하지 않는 조인이지만, 데이터가 모자랄 경우에 샘플 데이터를 늘리기 위해 사용되곤 합니다.

 

💡 SELF JOIN

• 동일한 테이블을 사용하는 특수한 조인으로, 자기 자신과 조인한다는 의미를 가집니다.
• 별도의 구문이 없고, INNER JOIN을 활용해서 같은 테이블이지만, 마치 테이블이 두 개인 것처럼 조인해서 사용할 수 있습니다.
• 보통 이건 언제 사용하냐... 예를 들면, 전일 대비 수익이 얼마인지를 알아볼 때 사용한다고 합니다.

 

---

❓ 서브쿼리

• 서브쿼리는 다른 SQL 문 안에 포함된 SQL 쿼리를 의미합니다.
• 즉, 쿼리 내부에서 또 다른 쿼리를 실행하는 방식으로, 특정 데이터를 추출하거나 비교하는 데 사용됩니다.
• 이 서브쿼리는 SELECT, FROM, WHERE, HAVING 절에서 보통 사용되고,
• ORDER BY, GROUP BY 절에서는 사용할 수 없습니다.

 

✨ 서브쿼리의 특징

1. 서브쿼리를 사용하기 위해서는 반드시 소괄호로 묶어주어야 합니다.
2. 서브쿼리는 메인 쿼리가 실행되는 도중에 딱 한 번만 실행됩니다.
3. 비교 연산자와 함께 사용되는 경우에는 반드시 서브쿼리를 연산자 오른쪽에 기술해야 합니다.
4. 서브쿼리 내부에는 정렬 구문인 ORDER BY를 사용할 수 없습니다.

 

✨ 다중 행 연산자

• 서브쿼리의 결과가 여러 개의 행을 반환할 경우, 이런 다중 행을 처리하기 위해 다중 행 연산자가 필요하게 됩니다.
• 다중 행 연산자는 서브쿼리의 결과와 비교하여 특정 조건을 만족하는 데이터를 필터링하는 데 사용합니다.
• 다중 행 연산자에는 크게 다음 네 가지 유형이 있습니다.

연산자	설명
IN	서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건 검색
ALL	서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건 검색
ANY	서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건 검색
EXISTS	서브 쿼리의 결과를 만족하는 값이 존재하는지 여부 확인

 

💡 IN

• 서브쿼리의 결과 중 하나라도 일치하는 값이 존재하는지 확인해주는 연산자로, 결과 안에 특정 값이 존재하면 TRUE를 반환해줍니다.

 

💡 ALL

• 서브쿼리에서 반환된 모든 값과 비교하여 조건을 만족하는 행을 추출해주는 연산자입니다.
• 주어진 조건이 서브쿼리에서 반환된 모든 값(AND)과 일치해야 하고,  주로 >, <, >=, <= 등의 비교 연산자와 함께 사용하곤 합니다.

 

💡 ANY / SOME

• 서브쿼리에서 반환된 어느 하나의 값이라도 만족하는 경우 TRUE를 반환해주는 연산자입니다.
• ALL과 유사하지만, 특정 값보다 크거나(OR) 작은 경우를 확인할 때 사용되고, ALL과 같이 주로 >, <, >=, <= 등의 비교 연산자와 함께 사용됩니다.

 

💡 EXISTS

• 서브쿼리에서 하나 이상의 결과가 존재하는지 여부를 확인해주는 연산자로,
• 서브쿼리가 하나라도 결과 행을 반환하면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환해줍니다.
• IN과 달리 서브쿼리의 반환값을 직접 비교하지 않고, 단순히 존재 여부만 판단해주는 녀석입니다.

 

✨ FROM 절에 서브쿼리 사용하기

• FROM 절에서의 서브쿼리 결과는 테이블처럼 사용되어서 다른 테이블과 다시 JOIN이 가능합니다. 즉, 쿼리를 논리적으로 격리할 수 있게 됩니다.
• FROM 절에 사용하는 서브쿼리는 인라인뷰라고 얘기합니다!

SELECT
    [열]
FROM [테이블] AS a
    INNER JOIN (SELECT [열] FROM [테이블] WHERE [열] = [값]) AS b ON [a.열] = [b.열]
WHERE [열] = [값]

 

✨ SELECT 절에 서브쿼리 사용하기

• SELECT 절에 사용하는 서브쿼리는 반드시 1개의 행(단일 행)을 반환해야 합니다.
• SUM, COUNT, MIN, MAX와 같은 집계 함수와 함께 사용하는 경우가 다수이지만, 이 방식은 성능 면에서 문제가 발생하기 쉽기 때문에, 집계 함수와의 사용은 피하는 것이 좋다고 합니다.
• SELECT 절에 사용하는 서브쿼리는 스칼라 서브쿼리라고 얘기합니다!

SELECT
    [열], (SELECT <집계 함수> [열] FROM [테이블 2]
    WHERE [테이블 2.열] = [테이블 1.열]) AS a
FROM [테이블 1]
WHERE [조건]

 

---

🪑 공통 테이블 표현식(CTE)

• 공통 테이블 표현식(Common Table Expression, CTE)은 실제로 DB에 생성되는 테이블은 아니지만, 쿼리 실행 결과를 테이블처럼 활용하기 위해서 논리적인 테이블을 만들 때 활용하는 표현입니다.
• 주로 DB에 없는 테이블이 필요할 때 사용되며, 바로 다음에 실행할 SELECT 문에만 이런 CTE를 사용해서 데이터를 조회할 수 있는 일회성을 가지고 있습니다.
• CTE에는 일반 CTE와 재귀 CTE가 있는데 하나씩 살펴보겠습니다.

 

✨ 일반 CTE

WITH [테이블] (열 1, 열 2, …)
AS
( <SELECT 문> )
SELECT [열] FROM [테이블];

• 복잡한 쿼리를 단순하게 만들 때 이 일반 CTE를 사용합니다.
• 만약 CTE에서 정의한 열과 CTE 내부 SELECT 절의 열 목록이 다르면 오류가 발생!
• CTE 내부에 UNION ALL, INTERSECT, EXCEPT 키워드를 사용하면 합집합이나 교집합 등을 구현할 수도 있습니다.
  • 특히 여기서 EXCEPT를 사용하면 NOT IN과 비슷한 결과를 얻게 되고, 이는 결과값에서 중복을 제거한 유일한 행을 반환하게 됩니다.
  • 즉, CTE에서 먼저 작성된 SELECT 문을 기준으로 그 다음 작성한 SELECT 문과 중복되지 않는 데이터를 반환해줍니다.

 

✨ 재귀 CTE

WITH RECURSIVE [CTE_테이블] (열 1, 열 2, …)
AS (
    <SELECT * FROM 테이블 A> → 앵커 멤버
    UNION ALL
    <SELECT * FROM 테이블 B JOIN CTE_테이블> → 재귀 멤버
)
SELECT * FROM [CTE_테이블];

• 재귀 CTE는 만들어진 CTE의 결과를 CTE 내부 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 가집니다.
• 주로 조직도와 같은 계층 데이터를 검색할 때 많이 사용됩니다!
• 재귀 CTE는 2개 이상의 SELECT 문을 사용하고, 위 코드에서 보이는 앵커 멤버와 재귀 멤버를 포함한다는 특징이 있습니다.
  • 여기서 앵커 멤버는 정의한 CTE를 참조하지 않습니다.

 

---

🤔 22일차 회고

• 오늘은 SQL, 그 중에서도 SQL의 🌹인 JOIN과 서브쿼리에 대해 배우게 되었습니다.
• JOIN과 서브쿼리를 배워가면서 못 풀어갔던 프로그래머스 문제들이 술술 풀리는 것을 보고, SQL을 더 연습해야겠다 싶었습니다! 언젠가 PCSQL 고득점을 딸 그 날까지...!
• 오늘 진도가 모두 마무리되고 나서는 사용했던 DB인 sakila DB를 가지고 여러 테이블들을 조인하여 데이터 분석을 진행하였습니다.
• 오늘도 팀을 이루어 시각화까지 진행하게 되었는데, 이 과정들에 있어서 기획과 데이터 전처리가 정말 중요하구나를 많이 느끼는 것 같습니다. 주제를 세울 때에도, 시각화를 할 때에도 일관성 있게, 또 신박하게 해보고 싶습니다.
• 내일은 이어서 SQL 관련 주요 함수들을 다룰 예정입니다!