[SQL] SQL 문법 - ➁

SQL 문법

 JOIN 

- 두 개의 테이블을 엮어서 원하는 데이터를 추출할 수 있다.

SELECT * FROM users u
LEFT JOIN point_users p ON u.user_id = p.user_id
-- INNER JOIN - 교집합만 join 됨 / LEFT 는 null 값도 join

users 테이블에 user_id 와 point_users 테이블에 user_id 가 같은 것들을 한 테이블에 모아준다.

LEFT JOIN 은 붙이는 순서가 중요하지만 INNER JOIN 은 교집합이므로 순서는 상관이 없다.

 

SELECT u.name, COUNT(*) AS cnt FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.email LIKE '%naver.com'
GROUP BY u.name

* 쿼리가 진행되는 순서

FROM -> JOIN -> WHERE -> GROUP BY -> SELECT

 

SELECT c1.title, c2.week, COUNT(*) as cnt FROM courses c1 
INNER JOIN checkins c2 ON c1.course_id = c2.course_id 
INNER JOIN orders o  ON c2.user_id = o.user_id 
WHERE o.created_at >= '2020-08-01'
GROUP BY c1.title, c2.week 
ORDER BY c1.title, c2.week

* 날짜 조건을 걸 때는 '  ' 안에 날짜를 적어주면 된다.

 

 UNION

- 여러 쿼리문을 사용하여 한 데이터로 출력을 해야 할 때 결과를 합쳐주는 명령

(
select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at < '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)
UNION ALL
(
select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)

* UNION 으로 합쳐주면 ORDER BY 로 정렬해 두었던 데이터의 순서가 초기화된다 (=ORDER BY 가 적용이 안된다.

   서브 쿼리를 이용하여 실행해줘야 한다.)

 

 SUBQUERY 

- SQL 문 안에 또 다른 SQL 문이다.

 

SELECT c.checkin_id,
	   c.user_id,
	   c.likes,
	   (SELECT AVG(likes) FROM checkins WHERE user_id = c.user_id) as avg_likes_user -- Subquery
  FROM checkins c

쿼리가 실행될 때마다 괄호 안에 있는 서브 쿼리도 실행된다.

 

SELECT pu.user_id, pu.point, a.avg_likes FROM point_users pu 
INNER JOIN (
	SELECT user_id, ROUND(AVG(likes), 1) AS avg_likes FROM checkins
	GROUP BY user_id 
	) a on pu.user_id = a.user_id

서브 쿼리의 결과물을 하나의 테이블 ( a ) 처럼 INNER JOIN 을 할 수도 있다.

 

(서브 쿼리 예시)

-- WHERE 절에 들어가는 Subquery
SELECT * FROM point_users pu 
WHERE point > (
	SELECT AVG(point) FROM point_users pu
	INNER JOIN users u ON pu.user_id = u.user_id 
	WHERE u.name = '이**'
)

-- SELECT 절에 들어가는 Subquery
SELECT c.checkin_id,
	   c.course_id,
	   c.user_id,
	   c.likes,
	   (SELECT AVG(likes) FROM checkins
	    WHERE course_id = c.course_id) as avg_likes
	FROM checkins c

-- FROM 절에 들어가는 Subquery
SELECT c.title,
	   a.cnt_checkins,
	   b.cnt_total,
	   (a.cnt_checkins / b.cnt_total) as ratio
	FROM 
(
	SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins FROM checkins
	GROUP BY course_id
) a 
	INNER JOIN
(
	SELECT course_id, COUNT(*) as cnt_total FROM orders
	GROUP BY course_id 	
) b ON a.course_id = b.course_id
	INNER JOIN courses c ON a.course_id = c.course_id

 

 WITH 

- 서브 쿼리를 임시 테이블화 시켜주는 명령어, 서브 쿼리들을 치환함으로써 코드의 가독성을 높일 수 있다.

WITH table1 AS (
	SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins FROM checkins
	GROUP BY course_id
), table2 AS (
	SELECT course_id, COUNT(*) as cnt_total FROM orders
	GROUP BY course_id 
)

SELECT c.title,
	   a.cnt_checkins,
	   b.cnt_total,
	   (a.cnt_checkins / b.cnt_total) as ratio
	FROM table1 a 
	INNER JOIN table2 b ON a.course_id = b.course_id
	INNER JOIN courses c ON a.course_id = c.course_id

 

기타 SQL 문법

SUBSTRINIG_INDEX

SELECT user_id, email, SUBSTRING_INDEX(email, '@', -1) FROM users
SELECT order_no, created_at, SUBSTRING_INDEX(created_at, 1, 10) FROM orders

email 필드의 데이터들을 '@' 기준으로 나누어 앞에 것만 출력

created_at 필드의 데이터들의 1번째 텍스트부터 10개의 텍스 출력

 

CASE

SELECT pu.point_user_id, pu.point,
	   (CASE WHEN pu.point > 10000 THEN '잘 하고 있어요!'
		ELSE '조금 더 달려주세요!' END) AS 'msg'
FROM point_users pu

조건을 두어 테이블 출력 가능