데이터분석/SQL

[SQL] MySQL - 데이터 만지기

안나 2023. 3. 17. 10:09

규칙 만들기

SQL에서도 조건을 만들 수 있습니다.

IF 함수를 사용해서 조건을 만들면 되는데요, 기본 구조는 다음과 같습니다.

IF(조건식, 참일 때 값, 거짓일 때 값)

IF 절은 주로 SELECT 절에서 사용하고, 결과 값을 새로운 컬럼으로 반환해 줍니다.

 

예를 들어, mypokemon 테이블에서 이름과

공격력이 50 이상이면 strong, 아니면 weak로 표기한 attack_class를 가져오고 싶다면 다음과 같이 작성합니다.

SELECT name, IF(attack >= 50, 'strong', 'weak') AS attack_class
FROM mypokemon;

이렇게 하면 attack >= 50이 참이 될 경우 strong, 아니면 weak로 표기되죠.

 

데이터가 NULL인지를 확인하는 작업이 매우 중요하겠죠?

NULL값을 다른 값으로 채우기 위해 IFNULL 함수를 사용할 수 있습니다.

IFNULL([컬럼 이름], NULL일 때 값)

해당 컬럼 값이 NULL인 로우에서 설정한 NULL일 때 값을 반환합니다.

예를 들어, name 컬럼과 name 컬럼에 NULL 값이 있으면 unknown이라고 적은 결과를

full_name으로 가져오고 싶다면 다음과 같이 작성합니다.

SELECT name, IFNULL(name, 'unknown') AS full_name
FROM mypokemon;

 

CASE

CASE 함수는 데이터를 구분할 때 사용할 수 있습니다.

두 가지 형식으로 나타낼 수 있는데요.

첫 번째는 조건식에 컬럼 이름을 포함하는 방식입니다.

CASE
    WHEN 조건식1 THEN 결과값1
    WHEN 조건식2 THEN 결과값2
    ELSE 결과값3
END

두 번째는 컬럼 이름을 먼저 빼주는 방식입니다.

CASE [컬럼 이름]
    WHEN 조건값1 THEN 결과값1
    WHEN 조건값2 THEN 결과값2
    ELSE 결과값3
END

ELSE는 생략할 수 있는데, 생략할 경우 NULL 값을 반환하게 됩니다.

 

예를 들어볼까요?

이름과 공격력을 attack_class로 구분해서 가져올 건데

공격력이 100 이상이면 very strong, 60 이상 100 미만이면 strong, 아니면 weak로 구분하려면 이렇게 작성합니다.

SELECT name,
CASE
    WHEN attack >= 100 THEN 'very strong'
    WHEN attack >= 60 THEN 'strong'
    ELSE 'weak'
END AS attack_class
FROM mypokemon;

 

JOIN

JOIN은 말 그대로 테이블끼리 합칠 때 사용하는 키워드입니다.

가장 기본적으로 생각할 수 있는 JOIN 방식은 INNER JOIN 인데요, 쉽게 교집합으로 생각하시면 됩니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
INNER JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
WHERE 조건식;

이렇게 공통된 컬럼을 기준으로 조건을 걸어 조인할 수 있습니다.

좀 더 간단하게 적는 방법도 있는데요.

SELECT [컬럼 이름]
FROM [테이블 A 이름], [테이블 B 이름]
WHERE [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
AND [조건식]

이렇게 FROM 절에 테이블 이름을 나란히 적고 WHERE 절에 조건을 거는 방법도 있습니다.

 

다음으로 LEFT OUTER JOIN과 RIGHT OUTER JOIN이 있는데요.

LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 다 나오고, 오른쪽 테이블에 없는 데이터는 NULL로 표기합니다.

RIGHT OUTER JOIN에서 오른쪽에 있는 테이블을 왼쪽으로 옮기면 결국 LEFT OUTER JOIN이 되기 때문에

LEFT OUTER JOIN을 주로 사용한다고 합니다!

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN / RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] WHERE 조건식;

LEFT OUTER JOIN과 RIGHT OUTER JOIN도 마찬가지로 공통된 컬럼을 ON 절에 넣어서 조인합니다.

 

두 테이블에 있는 모든 값을 합치고 싶다면 어떻게 해야 할까요?

아쉽게도 MySQL에는 OUTER JOIN 키워드가 없어서

LEFT JOIN과 RIGHT JOIN을 UNION으로 연결해야 한다고 합니다.

이렇게 하면 LEFT JOIN 한 것과 RIGHT JOIN 한 결과를 중복 제외하고 합쳐서 보여줍니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름]
UNION
SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름];

UNION과 UNION ALL은 합집합을 의미하는 연산으로,

쿼리 1과 쿼리 2의 결과를 합쳐서 보여준다고 할 수 있습니다.

둘의 차이는 무엇일까요?

UNION은 중복 값을 제외하고 보여주는 거고, UNION ALL은 중복 값도 보여준다는 특징이 있습니다.

 

SELF JOIN은 같은 테이블에 있는 값을 합칠 때 사용하는데요.

컴퓨터/전자기기 > 데스크탑/노트북 > 삼성/애플/LG

이런 식으로 계층 구조가 있을 때 계층을 한번에 보여줄 경우 SELF JOIN을 사용한다고 합니다.

SELECT [컬럼 이름]
FROM [테이블 A 이름] AS t1
INNER JOIN [테이블 A 이름] AS t2
ON t1.[컬럼 A 이름] = t2.[컬럼 A 이름]
WHERE 조건식;

w3schools에서 JOIN 관련 예제를 몇 가지 살펴보겠습니다!

 

먼저 주문 ID와 사원 이름(FirstName, LastName), 주문 일자를 추출해보겠습니다.

SELECT O.OrderID, CONCAT(E.FirstName, ' ', E.LastName) Name, O.OrderDate
FROM Orders O, Employees E
WHERE O.EmployeeID = E.EmployeeID

사원 ID를 기준으로 Orders 테이블와 Employees 테이블을 INNER JOIN하여

필요한 데이터를 추출한 예시입니다.

CONCAT 함수를 통해 따로 있던 사원 이름을 하나의 컬럼으로 합쳤습니다.

위 코드를 실행하면 아래와 같은 결과가 나옵니다.

 

다음으로는 주문 이력이 없는 고객의 고객 ID와 고객 이름을 추출해보겠습니다.

SELECT C.CustomerID, C.CustomerName
FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE OrderID IS NULL

고객은 전체가 나오고 Orders 테이블에 해당 고객이 없으면 주문 이력이 없다고 판단해야 하기 때문에

Customers 테이블을 기준으로 LEFT OUTER JOIN을 한 예시입니다.

위 코드를 실행하면 아래와 같은 결과가 나옵니다.

 

SELF JOIN의 예시도 하나 볼까요?

SELECT A.EmployeeID, A.LastName, A.FirstName,
       B.EmployeeID BossID, B.LastName BossLN, B.FirstName BossFN
FROM Employees A, Employees B
WHERE A.EmployeeID = B.EmployeeID + 1

Employees 테이블에서 자신보다 사번이 1 적은 사번을 직속 상사라고 가정했을 때

자신의 사번과 이름, 상사의 사번과 이름을 출력하는 예시입니다.

위 코드를 실행하면 아래와 같은 결과가 나옵니다.

예제를 보니 좀 더 이해가 쉽지 않나요?

JOIN을 자유자재로 할 수 있으면 현업에서 SQL을 잘 다룬다고 인정 받는다고 해요!

저도 열심히 연습해보려고 합니다 💪