1. DQL(Data Query Language)
✅ DQL이란?
[그림0] SQL statements의 종류
DQL은 Data Query Language의 약자로 데이터를 쿼리하는데 사용되는 SQL문을 뜻한다. 쿼리(Query)의 사전적 의미는 묻다, 질문하다 라는 뜻이다. 데이터베이스의 상황에 빗대어 보면, “데이터베이스 서버에게 데이터를 달라고 요청하는 것” 이라고 해석할 수 있다. 대표적으로 SELECT 문이 있다.
SELECT 문장은 앞서 살펴보았던 INSERT나 UPDATE 같은 작업과 비교해 봤을 때, 애플리케이션에서 사용되는 쿼리 중에서도 가장 비율이 높고, 잘 알고 사용하지 못하면 성능상 문제가 발생할 수 있기 때문에 가장 중요한 건 SQL이다. INSERT나 UPDATE 작업은 대부분 행(row) 단위로 처리되지만, SELECT는 여러 개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러 개의 테이블들을 어떻게 읽을 것인가에 따라서 성능이 크게 달라질 수 있다. 정리하면, SELECT 문장은 테이블에 저장된 데이터를 꺼내오는 핵심 쿼리문이다.
✅ MySQL의 DQL 문법 표기
데이터를 조회/검색 하는데 사용되는 DQL 사용 방법과 예제를 살펴보기에 앞서, MySQL 8.0 Reference Manual SELECT Statement에 명시된 DQL 문법 표기를 찾아보자. 모든 RDBMS가 DQL을 이용해서 데이터 작업을 수행하지만, RDMBS의 종류와 각 버전에 따라서 DQL 문법이 다를 수 있기 때문에, 공식문서에서 제공하는 메뉴얼의 SQL 문법을 참조하는 것이 좋다. 추가로 자료를 검색할 때, DQL을 DML로 분류해서 설명하는 자료도 있다.
📍 SELECT Statement
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]
into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}
위 SELECT 문장에서 대괄호(”[]”)는 해당 키워드나 표현식 자체가 선택 사항임을 의미하고, 파이프(”|”)는 해당 키워드나 표현식 중에서 단 하나만 선택해서 사용할 수 있음을 의미한다. 중괄호(”{}”)는 괄호 내의 아이템 중에서 반드시 하나를 사용해야하는 필수 사항임을 의미한다. 마지막으로 “...” 표기는 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미. 많은 키워드와 표현식 중에서 기본적으로 자주 사용되는 키워드 위주로 축소해보면 다음과 같다.
SELECT
[ALL | DISTINCT | DISTINCTROW ]
select_expr [, select_expr] ...
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]2. 테스트 데이터 구축
[그림1] 예제로 사용될 사용자, 게시물, 댓글, 좋아요 테이블
✅ users 테이블
mysql> INSERT INTO users (name, email, password, age) VALUES ("Rebekah Johnson", "Glover12345@email.com", "password", 30);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Fabian Predovic", "O'Connell12345@email.com", "password", 31);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Elenor Gottlieb", "Skiles12345@email.com", "password", 22);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Madge Ledner", "Quitzon12345@email.com", "password", 23);mysql> SELECT * FROM users;
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| id | name | email | password | created_at | updated_at | age |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| 1 | Rebekah Johnson | Glover12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 21 |
| 2 | Fabian Predovic | Connell12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 22 |
| 3 | Elenor Gottlieb | Skiles12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 23 |
| 4 | Madge Ledner | Quitzon12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 20 |
| 5 | Zelma Kunde | Kozey12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 19 |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+✅ posts 테이블
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 1일차", "HTML과 CSS 익숙해지기..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 2일차", "Javascript 기본 문법 학습..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 3일차", "웹서비스의 역사와 발전 세션을 듣고..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("자료구조 1번", "BigO Notation이란 무엇인가?", 2);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("자료구조 2번", "시간 복잡도와 공간 복잡도에 대해서..", 2);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("프론트 개발 입문", "프론트 입문 HTML이란 무엇인가?", 3);mysql> SELECT * FROM posts;
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | 2022-04-10 11:41:36 | NULL |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | 2022-04-18 11:41:38 | NULL |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고..| 1 | 2022-05-02 11:41:39 | NULL |
| 4 | 자료구조 1번 | BigO Notation이란 무엇인가?.. | 2 | 2022-04-13 11:41:39 | NULL |
| 5 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서..| 2 | 2022-04-19 11:41:39 | NULL |
| 6 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | 2022-05-23 11:43:25 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+3. 데이터 조회/검색
✅ 모든 데이터 조회
“Westagram 홈 화면"에 모든 사람들이 올린 게시물 목록을 보여준다고 가정해보자. 이때, SELECT 문장을 사용하여 posts 테이블에 존재하는 모든 게시물 데이터를 검색할 수 있다. 물론 실제로 테이블에 존재하는 모든 게시글을 조회하는 것은 문제가 되겠지만, 설명을 위해서 모든 게시물을 조회한다고 가정해보자. 아래와 같이 SELECT 절과 FROM 절 두 개로 만들어진 SELECT 문장을 사용해서 쿼리할 수 있다.
mysql> SELECT * FROM posts;
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | 2022-04-10 11:41:36 | NULL |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | 2022-04-18 11:41:38 | NULL |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고..| 1 | 2022-05-02 11:41:39 | NULL |
| 4 | 자료구조 1번 | BigO Notation이란 무엇인가?.. | 2 | 2022-04-13 11:41:39 | NULL |
| 5 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서..| 2 | 2022-04-19 11:41:39 | NULL |
| 6 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | 2022-05-23 11:43:25 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+✅ 검색 조건 지정하기
데이터 검색 조건에는 열(column)을 지정하는 방법과 행(row)을 지정하는 방법으로 나누어진다.
📍열 지정하기
위의 SELECT * FROM posts; 쿼리문을 통한 결과를 보면 너무 길기 때문에 한 눈에 보기가 어렵다. 이럴때는 원하는 칼럼만 표시해 달라는 쿼리문을 작성할 수 있다. 현재 posts 테이블에 있는 게시글의 타이틀 들만 뽑아내고 싶을 땐 다음과 같은 쿼리문을 도출할 수 있다.
mysql> SELECT posts.title FROM posts; (혹은 mysql> SELECT title FROM posts;)
+-------------------+
| title |
+-------------------+
| 위코드 1일차 |
| 위코드 2일차 |
| 위코드 3일차 |
| 자료구조 1번 |
| 자료구조 2번 |
| 프론트 개발 입문 |
+-------------------+내용과 함께 보고싶을 때는 다음과 같이 , 로 구분해서 나열 해 주면 여러 칼럼의 값을 테이블에서 뽑아올 수 있다.
mysql> SELECT posts.title, posts.content FROM posts;
+-------------------+-----------------------------------------------------+
| title | content |
+-------------------+-----------------------------------------------------+
| 위코드 1일차 | HTML과 CSS 익숙해지기.. |
| 위코드 2일차 | Javascript 기본 문법 학습.. |
| 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고.. |
| 자료구조 1번 | BigO Notation이란 무엇인가? |
| 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서.. |
| 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? |
+-------------------+-----------------------------------------------------+📍 WHERE 절 (WHERE 키워드 + 기본 조건 표현식)
WHERE 키워드를 사용한 WHERE 절은 앞서 살펴보았듯, 조건을 걸어줄 수 있다. posts 테이블 내에 수백 건에서 천만 건의 행(row)에 달하는 데이터가 있을 경우, 필요한 행(row)만 검색하기 위해서 사용한다. 예를 들어서, 위스타그램 전체 게시물 중에서 “Rebekah Johnson” (users 테이블에 첫번째 row에 저장된 사용자)이 작성한 게시물만 출력하고 싶다면 다음과 같은 쿼리문을 작성할 수 있다.
mysql> SELECT id, title, content, user_id FROM posts WHERE user_id = 1;
+----+-------------------+------------------------------+---------+
| id | title | content | user_id |
+----+-------------------+------------------------------+---------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고.. | 1 |
+----+-------------------+------------------------------+---------+WHERE 키워드 뒤에 조건 표현식(user_id=1)을 사용해서 WHERE 절(WHERE user_id=1)을 만든다. 여기서 =는 이항 연산자로 좌변과 우변의 항목을 비교하고 서로 같은 값이면 참을 같지 않으면 거짓을 반환한다. 이렇게 비교한 결과값으로 True/False로 반환하는 연산자를 비교 연산자라고 부른다. 비교 연산자는 = 외에도 <>, >, ≥, <, ≤ 등 여러 연산자를 조합해서 복잡한 조건 표현식을 만들 수 있다. 다양한 연산자는 다음 링크를 확인하자.
📍 WHERE 절 (WHERE 키워드 + 복수의 조건 표현식)
WHERE 키워드 다음에 복수의 조건 표현식을 조합해서 사용하는 방법에 대해서 알아보자. 조합할 때는 AND, OR, NOT을 사용할 수 있다. AND 연산을 사용하여 “Rebekah Johnson” (user_id=1)이 작성한 게시물 중에서 생성일(created_at)이 “2022-04-15 00:00:00” 이전인 게시글만 검색해보자.
SELECT * FROM posts;
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | 2022-04-10 11:41:36 | NULL |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | 2022-04-18 11:41:38 | NULL |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고..| 1 | 2022-05-02 11:41:39 | NULL |
| 4 | 자료구조 1번 | BigO Notation이란 무엇인가?.. | 2 | 2022-04-13 11:41:39 | NULL |
| 5 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서..| 2 | 2022-04-19 11:41:39 | NULL |
| 6 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | 2022-05-23 11:43:25 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+
SELECT * FROM posts WHERE user_id = 1 AND created_at < '2022-04-15 00:00:00';
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | 2022-04-10 11:41:36 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+결과에서 보면 AND 연산은 조건을 만족하는 행을 집합으로 표현했을 때 이들 집합이 겹치는 교집합으로 계산할 수 있다. OR 연산은 합집합으로 계산한다.
추가로 NOT 연산자는 단항 연산자로 오른쪽에 조건 표현식이 참(True)이면 NOT이 붙어서 반대의 거짓(False)를 반환한다. 위에 SELECT 문에 NOT을 추가해보면, user_id = 1 AND created_at < '2022-04-15 00:00:00'; 조건에 반대로 1번 게시물을 제외한 나머지 부분을 결과로 반환한다.
SELECT SELECT * FROM posts WHERE NOT(user_id = 1 AND created_at < '2022-04-15 00:00:00');
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | 2022-04-18 11:41:38 | NULL |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고..| 1 | 2022-05-02 11:41:39 | NULL |
| 4 | 자료구조 1번 | BigO Notation이란 무엇인가?.. | 2 | 2022-04-13 11:41:39 | NULL |
| 5 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서..| 2 | 2022-04-19 11:41:39 | NULL |
| 6 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | 2022-05-23 11:43:25 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+📍 WHERE 절 + LIKE 패턴 매칭
지금까지 조건 검색을 하는 경우에 = 연산자를 사용했다. = 연산자로 검색할 경우에는 열 값이 완전히 일치할 때만 참(True)를 반환하여 결과를 검색한다. 그렇기 때문에, 위스타그램 서비스를 이용중 게시물의 내용에 포함되어 있는 문자열을 부분 검색하는 경우에는 검색 결과를 제대로 얻을 수 없다.
예를들어, 사용자가 게시물의 내용(content)에 “HTML”이라는 문자열만으로도 게시글을 검색하고 싶은 경우엔 LIKE 서술어를 사용할 수 있다.
# 예제 테이블
SELECT * FROM posts;
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title | content | user_id | created_at | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | 2022-04-10 11:41:36 | NULL |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | 2022-04-18 11:41:38 | NULL |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고..| 1 | 2022-05-02 11:41:39 | NULL |
| 4 | 자료구조 1번 | BigO Notation이란 무엇인가?.. | 2 | 2022-04-13 11:41:39 | NULL |
| 5 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서..| 2 | 2022-04-19 11:41:39 | NULL |
| 6 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | 2022-05-23 11:43:25 | NULL |
+----+-------------------+-----------------------------+----------+---------------------+------------+LIKE 서술어와 메타문자 %를 합쳐서, 문자열 맨 앞의 단어와 일차하는 경우, 문자열의 중간에 검색하고자 하는 단어가 포함되어 있는 경우 혹은 맨 뒤에 있는 경우 등 다양한 경우에 사용할 수 있는 패턴을 만들 수 있다.
첫 번째, 문자열 맨 앞의 단어와 일차하는 경우 검색하는 방법을 살펴보자. LIKE ‘HTML%’ 를 사용한 방법으로 검색하고자 하는 텍스트 뒤에 메타문자 %를 붙여준다. 텍스트 뒤에 %가 붙어있는 것은 HTML 텍스트 뒤로 임의의 문자열이 존재한다는 것을 의미한다.
SELECT id, title, content, user_id FROM posts WHERE content LIKE 'HTML%';
+----+-------------------+-------------------------------+---------+
| id | title | content | user_id |
+----+-------------------+-------------------------------+---------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 |
+----+-------------------+-------------------------------+---------+두번째, LIKE %HTML%를 사용한 방법으로 문자열의 중간에 검색하고자 하는 단어가 포함되어 있는 경우다. 첫 번째 행(row)의 content에서 HTML은 텍스트 중간이 아니라 처음에 포함되어 있는데 검색됐다. 그 이유는 HTML 앞에 %는 임의의 문자열 뿐만아니라 빈 문자열(’’)에도 매치되기 때문이다.
SELECT id, title, content, user_id FROM posts WHERE content LIKE '%HTML%';
+----+-------------------------+-------------------------------------------+---------+
| id | title | content | user_id |
+----+-------------------------+-------------------------------------------+---------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 |
| 7 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 |
+----+-------------------------+-------------------------------------------+---------+LIKE 서술어는 %이외 메타문자를 사용해서 문자열을 검색하는데, 다양한 특수문자와 해당하는 의미는 다음 링크를 확인.
4. 테이블 결합
테이블 결합은 RDBMS의 꽃이라고 할 수 있는 중요한 개념이다. 지금까지는 하나의 테이블을 사용하는 SQL 명령만 배웠다. 그러나 실제 서비스에서 데이터베이스는 하나의 테이블에 많은 데이터를 저장하지 않고 데이터를 정규화해서 여러 개의 테이블로 나누어 저장한다. 그렇기 때문에 실제로 웹 시비스에서 필요로 하는 다양하고 복잡한 데이터를 보여주기 위해서는 복수의 테이블을 결합해서 데이터를 검색할 수 있어야 한다.
FROM 절 뒤에 올 수 있는 표현식을 통해서 복수의 테이블들의 결합에 관해서 알아보자. 이번에는 위스타그램 게시물 목록에서 각 게시물 마다 사용자 이름, 제목, 그리고 내용을 한번에 보여주기 위해서, users과 posts 테이블을 결합하여 아래와 같은 결과를 만들어야 한다고 가정한다.
// 목표 결과
+----+--------------+------------------------------+----+-----------------+
| id | title | content | id | name |
+----+--------------+------------------------------+----+-----------------+
| 1 | 위코드 1일차 | HTML과 CSS 익숙해지기.. | 1 | Rebekah Johnson |
| 2 | 위코드 2일차 | Javascript 기본 문법 학습.. | 1 | Rebekah Johnson |
| 3 | 위코드 3일차 | 웹서비스의 역사와 발전 세션을 듣고.. | 1 | Rebekah Johnson |
| 5 | 자료구조 1번 | BigO Notation이란 무엇인가? | 2 | Fabian Predovic |
| 6 | 자료구조 2번 | 시간 복잡도와 공간 복잡도에 대해서.. | 2 | Fabian Predovic |
| 7 | 프론트 개발 입문 | 프론트 입문 HTML이란 무엇인가? | 3 | Elenor Gottlieb |
+----+--------------+------------------------------+----+-----------------+✅ 곱집합
테이블 간의 결합을 제대로 이해하기 위해서 먼저 곱집합이라는 개념을 이해해야 한다. 곱집합은 우리가 알고 있는 합집합(UNION)이나 교집합(INTERSECT)처럼 집합의 연산 방법 중에 하나다. 곱집합은 말 그대로 두 개의 집합을 곱하는 연산 방법으로, 한 시즌에 8화까지 있는 비밀의 숲 드라마가 시즌 2까지 방영되는 방송표를 만드는 경우를 생각해볼 수 있다. 시즌 집합 {시즌1, 시즌2, 시즌3}과 회차 집합 {1화, 2화, 3화, 4화, 5화, 6화, 7화, 8화}으로 아래와 같은 편성표를 만들 수 있다.
[그림2] 집합 A와 집합 B의 곱집합
// 비밀의 숲
+---------+----------+---------+---------+----------+---------+---------+---------+
| 시즌1-1화 | 시즌1-2화 | 시즌1-3화 | 시즌1-4화 | 시즌1-5화 | 시즌1-6화 | 시즌1-7화 | 시즌1-8화 |
+---------+----------+---------+---------+----------+---------+---------+---------+
| 시즌2-1화 | 시즌2-2화 | 시즌2-3화 | 시즌2-4화 | 시즌2-5화 | 시즌2-6화 | 시즌2-7화 | 시즌2-8화 |
+---------+----------+---------+---------+----------+---------+---------+---------+
| 시즌3-1화 | 시즌3-2화 | 시즌3-3화 | 시즌3-4화 | 시즌3-5화 | 시즌3-6화 | 시즌3-7화 | 시즌3-8화 |
+---------+----------+---------+---------+----------+---------+---------+---------+✅ 교차 결합(Cross Join)
위에서 시즌 집합과 회차 집합의 곱집합으로 편성표를 만들었는데, 데이터베이스에서 테이블(집합)과 테이블(집합)의 곱집합을 계산하는 방법으로 교차결합(Cross JOIN)이 있다. FROM 절에 복수의 테이블을 ,로 구분지어 지정하면 아래와 같이 users 테이블과 posts 테이블을 교차결합 할 수 있다.
[그림3] Cross Join
//users 테이블과 posts 테이블을 집합으로 생각해보면 아래와 같습니다.
{user1, user2, user3, user4} X {post1, post2, post3, post4, post5}
(user1, post1), (user1, post2), (user1, post3), (user1, post4), (user1, post5)
(user2, post1), (user2, post2), (user2, post3), (user2, post4), (user2, post5)
(user3, post1), (user3, post2), (user3, post3), (user3, post4), (user3, post5)
(user4, post1), (user4, post2), (user4, post3), (user4, post4), (user4, post5)
(user5, post1), (user5, post2), (user5, post3), (user5, post4), (user5, post5)
mysql> SELECT
posts.id,
posts.title,
posts.user_id,
posts.content,
users.id,
users.name
FROM posts, users
+----+------------+---------+----------------------------+----+------------------+
| id | title | user_id | content | id | name |
+----+------------+---------+----------------------------+----+------------------+
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 1 | Rebekah Johnson |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 2 | Fabian Predovic |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 3 | Elenor Gottlieb |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 4 | Madge Ledner |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 5 | Zelma Kunde |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 1 | Rebekah Johnson |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 2 | Fabian Predovic |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 3 | Elenor Gottlieb |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 4 | Madge Ledner |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 5 | Zelma Kunde |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 1 | Rebekah Johnson |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 2 | Fabian Predovic |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 3 | Elenor Gottlieb |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 4 | Madge Ledner |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 5 | Zelma Kunde |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 1 | Rebekah Johnson |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 2 | Fabian Predovic |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 3 | Elenor Gottlieb |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 4 | Madge Ledner |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 5 | Zelma Kunde |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 1 | Rebekah Johnson |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 2 | Fabian Predovic |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 3 | Elenor Gottlieb |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 4 | Madge Ledner |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 5 | Zelma Kunde |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 1 | Rebekah Johnson |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 2 | Fabian Predovic |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 3 | Elenor Gottlieb |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 4 | Madge Ledner |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 5 | Zelma Kunde |
+----+------------+---------+----------------------------+----+------------------+✅ 내부 결합 (Inner Join)
위에서 설명한 교차 결합(Cross Join)은 필요한 경우가 없고, 비용이 매우 많이 드는 연산이기 때문에 실무에서는 사용되지 않는다. 그럼에도 교차 결합을 설명한 것은 교차 결합(Cross Join)이 지금 설명할 내부 결합(Inner Join)과 이후에 설명할 외부 결합의 근간이 되기 때문이다.
위에서 교차 결합으로 얻은 결과를 기준으로 이번에는 자주 사용하는 내부 결합(Inner Join)에 대해 알아보자. 내부 결합(Inner Join)은 기준이 되는 테이블 (left table)과 join이 걸리는 테이블(right table) 양쪽 모두에 결합조건이 matcing되는 row만 검색하는 방법이다.
[그림4] Inner Join
먼저, 위 교차결합 계산 결과 만들어진 집합에서 우리가 목표로 하는 결과를 아래 테이블에서 하이라이트 되어 있다. 자세히 살펴보면, users 테이블의 id와 posts 테이블의 user_id가 매칭되는 row만을 검색하면 원하는 결과를 얻을 수 있을 것 같아 보인다.
+----+------------+---------+----------------------------+----+------------------+
| id | title | user_id | content | id | name |
+----+------------+---------+----------------------------+----+------------------+
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 1 | Rebekah Johnson |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 2 | Fabian Predovic |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 3 | Elenor Gottlieb |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 4 | Madge Ledner |
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 5 | Zelma Kunde |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 1 | Rebekah Johnson |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 2 | Fabian Predovic |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 3 | Elenor Gottlieb |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 4 | Madge Ledner |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 5 | Zelma Kunde |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 1 | Rebekah Johnson |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 2 | Fabian Predovic |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 3 | Elenor Gottlieb |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 4 | Madge Ledner |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 5 | Zelma Kunde |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 1 | Rebekah Johnson |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 2 | Fabian Predovic |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 3 | Elenor Gottlieb |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 4 | Madge Ledner |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 5 | Zelma Kunde |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 1 | Rebekah Johnson |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 2 | Fabian Predovic |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 3 | Elenor Gottlieb |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 4 | Madge Ledner |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 5 | Zelma Kunde |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 1 | Rebekah Johnson |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 2 | Fabian Predovic |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 3 | Elenor Gottlieb |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 4 | Madge Ledner |
| 7 | 프론트 개발 | 3 | 프론트 입문 HTML이란 무엇인가? | 5 | Zelma Kunde |
+----+------------+---------+----------------------------+----+------------------+이렇게 교차 결합으로 계산된 곱집합에 결합조건(users.id = posts.user_id)을 더해서 검색하는 것을 내부결합(Inner Join)이라고 부른다. 이때, WHERE절을 사용해서 내부결합을 계산할 수 있다. 결과와 같이 내부 결합은 교차 결합의 부분집합이다. (왜 이름이 “내부” 결합인지 알 수 있다)
mysql> SELECT
posts.id,
posts.title,
posts.user_id,
posts.content
users.id,
users.name
FROM posts, users
WHERE posts.user_id = users.id;
+----+--------------+---------+----------------------------+----+-----------------+
| id | title | user_id | content | id | name |
+----+--------------+---------+----------------------------+----+-----------------+
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 1 | Rebekah Johnson |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 1 | Rebekah Johnson |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 1 | Rebekah Johnson |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 2 | Fabian Predovic |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 2 | Fabian Predovic |
| 7 | 프론트 개발 입문 | 3 | 프론트 입문 HTML이란 무엇인가? | 3 | Elenor Gottlieb |
+----+--------------+---------+----------------------------+----+-----------------+위에 방식으로 내부결합을 계산해도 되지만, 지금은 INNER JOIN 키워드를 사용해서 내부결합을 계산한다. MySQL 공식 문서에는 어떻게 가이드를 하고 있는지 살펴보면 쉽게 납득할 수 있다. SELECT 문장 안에 FROM 키워드 뒤에 table_references가 있다. 여기서 table_references가 무엇인지 MySQL 8.0 Reference Manual JOIN Clause에서 확인해 볼 수 있다.
# SELECT statement
SELECT
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
# JOIN Clause
table_references:
escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference: {
table_reference
| { OJ table_reference }
}
table_reference: {
table_factor
| joined_table
}
joined_table: {
table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}
join_specification: {
ON search_condition
| USING (join_column_list)
}
조금 더 이해하기 쉬운 형태로 바꿔보면 다음과 같다.
# SELECT statement
mysql> SELECT *
FROM table_1
INNER JOIN table_2 ON search_condition
WHERE where_conditionINNER JOIN으로 users 테이블과 posts 테이블을 user_id 기준으로 결합할 수 있다.
mysql> SELECT
posts.id,
posts.title,
posts.user_id,
posts.content
users.id,
users.name
FROM posts
INNER JOIN users ON posts.user_id = users.id;
+----+--------------+---------+----------------------------+----+-----------------+
| id | title | user_id | content | id | name |
+----+--------------+---------+----------------------------+----+-----------------+
| 1 | 위코드 1일차 | 1 | HTML과 CSS 익숙해지기.. | 1 | Rebekah Johnson |
| 2 | 위코드 2일차 | 1 | Javascript 기본 문법 학습.. | 1 | Rebekah Johnson |
| 3 | 위코드 3일차 | 1 | 웹서비스의 역사와 발전 세션을 듣고.| 1 | Rebekah Johnson |
| 5 | 자료구조 1번 | 2 | BigO Notation이란 무엇인가? | 2 | Fabian Predovic |
| 6 | 자료구조 2번 | 2 | 시간 복잡도와 공간 복잡도에 대해서.| 2 | Fabian Predovic |
| 7 | 프론트 개발 입문 | 3 | 프론트 입문 HTML이란 무엇인가? | 3 | Elenor Gottlieb |
+----+--------------+---------+----------------------------+----+-----------------+✅ 외부 결합 (Outer Join)
외부 결합(Outer Join)에는 대표적으로 LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN이 있다. 외부 결합은 내부 결합과 함께 자주 사용되는 결합으로 결합(Join)하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블을 기준으로 모두 출력하는 결합 방법이다.
[그림5] Outer Join의 종류
외부 결합을 쉽게 설명하기 위해서 새롭게 jobs라는 테이블을 추가하고 users 테이블에 job_id 열을 추가해 본다. 이후 jobs 테이블과 users 테이블의 데이터가 아래와 같이 되도록 추가했다.
mysql> CREATE TABLE jobs (
id INT NOT NULL AUTO_INCREMENT,
job VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(id)
);
mysql> ALTER TABLE users ADD COLUMN job_id INT NULL;
mysql> ALTER TABLE users ADD CONSTRAINT users_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(id);# jobs table
+----+-----------+
| id | job |
+----+-----------+
| 1 | 개발자 |
| 2 | 경찰 |
| 3 | 소방관 |
| 4 | 교사 |
| 5 | 건축가 |
+----+-----------+
# users table
+----+-----------------+-----+--------+
| id | name | age | job_id |
+----+-----------------+-----+--------+
| 1 | Rebekah Johnson | 21 | 1 |
| 2 | Fabian Predovic | 22 | 1 |
| 3 | Elenor Gottlieb | 23 | 1 |
| 4 | Madge Ledner | 20 | 2 |
| 5 | Zelma Kunde | 19 | NULL |
+----+-----------------+-----+--------+
# Inner Join
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users
INNER JOIN jobs ON users.job_id = jobs.id;
+----+-----------------+--------+-----------+
| id | name | job_id | job |
+----+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
+----+-----------------+--------+-----------+📍 LEFT (OUTER) JOIN
기준이되는 테이블 (left table)의 모든 row와 join이 걸리는 테이블(right table) 중에서 left table과 매칭되는 row만 검색한다. 기준이 되는 테이블(left table) 쪽에만 존재하는 row는 전부 그대로 가져오면서, join이 걸리는 테이블에서 매칭되는 결과가 없는 경우 빈 값(NULL)로 표시한다.
따라서, 유저 이름과 직업을 결합하고 싶은데, 모든 유저를 그대로 유지하는 테이블을 만들 때 자주 사용된다.
# Left Join
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users LEFT JOIN jobs ON users.job_id = jobs.id;
+----+-----------------+--------+-----------+
| id | name | job_id | job |
+----+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| 5 | Zelma Kunde | NULL | NULL |
+----+-----------------+--------+-----------+📍 RIGHT (OUTER) JOIN
join이 걸리는 테이블(right table)의 모든 row와 기준이 되는 테이블 (left table)에서 right table과 matching되는 row만 검색하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users
RIGHT JOIN jobs ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| NULL | NULL | NULL | 건축가 |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
+------+-----------------+--------+-----------+📍 FULL (OUTER) JOIN
LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 결합 방법으로 기준이 되는 테이블(left table)과 join이 걸리는 테이블(right table) 양쪽 모두의 row를 검색한다. MySQL에서는 FULL OUTER JOIN과 같은 직접적인 키워드가 없기 때문에, 아래와 같이 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION 하는 방식으로 FULL OUTER JOIN을 구현할 수 있다.
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users LEFT JOIN jobs ON users.job_id = jobs.id
UNION
SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users RIGHT JOIN jobs ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| 5 | Zelma Kunde | NULL | NULL |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
| NULL | NULL | NULL | 건축가 |
+------+-----------------+--------+-----------+5. 정리
DQL은 Data Query Language 의 약자로 데이터를 쿼리하는데 사용되는 SQL문을 뜻한다.- 기본적인
SELECT문에WHERE,LIKE를 사용하여 제한적인 조건이 적용된 데이터를 선택할 수 있습다. JOIN문을 사용하면 서로 다른 테이블 공유하는 컬럼을 기준으로 연결된 데이터를 쿼리할 수 있다.내부 결합(Inner Join)은 기준이 되는 테이블 (left table)과 join이 걸리는 테이블(right table) 양쪽 모두에 결합조건이 matcing되는 row만 검색하는 방법이다.외부 결합(Outer Join)은 내부 결합과 함께 자주 사용되는 결합으로 결합(Join)하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블을 기준으로 모두 출력하는 결합 방법이다.
