TIL_230501 - MySQL(Union~NULL Functions)

📒 오늘의 공부

1. MySQL

Union ~ 공식문서 MySQL SQL 끝

UNION

• select로 추출한 데이터 여러 개를 같이 보고 싶을 때

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

• 중복을 제거한 데이터를 출력
  • A, B테이블 모두 같은 City('Canada')가 존재할 때 'Canada'는 한 번만 출력

UNION ALL

• 중복을 포함한 모든 데이터 출력
  • 'Canada'가 여러 개 있어도 모두 출력

HAVING

• WHERE절은 집계 함수를 사용하여 계산된 결과에 대해 조건을 적용하지 못하기 때문에 HAVING 사용

• 집계 함수란?

  • 데이터베이스에서 사용되는 함수 중 하나로, 주어진 열 또는 집합에서 값을 계산하는 함수
  • COUNT, SUM, AVG...

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

• 집계 함수라 WHERE 대신 HAVING을 써야 함

EXISTS

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

• 하위 쿼리(subquery)의 결과가 존재하는지 여부를 검사하는 논리 연산자
  • 하위 쿼리가 반환하는 행이 하나 이상 존재하는 경우, 부모 쿼리는 SELECT 문의 열(column) 중 하나 이상을 반환

ANY, ALL

ANY

• 데이터 중 조건을 충족하는 데이터가 하나라도 있으면 true 반환(=SELECT 문에 지정된 column 값)

SELECT ProductName
FROM Products
WHERE ProductID = ANY
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);

• 하위쿼리(괄호 안의 SELECT문)에서 Quantity가 10인 ProductID가 하나라도 있으면 해당 ProductID로 Products에서 ProductName을 반환

ALL

• 하위쿼리(괄호 안의 SELECT문)에서 ProductID가 모두 Quantity가 10인 경우에 해당 ProductID로 Products에서 ProductName을 반환
  • 하위쿼리에서 ProductID가 모두 10이 아니라면 0을 반환

INSERT INTO SELECT

• 하나의 테이블에서 다른 테이블로 데이터를 복사
• SELECT 문의 결과를 새로운 테이블에 삽입

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

• 열의 개수와 이름이 일치할 경우

INSERT INTO B
SELECT * FROM A;

NULL Functions

• NULL 값을 다른 값으로 대체하는 기능

IFNULL() Function

IFNULL(expr1, expr2)

• 2개의 인수만 받는다
• 첫 번째 매개변수가 NULL인 경우 두 번째 매개변수를 반환

COALESCE() Function

COALESCE(value1, value2, ..., valueN)

• 여러 개의 인수를 받을 수 있다
• 입력된 인자 중 NULL이 아닌 첫 번째 값을 반환
• 모든 인자가 NULL인 경우 NULL

---

문자열, CASE

SUBSTRING_INDEX

• split이랑 비슷
• 이메일 쪼개기
  • SUBSTRING_INDEX(email, '@', 1)
    -> email의 앞부분 가져오기
  • SUBSTRING_INDEX(email, '@', 2)
    -> '@'로 쪼갠 부분의 1부터 2까지 모두 가져오기
  • SUBSTRING_INDEX(email, '@', -1)
    -> 쪼갠 마지막 부분만 가져오기

SUBSTRING

• slice랑 비슷
• SUBSTRING(문자열, 출력할 첫 글자의 위치, 출력하고 싶은 글자 수)

CASE

• 조건에 따라 결과를 다르게 처리하기 위해 사용

CASE
  WHEN 조건1 THEN 결과1
  WHEN 조건2 THEN 결과2
  ...
  ELSE 기본결과
END

• with절로 전체 강의 수, 들은 강의 수를 각각 구하는 경우 enrolleds_detail 테이블을 두 번 스캔하기 때문에 성능상 문제 발생 가능

with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

• CASE를 이용해서 더 간결하게 표현 가능

  SELECT 
    ed.enrolled_id, 
    COUNT(CASE WHEN ed.done = 1 THEN 1 END) AS cnt_done, 
    COUNT(*) AS cnt_total
  FROM 
    enrolleds_detail ed
  GROUP BY 
    ed.enrolled_id;

• 가장 간결하게 표현

  select enrolled_id,
   sum(done) as cnt_done,
   count(*) as cnt_total
  from enrolleds_detail ed
  group by enrolled_id;

SUBQUERY

• 두 개 이상의 테이블 간에 JOIN을 수행하지 않고도 원하는 데이터 검색 가능

  • 서브쿼리를 사용하여 하나의 테이블에서 데이터를 검색 후 그 결과를 기반으로 다른 테이블에서 데이터를 검색

• 너무 많은 서브쿼리 사용은 성능에 부정적인 영향을 미칠 수 있음

  • SUBQUERY 사용 했을 때(강의 자료 정답)

    select domain, count(*) as cnt from (
    select SUBSTRING_INDEX(email,'@',-1) as domain from users
    ) a
    group by domain

  • SUBQUERY 사용하지 않았을 때(나의 풀이)

    SELECT SUBSTRING_INDEX(u.email, '@', -1) as domain, COUNT(*) as cnt_domain  FROM users u
    group by domain

  • 둘 모두 같은 값을 반환하지만 나의 풀이는 서브쿼리를 사용하지 않았기 때문에 구문이 더 간결하고 가독성이 높다

참고

MySQL_w3schools
SQL문법 정리_내 블로그