그룹화하기

엑셀의 피벗 테이블처럼 데이터를 그룹화하고, 필터링 하는 기능은 GROUP BY와 HAVING을 통해 구현할 수 있다. 아래는 기본 형식이다.

SELECT [열] FROM [테이블]
WHERE [열] = [조건값]
GROUP BY [열]
HAVING [열] = [조건값]

여기서 GROUP BY로 열은 여러개를 지정할 수 있다. 다음 쿼리는 열1을 먼저 그룹화하고, 그다음 열2를 그룹화한다.

SELECT [열1], [열2] FROM [테이블]
GROUP BY [열1], [열2]

그룹화 열 데이터 개수 확인하기 (COUNT)

피벗 테이블처럼 그룹화는 집계 함수와 함께 사용하는데, 그룹별로 데이터 개수를 세는 방법은 COUNT 함수를 활용하면 된다. 다음 쿼리는 이를 구현한 것이며, AS cnt는 개수를 센 결과를 출력할 때 열 이름을 cnt로 한다는 뜻이다. 다만, cnt는 실제 데이터가 아니라 출력에만 활용되므로 GROUP BY나 HAVING에서 찾는 열에서는 활용할 수 없다. 최종적으로 출력할 때 개수에 따라 정렬까지 수행하였다.

SELECT [열], COUNT(*) AS cnt FROM [테이블]
GROUP BY [열]
ORDER BY COUNT(*)

데이터 필터링 하기 (HAVING)

그룹화 데이터 필터링을 위해서는 HAVING을 사용한다. WHERE와 차이점은 WHERE 문은 테이블에 있는 열에 적용하는 것이고, HAVING은 SELECT나 GROUP BY에 있는 열에만 적용 가능하다. 아래 예시는 그룹화 열 데이터 개수가 100 이상인 것만 조회하는 쿼리이다.

SELECT [열], COUNT(*) AS cnt FROM [테이블]
GROUP BY [열]
HAVING COUNT(*) >= 100

중복 데이터 제거하기 (DISTINCT)

GROUP BY는 그룹화를 통해 중복을 제거할 수 있지만, 이외에 SELECT DISTINCT를 통해 중복 데이터를 제거할 수 있다. 아래 기본 형식은 해당 열의 중복 데이터를 제거하른 쿼리이다. 다만, 집계하는데 활용하기에는 제한이 있다.

SELECT DISTINCT [열] FROM [테이블]

와일드카드로 문자열 검색하기

정확한 조건이 아닌 일부만으로 검색하기 위해서는 아래와 같이 LIKE를 사용해 와일드 카드로 지정한 패턴과 일치하는 문자열, 시간 등을 검색할 수 있다.

SELECT [열] FROM [테이블]
WHERE [열] LIKE [조건]

특정 문자열 포함(%) 검색

특정 문자열을 포함하는 것을 찾을 때는 %를 사용한다. %의 위치에 따라 특정 문자열이 포함된 형태를 검색할 수 있다. 다음은 %의 사용방법에 대한 간단한 요약이다.

아래는 간단한 사례이다. 만약 특정 문자열을 제외한 데이터를 검색하려고 한다면, LIKE 대신 NOT LIKE를 사용하면 된다.

SELECT * FROM [테이블]
WHERE [열] LIKE 'A%'

만약 문자열에 %가 포함된 데이터를 검색하려면, ESCAPE를 활용하면 된다. ESCAPE는 뒤에 있는 문자를 제거하고 %를 포함한 어떤 문자가 앞뒤로 나와도 상관없도록 LIKE 실행을 돕는다.

SELECT * FROM [테이블]
WHERE [열] LIKE '%#%%' ESCAPE '#'

특정 길이의 문자열 검색

문자열의 길이를 특정해 검색하고 싶다면, 언더바( _ )를 사용하면 된다. 데이터의 양이 많다면 %로 찾을 때는 시간이 다소 소요될 수 있는데, 원하는 문자열에 대한 길이의 추가정보가 있다면 이를 활용하면 더 빠른 검색이 가능하다고 한다. 사용하는 패턴은 % 와 거의 유사하다. 다음 예시는 4글자로 A로 시작하고 C로 끝나는 문자열을 찾는 쿼리이다.

SELECT * FROM [테이블]
WHERE [열] LIKE 'A__C'

앞에서 %와 조합해 좀 더 풍부한 검색식을 만들 수도 있다. 다음은 A로 시작하고 C로 끝나면서 4글자 접두사를 포함하는 (길이와 상관없이) 모든 문자열을 찾는 쿼리이다.

SELECT * FROM [테이블]
WHERE [열] LIKE 'A__C%'

문자 범위를 지정해 검색

모든 문자열이 아니라 검색해야 하는 문자의 범위를 지정하는 것은 대괄호([])를 활용하면 된다. 다음은 사용패턴이다.

SELECT 기본 구문

SELECT문은 데이터베이스에서 데이터를 검색하는 구문으로 직관적으로 사용할 수 있지만, 시스템 성능에 영향을 줄 수 있기 때문에 주의해서 사용해야 한다. 실전에서 사용하는 구문들은 훨씬 복잡하지만, 실제 큰 틀에서는 아래에서 크게 벗어나진 않는다.

SELECT [열]
FROM [테이블]
WHERE [조건]
ORDER BY [열]

WHERE으로 데이터 검색하기

원하는 조건에 맞는 행을 검색하는 기본적인 WHERE문의 형식은 다음과 같다. 조건문에서는 조건 연산자와 논리 연사자 등을 적절히 조합해 원하는 조건문을 만들어 준다.

SELECT [열] 
FROM [테이블] 
WHERE [열] [조건 연산자] [조건값]

• 조건 연산자

• 논리 연산자

케이스 스터디

특정 범위에 해당하는 값 검색

특정 범위에 해당하는 값을 검색하기 위해 BETWEEN이나 AND를 사용할 수 있습니다.

-- BETWEEN 사용
SELECT * FROM nasdaq_company 
WHERE ipo_year BETWEEN 2010 AND 2011

-- AND 사용
SELECT * FROM nasdaq_company
WHERE ipo_year >= 2010
  AND ipo_year <= 2011

AND, OR 조건이 혼합된 조건 검색

예를 들어, 아래 두가지 조건(각 bullet은 AND 조건)을 만족하는 행을 검색한다고 가정하자.

• sector가 'Technology' 또는 'Consumer Services'
• symbol이 'MSFT' 또는 'AMD' 또는 'AMZN'

이 경우 아래와 같이 구현이 가능하다. 만약 IN을 사용하지 않고, OR를 연결하려면 괄호를 통해 우선순위를 지정해줄 필요가 있다.

SELECT * FROM nasdaq_company
WHERE sector IN ('Technology', 'Consumer Services')
  AND symbol IN ('MSFT', 'AMD', 'AMZN')

NULL 데이터 검색

데이터가 비어있는 상태(Null)의 경우 분석에 반영 여부가 중요할 수 있다. NULL을 필터링 하기 위해서는 아래 코드처럼 입력해주면 된다. 만약 NULL이 아닌 데이터를 검색하려거든 IS NOT NULL로만 변경하면 된다.

SELECT * FROM nasdaq_company
WHERE sector IS NULL

ORDER BY로 데이터 정렬

데이터베이스 출력 결과를 오름차순(ASC)과 내림차순(DESC)으로 정렬할 수 있다. 열 기준에 따라 정렬할 수 있는데, 여러 개의 열을 동시에 쓸 수 있으며 쓰여진 순서에 따라 서로 다르게 정렬이 이뤄진다.

SELECT [열] 
FROM [테이블] 
WHERE [열] [조건 연산자] [조건값]
ORDER BY [열] [ASC, DESC]

케이스 스터디

오름차순과 내림차순을 조합해 정렬하기

각 열에 오름차순과 내림차순을 조합해 정렬할 수 있다. 다음은 sector 열에서는 오름차순으로 정렬하고 symbol 열에서는 내림차순으로 정렬하며, 정렬 우선순위는 sector와 symbol 순인 경우이다.

SELECT * FROM nasdaq_company
WHERE sector IS NOT NULL AND sector <> ''
ORDER BY sector ASC, symbol DESC

TOP N 데이터 검색

(SQL 서버)특정 조건에 해당하는 데이터 중 상위 N개의 데이터만 보고 싶다면, SELECT TOP으로 검색하려는 행의 수를 추가해 사용한다. 다음은 sector는 오름차순, symbol은 내림차순으로 정렬해 상위 10개 데이터를 검색하는 쿼리이다.

SELECT TOP 10 * FROM nasdaq_company
ORDER BY sector ASC, symbol DESC

(MySQL) 만약 MySQL을 사용한다면, 위에 구문은 지원하지 않기 때문에 LIMIT를 대신 사용한다. 

SELECT * FROM nasdaq_company
ORDER BY sector ASC, symbol DESC
LIMIT 10

특정 구간 데이터 검색

Top N 데이터와 다르게 특정 구간의 데이터를 검색하고 싶다면, OFFSET ... FETCH NEXT를 활용한다. OFFSET은 검색 결과에서 지정한 행 개수만큼 건너뛰고 출력한다. (즉, ...에 입력한 행은 건너뛰고 +1부터 조회한다) 여기서 주의할 점은 정렬한 열을 기준으로 하기 때문에 ORDER BY가 필수적이라는 점이다.

FETCH NEXT는 OFFSET 이후 얼마만큼 출력할지 행의 개수를 지정한다. 이 때문에 OFFSET을 반드시 먼저 수행해줘야 한다. 다음 구문은 1,001번째 데이터부터 10개의 데이터를 출력하는 쿼리이다.

SELECT * FROM nasdaq_company
ORDER BY symbol DESC
OFFSET 1000 ROWS
FETCH NEXT 10 ROWS ONLY

엑셀에서는 피벗테이블이라는 기능을 통해 데이터에 대한 요약을 아주 쉽게 할 수 있다. 행과 열, 필터 설정을 통해 데이터의 표시 형식과 데이터 자체도 합계, 개수, 비율 등 꽤나 다양하게 볼 수 있기 때문에 자주 사용하는 기능이기도 하다. 그렇다면 데이터를 다룰 수 있는 다른 언어들은 어떨까?

내가 다루고자 하는 SQL에서는 GROUP BY를 통해 이를 어느정도 해결해 줄 수 있다. (물론, 엑셀만큼 직관적이지는 않지만) 이와 유사한게 데이터를 다룰 수 있는 다른 언어인 Python에서 Pandas에서 제공하는 groupby란 것을 통해 우리가 익숙한 엑셀의 피벗테이블과 유사한 형태를 만들어 줄 수 있다.  

쓰임새는 매우 간단하다. SQL에서 항상 하는 것처럼 SELECT를 통해 우리가 구하고 싶은 열을 고르고, 그에 대해서 GROUP BY (열1), (열2) ... 등등으로 묶어주고 싶은 열들에 대해 나열해주면 끝이다. 다음 예제를 살펴보자. 전체 매출(TotalSales 테이블)에서 국가별, 지역별 매출액의 합계를 보고자한다면, 아래와 같이 작성하면 된다.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

이외에도 WHERE, HAVING을 통해 조건에 맞지 않는 것들을 필터할 수 있고, ORDER BY로 순서를 결정할 수 있다. 예를 들면 이런 식이다. 연도별 합계를 정리하지만, 2003년 이후의 것들만 찾는 것이 목표이기 때문에 HAVING을 통해 필터링을 진행한 것이다.

SELECT DATEPART(yyyy,OrderDate) AS 'Year'  
    ,SUM(TotalDue) AS'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
HAVING DATEPART(yyyy,OrderDate) >= '2003'  
ORDER BY DATEPART(yyyy,OrderDate);

이외에도 필요한 내용들은 아래 URL에 매우 잘 정리되어 있으니 참고해볼만하다.

docs.microsoft.com/ko-kr/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

데이터를 이용하기 위해서는 기본적으로 데이터에 대한 전처리가 필수적이다. 데이터 이용의 대표격인 머신러닝 등이 있을텐데, 데이터 전처리(Data Preprocessing)는 앞서 설명한 ML 알고리즘만큼 중요하다는 것이다. 왜냐하면 데이터 이용 알고리즘은 데이터에 기반한 경우가 많은데, 어떤 데이터 입력으로 가지느냐에 따라 결과가 크게 달라질 수 있기 때문이다. 흔히들 얘기하는 Garbage In, Garbage Out이 되지 않기 위해서라도 데이터의 전처리에 대해 중요성은 더 말할 필요는 없을 것이다.

전처리에서 우리가 처리해야 할 형태는 매우 다양하지만, 여기서 살펴볼 내용은 빈칸이 발생한 경우에 대해 어떻게 처리할 것인가에 대한 것을 살펴보고자 한다. 빈칸을 그대로 두게 되면, 평균이나 분산 등 여러 통계치와 다른 알고리즘 적용에 그다지 좋지 않은 결과를 낼 수 있다. (물론, 빈칸이 전체 데이터 개수 대비 매우 적다면 무시하는 방법도 고려할 수 있겠지만)

예시를 들어가기에 앞서, COALESCE라는 함수에 대해 알아보고자 한다. 한국에서만 영어를 배웠던 나에게는 그리 친숙하지 않은 단어인데, 영어 단어상으로 '더 큰 덩어리로 합치다, 연합하다' 이런 뜻을 가진 단어라고 한다. 

(그래서 나는 여러 가지 NULL에 대해

사용방법: COALESCE(p1, p2 , .... pN) 

결론적으로 이야기하자면, Null이 나올 때 대입할 값만 pN으로 지정해주고, 나머지 값들은 그대로 간다는 뜻이다. 익숙한 CASE WHEN 구문하고 유사하다고 할 수 있는데, 이걸 CASE WHEN으로 바꿔보면 다음과 같다.

CASE
WHEN (p1 IS NOT NULL) THEN p1
WHEN (p2 IS NOT NULL) THEN p2
...
ELSE pN
END

우리에게 익숙한 CASE WHEN으로 처리하는 것이 평소엔 편하겠지만, 코드가 조금만 길어지거나 복잡해진다면 COALLESCE 함수를 이용해 단순화하는 것도 좋은 방법이 될 것이다.

그렇다면 간단한 예시를 살펴보고, 코드를 통해 학습한 다음 나중에 적용해보자. 상황은 다음과 같다. 

2004년에 덴마크(DEN), 노르웨이(NOR), 스웨덴(SWE) 3국이 금메달을 딴 개수를 Country와 Gender 필드에 대해 정렬해서 나타내고자 한다. 거기서 빈칸에 대해서는 Country에 대해서는 'All countries'로, Gender에 대해서는 'All genders'로 처리하도록 한다.

SELECT
  -- Replace the nulls in the columns with meaningful text
  COALESCE(Country, 'All countries') AS Country,
  COALESCE(Gender, 'All genders') AS Gender,
  COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2004
  AND Medal = 'Gold'
  AND Country IN ('DEN', 'NOR', 'SWE')
GROUP BY ROLLUP(Country, Gender)
ORDER BY Country ASC, Gender ASC;

위 코드에서 볼 수 있다시피, COALSESCE를 이용해 공백에 대해 처리가 가능함을 확인할 수 있다. Country 필드는 값 그대로 가되, 맨 마지막에 위치한(사실은 2번째지만) 'All countries'를 공백에 채워넣는 것으로 이해할 수 있다. 동일하게, Gender 필드도 처리되는 것이라 생각할 수 있을 것이다.

이 글의 주된 내용은 아니지만, GROUP BY ROLLUP()의 경우 안에 들어가 있는 필드에 대해 그룹으로 묶어주는데, ROLLUP을 통해 부분합을 제공할 수 있다.

마이크로소프트에서 제공하는 참조 문서이니 COALESCE 함수와 ROLLUP에 대해 적극적으로 사용하고 싶은 분들은 아래 링크를 통해 참고하도록 하자.

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/ko-kr/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

주식 등 자산에 대해 차트매매를 하시는 분들이 있다면, 한번쯤 들어본 적이 있는 '이동평균'이라는 것이 있다. 이동평균이란 Moving Average(MA)로 여러 계산 방법이 있지만, 여기서는 단순 이동평균이라는 방법을 사용할 예정이다. 계산은 이전에 있던 데이터에 단순평균을 계산하는 쉬운 방법이다.

여기서 상황은 러시아에서 1980년을 포함한 이후에 금메달을 딴 개수의 이동평균(이전 2개 대회 + 현 대회)을 계산해야 하는 것이다. 아래 코드를 살펴보면, WITH 절에 각 조건(러시아, 금메달, 연도)들을 적용한 테이블을 만들었다. 그리고 이 테이블을 참조해 연도가 증가하는 방향으로 배열(ORDER BY Year ASC)하여 다음 작업을 수월하게 만들었다.

이전 2개 대회를 참조하는 2 PRECEDING과 현재 행(CURRENT ROW)을 모두 참조하기 위해 ROWS BETWEEN까지 해서 평균을 구해주면 우리가 원하는 단순 이동평균을 구할 수 있다. 

WITH Russian_Medals AS (
  SELECT
    Year, COUNT(*) AS Medals
  FROM Summer_Medals
  WHERE
    Country = 'RUS'
    AND Medal = 'Gold'
    AND Year >= 1980
  GROUP BY Year)

SELECT
  Year, Medals,
  --- Calculate the 3-year moving average of medals earned
  AVG(Medals) OVER
    (ORDER BY Year ASC
     ROWS BETWEEN
     2 PRECEDING AND CURRENT ROW) AS Medals_MA
FROM Russian_Medals
ORDER BY Year ASC;

DB를 볼 때, 어떤 정해진 방식에 따라 랭킹을 매겨야 할 순간이 있다. SQL에서는 그걸 할 수 있는 여러 방법이 존재하는데, 그 중에 대표적인 방법인 ROW_NUMBER()를 이용하는 방식을 알아보고자 한다.

ROW_NUMBER()는 기본 적으로 제공되는 함수로서 뒤에 OVER( )를 사용해 정렬해주는 방식에 따라 순번을 매길 수 있다.

한 가지 예시를 생각해보자. 올림픽에서 매달을 딴 선수(Athlete)들의 메달 수(COUNT(*))에 따라 순서를 매기고자 한다. 아래 코드를 살펴보자. 여기선 WITH AS 절로 묶어서 해줬는데, WITH절에서 Athlete와 그 메달 수를 Count한 Medals란 데이터를 생성한 새로운 테이블을 만들어주는 역할을 한다.

여기서 만들어진 새로운 테이블(Athlete_Medals)에서 개수를 내림차순으로 정렬하면, 맨 위에 가장 개수가 많은 선수를 찾을 수 있다. 그리고 여기에 ROW_NUMBER()를 부여하면 순위를 부여할 수 있다.

WITH Athlete_Medals AS (
  SELECT
    -- Count the number of medals each athlete has earned
    Athlete,
    COUNT(*) AS Medals
  FROM Summer_Medals
  GROUP BY Athlete)

SELECT
  -- Number each athlete by how many medals they've earned
  Athlete,
  ROW_NUMBER() OVER (ORDER BY Medals DESC) AS Row_N
FROM Athlete_Medals
ORDER BY Medals DESC;

SQL을 공부하면서 내가 그동안 해왔던 방법이 아닌 새로운 방법을 알게 될 때가 있다. 이번에 정리하려는 백분율을 계산하는 방법이 그 중 하나인데, 내가 그동안 일반적으로 해왔던 방법은 백분율을 구하고 싶은 수치를 전체 합계를 구한 거로 나눠서 구하는 방법이다.

여기서는 조금 다른 것이 AVERAGE를 이용해 백분율을 구한다. 사실 위에 내가 구한거와 거의 동일하지만, 때로는 이렇게 구하는 것이 훨씬 유용할 때도 있을 것 같다. 방법론은 다음과 같다. 우리가 원하는 값에는 1을 부여하고, 원하지 않는 것에는 0을 부여해 그에 대한 평균을 구하는 것이다.

예를 들어 생각해보자. 축구 리그를 진행하면서 무승부에 대한 비율을 찾고자 한다. 모두가 알다시피, 축구에서 무승부가 되려면 home팀과 away팀의 득점이 똑같으면 된다. 원래 하던데로 하려면, 득점이 동일한 경우를 찾아서 COUNT를 해주고, 전체 경기수를 구해서 나눠주는 과정을 통해 구할 수 있다.

하지만, AVERAGE를 이용하게 되면, 득점이 동일한 경우에는 1을 부여하고 그렇지 않은 경우엔 0을 부여해 평균을 구해주면 끝난다. 이를 2019/2020 챔피언스리그 A조에 있었던 경기를 가지고 확인해보자.

위 표에서 보면, 무승부를 기록한 경기에는 1을 표시하고 승부가 갈린 경우는 0으로 표시했다. 따라서 여기에 있는 1을 전부 더한 값 4가 무승부를 기록한 경기를 의미하는 것이고 이를 전체 개수로 나눠주는 AVERAGE를 적용하면 전체 경기 중 무승부 비율을 구할 수 있게 되는 것이다.

밑의 사진은 실제로 구글링해서 나온 챔피언스 리그 결과인데, 무승부를 기록한 경기수를 모두 더하면 8이 나오고, 이 기록이 맞붙은 두 팀이 모두 기록하게 되는 것이므로 2를 나눠보면, 우리가 구했던 4와 동일함을 알 수 있다.

아래에 제시된 코드는 위에서 전체적으로 설명한 것의 SQL 예시 코드이다.

SELECT c.name AS country, ROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1 WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0 END),2) AS pct_ties_2013_2014, ROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1 WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0 END),2) AS pct_ties_2014_2015 FROM country AS c LEFT JOIN matches AS m ON c.id = m.country_id GROUP BY country;

SQL에서 JOIN이라는 중요한 하나의 툴을 제공한다. JOIN은 서로 다른(혹은 자기 자신도 포함하는) 테이블을 가지고 합치는 등의 연산(?)을 수행하는 하나의 방법이다. 실제로 데이터를 다루다보면 합쳐야 하는 경우가 생기기 마련이다.

SQL에서는 JOIN에 대한 여러가지 방법을 제공하는데, 여기선 INNER JOIN, LEFT(RIGHT) JOIN, FULL JOIN 세 가지를 다뤄보고자 한다. LEFT와 RIGHT는 표면상으로 다르긴 하지만, 작동방식은 반대로만 생각하면 되기 때문에 사실은 유사한 JOIN이라고 할 수 있다. 이는 후에 설명하도록 하겠다.

SQL 스크립트를 들어가기 전에, 간단히 상황 설명을 하자면 아래와 같다.

•  두개의 테이블이 존재: countries, currencies
• 지역이 'North America'이거나 없는(NULL) 경우에 대해서만 찾아보기
• 두 테이블 사이 연관되는 것은 나라 코드(code)
• 나라이름(name), 나라코드(code), 지역(region), 화폐단위(basic_unit)을 조회

이것을 명심한 상태로 각각의 JOIN에 대해 살펴보자. 첫번째로, FULL JOIN이다. 이름에서 알 수 있다시피 FULL JOIN은 모든 것을 포함한다는 뜻이다. 이를 집합을 이용해 설명하자면, 합집합의 형태이다.

하나의 키로 기준을 보면, countries 테이블에만 있는 것도 있고 currencies 테이블에만 있는 것도 있기 마련이다. 앞에서 말했던 내용 JOIN들은 한쪽에만 있거나 겹치는 경우만 출력한다. 하지만, FULL JOIN에서는 두 테이블에 있는 내용들을 전부 출력하는데 사용할 수 있다. 따라서, 서로의 테이블에 없는 경우가 출력되는 경우도 있는데 그럴 경우에는 NULL값이 나온다.

SELECT name AS country, code, region, basic_unit
-- 3. From countries
FROM countries
  -- 4. Join to currencies
  FULL JOIN currencies
    -- 5. Match on code
    USING (code)
-- 1. Where region is North America or null
WHERE region = 'North America' OR region IS NULL
-- 2. Order by region
ORDER BY region;

한 가지 덧붙이자면, 조건에 대해 사용하는 코드가 ON table A.key = table B.key 를 사용하는 게 일반적이다. 하지만, 교집합을 찾아줄 수 있는 key가 같다면, USING을 이용해서 훨씬 코드의 길이를 줄일 수 있다.

다음은 LEFT JOIN이다. 이는 원래 SELECT문의 테이블을 왼쪽으로 두고, 오른쪽에 LEFT JOIN 다음에 오는 테이블을 두는 형태이다. (RIGHT JOIN은 그 반대) 이런 LEFT JOIN은 집합의 관점에서 봤을 때, 차집합이다. 즉, 위 FULL JOIN에서 countries에 null 값이 나오는 것을 제외하는 개념이다.

SELECT name AS country, code, region, basic_unit
-- 1. From countries
FROM countries
  -- 2. Join to currencies
  LEFT JOIN currencies
    -- 3. Match on code
    USING (code)
-- 4. Where region is North America or null
WHERE region = 'North America' OR region IS NULL
-- 5. Order by region
ORDER BY region;​

마지막으로는 INNER JOIN인데, 이를 집합의 관점에서 보자면 교집합과 같다. 즉, 두개의 테이블이 code란 공통점을 가지고 겹치는 부분만을 조회하게 해준다는 것이다. 위 LEFT JOIN에서 countries에서 가져온 필드 값들이 null이 나온 경우를 제외한 거라면, INNER JOIN에서는 여기다가 currencies에서 가져온 필드가 null이 나온 경우까지 제외한다.

그러다보니, 조회되는 데이터의 수는 가장 적을 수 밖에 없다. (데이터의 개수: FULL >= LEFT(RIGHT) >= INNER)

SELECT name AS country, code, region, basic_unit
FROM countries
  -- 1. Join to currencies
  INNER JOIN currencies
    USING (code)
-- 2. Where region is North America or null
WHERE region = 'North America' OR region IS NULL
-- 3. Order by region
ORDER BY region;

지금까지 얘기한 것을 그림으로 종합하면, 아래처럼 종합할 수 있다. 내가 만든 그림은 아니지만, JOIN에 대해서 꽤 잘 설명되어 있다고 생각해서 가져왔으므로 쓰고자 하는 형태에 따라서 맞춰서 사용하면 된다.

이미지 참고: https://postitforhooney.tistory.com/entry/DBMARIADB-SQL-%EC%98%88%EC%A0%9C%EB%A5%BC-%ED%86%B5%ED%95%9C-JOIN%EC%9D%98-%EC%A2%85%EB%A5%98-%ED%8C%8C%EC%95%85