초보끼리

MySQL 에서 JOIN에 대해 알아보도록 하겠습니다.

0. JOIN 개요

SQL JOIN 이라고 검색하면 나오는 유명한(?) 그림 입니다. 일단 이 그림만 보아도 JOIN에 대한 개념을 어느정도 잡을 수 있습니다.

1. 테스트할 데이터 준비

CREATE TABLE IF NOT EXISTS `member` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `member` (`idx`, `email`, `username`) VALUES
	(1, '철수@초보끼리.com', '김철수'),
	(2, '영희@초보끼리.com', '이영희'),
	(3, '초보@초보끼리.com', '김초보');

CREATE TABLE IF NOT EXISTS `member_detail` (
  `member_idx` int(11) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `nickname` varchar(50) NOT NULL,
  `thumbnail` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `member_detail` (`member_idx`, `phone`, `nickname`, `thumbnail`) VALUES
	(1, '01011121112', '철수철수', '철수.png'),
	(3, '01012341234', '초보', '초보.jpg');

CREATE TABLE IF NOT EXISTS `order_address` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `member_idx` int(11) NOT NULL,
  `addressee` varchar(50) NOT NULL COMMENT '수취인',
  `address` varchar(200) NOT NULL,
  `phone` varchar(50) NOT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `order_address` (`idx`, `member_idx`, `addressee`, `address`, `phone`) VALUES
	(1, 1, '김철수', '서울시 강남구 타워펠리스 지하 4층', '01011121112'),
	(2, 1, '김철수 어머니', '서울시 강남구 삐까뻔적 오피스텔 2층 201호', '01012341234'),
	(3, 2, '김초보', '뉴욕시 마봉리 페리스 오피스텔 지하 2층', '01011112222'),
	(4, 10000, '비회원', '토쿄시 나루토군 라면이오이시 닌자아파트 5층 502호', '01011112222');

2. SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.key = B.key

SELECT * FROM member AS M
LEFT JOIN member_detail AS MD ON M.idx = MD.member_idx

3. SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.key = B.key WHERE B.key IS NULL

SELECT * FROM member AS M
LEFT JOIN member_detail AS MD ON M.idx = MD.member_idx
WHERE MD.member_idx IS NULL

4. SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.key = B.key 

SELECT * FROM member AS M
RIGHT JOIN order_address AS OA ON M.idx = OA.member_idx

5. SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.key = B.key WHERE A.key IS NULL

SELECT * FROM member AS M
RIGHT JOIN order_address AS OA ON M.idx = OA.member_idx
WHERE M.idx IS NULL

6. SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.key = B.key 

SELECT * FROM member AS M
INNER JOIN member_detail AS MD ON M.idx = MD.member_idx

7. SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key 

SELECT * FROM member AS M
LEFT JOIN order_address AS OA ON M.idx = OA.member_idx
UNION
SELECT * FROM member AS M
RIGHT JOIN order_address AS OA ON M.idx = OA.member_idx

8. SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key WHERE A.key IS NULL OR B.key IS NULL

SELECT * FROM (
SELECT M.*, OA.addressee, OA.member_idx, OA.address, OA.phone  FROM member AS M
LEFT JOIN order_address AS OA ON M.idx = OA.member_idx
UNION
SELECT M.*, OA.addressee, OA.member_idx,  OA.address, OA.phone  FROM member AS M
RIGHT JOIN order_address AS OA ON M.idx = OA.member_idx
) AS U
WHERE U.idx IS NULL OR U.member_idx IS NULL

MySQL에서 도메인에 값들의 갯수를 세어야 할 때가 있습니다. 그럴 때 사용할 수 있는 것이 SUM과 IF의 조합입니다.

1. 테스트할 데이터 준비

CREATE TABLE IF NOT EXISTS `count_test` (
`id` int(11) NOT NULL,
`state` enum('Y','N') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `count_test` (`id`, `state`) VALUES
(1, 'Y'),
(2, 'Y'),
(3, 'Y'),
(4, 'Y'),
(5, 'N'),
(6, 'Y'),
(7, 'N'),
(8, 'Y'),
(9, 'Y'),
(10, 'N');

2. 갯수 세기(SUM, IF)

SELECT SUM(IF(state = "Y", 1, 0)) AS y_cnt, SUM(IF(state = "N", 1, 0)) AS n_cnt FROM count_test

스키마를 엑셀에 문서화 하고 싶을 때 다음과 같이 쿼리를 생성하여 csv 혹은 엑셀로 export하여 사용하면 된다.

SELECT 
TABLE_SCHEMA, 
TABLE_NAME, 
COLUMN_NAME, 
CONCAT(DATA_TYPE,'(',IFNULL(CHARACTER_MAXIMUM_LENGTH,IFNULL(NUMERIC_PRECISION,'')),')') TYPE, 
COLUMN_KEY,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'isb_stg', 'isb_prd')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;