Introduce
본 문서는 2022년 3월 18일 에 작성되었습니다.
우리는 Velog - Unchaptered / Syntax | DDL Syntax 에서
SQL Theory 를 배우면서, Relationship 에 대해서 아주 간략하게만 설명했습니다.
RDBMS(관계형 데이터 베이스) 는 Relation 이 존재합니다.
따라서, 관계를 이용해 원하는 정보와 그 정보를 탐색하기 위한 언어 가 필요하며, 이를 관계형 대수라고 부릅니다.
Relation Algebra
Relation Algebra, 관계 대수는
관계를 이용해서 원하는 정보와 그 정보를 탐색하기 위한 언어 입니다.
| 이론적 개념 | Oracle 명령어 | 설명 |
|---|---|---|
| UNION | UNION UNION ALL | 합집합 출력 ( 교집합의 중복 제거 ) 합집합 출력 ( 교집합의 중복 무시 ) |
| INTERSECTION | INTERSECTION | 교집합 출력 |
| DIFFERENCE | MINUS | 차집합 출력 |
| PRODUCT | CROSS JOIN | a 개의 로우 데이터를 가진 테이블 A 와 b 개의 로우 데이터를 가진 테이블 B 의 a * b 길이의 집합 출력 |
| SELECT | WHERE | 로우에 대한 부분 집합 출력 |
| PROJECT | SELECT | 칼럼에 대한 부분 집합 출력 |
| NATRUAL JOIN | NATURAL JOIN INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN USING ON | 일반 조인 이너 조인 좌측 기준 아우터 조인 우측 기준 아우터 조인 양방향 아우터 조인 조건식 조건식 |
| DIVIDE | - |
UNION
UNION 은 별개의 데이터 집합을 취합하여 합집합 을 만드는 명령어입니다. ( 중복 부분[=교집합] 을 제거 )
SELECT 칼럼명 FROM 테이블명 WHERE 조건식
UNION
SELECT 칼럼명 FROM 테이블명 WHERE 조건식;UNION ALL
UNION ALL 은 별개의 데이터 집합을 취합하여 합집합 을 만드는 명령어입니다. ( 중복 부분[=교집합] 을 무시 )
SELECT 칼럼명 FROM 테이블명 WHERE 조건식
UNION ALL
SELECT 칼럼명 FROM 테이블명 WHERE 조건식;# UNION vs UNION ALL
RDBMS 에서 중복된 데이터를 확인하기 위해서 정렬이 필요합니다.
즉,
UNION 은 정렬의 과정 만큼 시간이 소요되므로
별도의 이유가 없다면 UNION ALL 을 사용하는 것이 유리합니다.
# UNION ALL 과 ORDER BY
ORDER BY 는 정렬 기능이 담긴 명령어입니다.
UNION 및 UNION ALL 으로 취합한 데이터의 결과를 보장하기 위해서는 정렬을 해야 합니다. 이러한 경우 전술한 ORDER BY 구문을 사용해야 합니다.
이 경우,
맨 마지막 SELECT 구문에 ORDER BY 를 적용해야 합니다.
INTERSECTION
INTERSACTION 은 별개의 데이터 집합을 취합하여 교집합 을 만드는 명령어입니다.
SELECT 칼럼명 FROM 테이블명 WHERE 조건식
INTERSECT
SELECT 칼럼명 FROM 테이블명 WHERE 조건식;MINUS
MINUS 는 별개의 데이터 집합을 취합하여 차집합 을 만드는 명령어입니다.
SELECT 칼럼명 FROM 테이블명 WHERE 조건식
MINUS
SELECT 칼럼명 FROM 테이블명 WHERE 조건식;# MINUS 사용 상의 이점
우리는 코딩 중,
어떠한 것이 아닌 결과 를 찾으려 할때 부정연산자를 사용했습니다.
그러나 RDBMS 의 부정연산자는 인덱스를 사용할 수 없습니다.
따라서 부정연산자 보다는 MINUS 연산자를 사용하는 것이 유리합니다.
-- 성능 열악
SELECT * FROM user WHERE created_date != SYSDATE
-- 성능 우위
SELECT * FROM user
MINUS
SELECT * FROM user WHERE created_date == SYSDATE;# MINUS 사용 상의 주의사항
MINUS 은 UNION 과 마찬가지로 중복을 제거하는 특징이 있다.
따라서 결과를 확실하게 보장할 수 있는 경우에만 사용해야 한다.
JOIN
Relation Algebra 안에는 JOIN (CROSS JOIN, NATURAL JOIN) 이 있었습니다.
이러한 JOIN 은,
복수의 테이블을 서로 연결 및 결합하여 출력하는 것을 의미합니다.
CROSS JOIN
CROSS JOIN 이란?
a 개의 로우 데이터를 가진 테이블 A 와
b 개의 로우 데이터를 가진 테이블 B 의
a * b 길이의 집합 출력
-- DDL
CREATE TABLE color (
color_id SERIAL PRIMARY KEY,
color_name TEXT
);
CREATE TABLE size (
size_id SERIAL PRIMARY KEY,
size_name TEXT
);
-- DML
INSERT INTO color (color_name)
VALUES ('red'),('blue'),('yellow');
INSERT INTO size (size_name)
VALUES ('small'),('middle'),('big');위와 같은 카테고리형 데이터가 있을 때
우리가 제공할 수 있는 모든 경우의 수 를 출력하기 위해서 CROSS JOIN을 사용할 수 있습니다.
이 경우 SQL 방식과 ANSI 방식을 사용할 수 있습니다.
-- SQL 방식
SELECT
ROWNUM as '구분',
color_name as '색상',
size_name as '사이즈'
FROM color, size;
-- ANSI 방식
SELECT
ROWNUM as '구분',
color_name as '색상',
size_name as '사이즈'
FROM color CROSS JOIN size;NATURAL JOIN
NATURAL JOIN 은 동일한 형태의 테이블을 합치는 명령어입니다.
Tistory - jiyongpark / NATURAL JOIN 이란?
-- DDL
CREATE TABLE user_jan (
user_id TEXT PRIMARY KEY,
user_name TEXT
);
CREATE TABLE user_feb (
user_id TEXT PRIMARY KEY,
user_name TEXT
);
-- 더미 데이터가 있다는 전제 하, DML
SELECT
ROWNUM as '구분',
user_name as '유저이름'
FROM user_jan NATURAL JOIN user_feb;INNER JOIN
INNER JOIN 은 서로 다른 테이블을 특정한 조건문에 일치되는 집합 을 출력하는 명령어입니다.
Tistory - royzero / INNER JOIN 이란?
-- DDL
CREATE TABLE user (
user_id TEXT PRIMARY KEY,
user_name TEXT
user_detailed_pk INT
FOREIGN KEY user_detailed (user detailed_pk)
);
CREATE TABLE user_detailed (
user_detailed_pk INT PRIMARY KEY,
user_detailed_tests TEXT
);
-- 더미 데이터가 있다는 전제 하, DML
SELECT * FROM user INNER JOIN user_detailed
조건명령어 조건식;위에서 조건명령어는 WHERE 혹은 ON 을 의미하며,
이에 대해서는 아래 ON 절을 참고해주세요.
USING 절
SQL 에서 JOIN 을 하려고 할 때,
해당 칼럼명이 완벽하게 일치하는 경우에 USING 을 사용할 수 있습니다.
INNER JOIN 에서 만든 user, user_detailed 테이블을 보면,
조인할 대상이 user_detailed_pk 임을 알 수 있고 이는 일치합니다.
즉, 다음과 같이 USING 절을 사용할 수 있습니다.
SELECT * FROM user JOIN user_detailed
USING (user_detailed_pk);ON 절
SQL 에서 JOIN 을 하려고 할 때,
해당 칼럼명이 다른 경우에 ON 을 사용할 수 있습니다.
-- DDL
CREATE TABLE user (
user_id TEXT PRIMARY KEY,
user_name TEXT
);
CREATE TABLE post (
post_id SERIAL PRIMARY KEY,
post_title TEXT NOT NULL,
post_text TEXT NOT NULL,
post_owner TEXT
FOREIGN KEY user (user_id)
);
-- DML
SELECT
ROWNUM as '구분',
post_title as '제목',
post_text as '내용',
user_name as '작성자'
FROM post JOIN user
ON post_owner = user_id;LEFT OUTER JOIN
OUTER JOIN 은 기준 테이블을 정하고 기준 테이블의 값을 전부 출력하면서, 느슨하게 연결된 테이블의 값이 있으면 JOIN 하고 없으면 null 로 출력합니다.
여기서는 좌측 테이블을 기준 으로 삼는 LEFT OUTER JOIN 으로 예시를 들겠습니다.
-- DDL
CREATE TABLE user (
user_id TEXT PRIMARY KEY,
user_pw TEXT NOT NULL,
user_detailed_pk INT
);
COMMENT ON user.user_detailed_pk IS '느슨한 연결'
CREATE TABLE user_detailed (
user_detailed_pk SERIAL PRIMARY KEY,
user_detailed_texts TEXT
);
-- DML
INSERT INTO user (user_id, user_pw, user_detailed_pk) VALUES
('tester1', 'testpassword1', null),
('tester2', 'testpassword2', 1),
('tester3', 'testpassword3', 2);
INSERT INTO user_detailed (user_detailed_tests) VALUES
('안녕하세요. 내 이름은 tester2 입니다만.'),
('삐빅- tester3 입니다.');위와 같이, user_detailed_pk 는 선택값으로 입력받습니다.
이러한 경우 외래키 참조를 하지 않고 느슨한 연결을 해도 됩니다.
즉,
유저 정보와 함께 자기소개를 결합 출력할 때,
tester1 의 유저의 정보가 누락되지 않아야 합니다.
이 경우, 다음과 같은 OUTER JOIN 을 사용할 수 있습니다.
-- SQL 방식
SELECT
user_id as '아이디',
user_detailed as '자기소개'
FROM user, user_detailed
ON user.user_detailed_pk = user_detail.user_detailed_pk (+);
-- ANSI 방식
SELECT
user_id as '아이디',
user_detailed as '자기소개'
FROM user LEFT OUTER JOIN user_detailed
ON user.user_detailed_pk = user_detailed.user_detailed_pk;예상되는 출력결과는 다음과 같습니다.
| 아이디 | 자기소개 |
|---|---|
| tester1 | null |
| tester2 | 안녕하세요. 내 이름은 tester2 입니다만. |
| tester3 | 삐빅- tester3 입니다. |
단, 이 경우 비교라는 이유로 WHERE 을 사용하게 되면 다음의 결과가 나옵니다.
| 아이디 | 자기소개 |
|---|---|
| tester2 | 안녕하세요. 내 이름은 tester2 입니다만. |
| tester3 | 삐빅- tester3 입니다. |
이에 대해서는 맨 마지막에 WHERE vs ON, USING 에서 보겠습니다.
RIGHT OUTER JOIN
LEFT OUTER JOIN 과 방향만 반대입니다.
FULL OUTER JOIN
FULL OUTER JOIN 은 말 그대로 합집합 을 만들어냅니다.
하지만, 해당 명령어의 주된 사용목적을 파악하지 못하였습니다.
SELECT * FROM 테이블 A FULL OUTER JOIN 테이블 B
ON 테이블 A.칼럼 a = 테이블 B.칼럼 b;WHERE vs USING, ON
다양한 관계형 대수 중에서도 JOIN 이라는 부분은
일반적으로 순수한 SQL 방식 과 ANSI 방식(키워드) 의 2가지 방법이 있다.
FULL OUTER JOIN 은 ANSI 방식만 지원된다.
그렇다면 의문이 생기는데,
WHERE 로 비교하는 것과 USING, ON 으로 비교하는 것이 무엇이 다른가? 이다.
앞서, USING과 ON 의 차이는
칼럼명의 일치여부에 따라서 선택하며, 기능적으론 동일하다 는 것을 알았다.
WHERE 과 USING, ON 의 차이는
USING, ON 은 JOIN 직전에 실행되고 WHERE 은 JOIN 이후에 실행된다 라는 점입니다.
이러한 특징을 잘 보여주는 예시가 Stackoverflow SQL JOIN - WHERE clause vs. ON clause 에 있다.
Document 테이블
| doc_id | doc_name |
|---|---|
| 1 | Document1 |
| 2 | Document2 |
| 3 | Document3 |
| 4 | Document4 |
| 5 | Document5 |
Download 테이블
| down_num | doc_id | down_username |
|---|---|---|
| 1 | 1 | 고구마 |
| 2 | 1 | 감자 |
| 3 | 2 | 고구마 |
| 4 | 2 | 맛탕 |
| 5 | 3 | 사탕 |
위와 같은 데이터 타입에서
Document 테이블을 기준 테이블로 잡고 다운로드 한 사람들의 리스트를 뽑고 싶다고 해보자. 이러한 경우 다음 SQL 문을 사용할 수 있을 것이다.
LEFT OUTER JOIN
SELECT
ROWNUM as '기준값',
documents.doc_id as '문서 번호',
documents.doc_name as '문서 제목',
download.down_num as '다운로드 번호',
download.down_username as '다운로드 유저명'
FROM documents LEFT OUTER JOIN download
ON documents.doc_id = download.doc_id;이러한 경우, Documents4 와 Documents5 는 다운로드 이력이 없으므로 아래와 같이 null 을 가질 것이다.
| 기준값 | 문서 번호 | 문서 제목 | 다운로드 번호 | 다운로드 유저명 |
|---|---|---|---|---|
| 1 | 1 | Documents1 | 1 | 고구마 |
| 2 | 1 | Documents1 | 2 | 감자 |
| 3 | 2 | Documents2 | 3 | 고구마 |
| 4 | 2 | Documents2 | 4 | 맛탕 |
| 5 | 3 | Documents3 | 5 | 맛탕 |
| 6 | 4 | Documents4 | null | null |
| 7 | 5 | Documents5 | null | null |
이제 우리는 다운로드 유저명을 기준으로 검색을 할 수 있고
이때 WHERE 절을 사용하여 다음과 같이 작성할 수 있다.
LEFT OUTER JOIN + WHERE
SELECT
ROWNUM as '기준값',
documents.doc_id as '문서 번호',
documents.doc_name as '문서 제목',
download.down_num as '다운로드 번호',
download.down_username as '다운로드 유저명'
FROM documents LEFT OUTER JOIN download
ON documents.doc_id = download.doc_id
WHERE download.down_username = '고구마';이 경우 다음의 값을 얻게 된다.
| 기준값 | 문서 번호 | 문서 제목 | 다운로드 번호 | 다운로드 유저명 |
|---|---|---|---|---|
| 1 | 1 | Documents1 | 1 | 고구마 |
| 2 | 2 | Documents2 | 3 | 고구마 |
LEFT OUTER JOIN + ON
그렇다면 ON 을 통해서 비교하면 어떻게 될까?
SELECT
ROWNUM as '기준값',
documents.doc_id as '문서 번호',
documents.doc_name as '문서 제목',
download.down_num as '다운로드 번호',
download.down_username as '다운로드 유저명'
FROM documents LEFT OUTER JOIN download
ON documents.doc_id = download.doc_id
AND download.down_username = '고구마';이 경우 다음의 값을 얻게 된다.
| 기준값 | 문서 번호 | 문서 제목 | 다운로드 번호 | 다운로드 유저명 |
|---|---|---|---|---|
| 1 | 1 | Documents1 | 1 | 고구마 |
| 2 | 2 | Documents2 | 3 | 고구마 |
| 3 | 3 | Documents3 | null | null |
| 4 | 4 | Documents4 | null | null |
| 5 | 5 | Documents5 | null | null |
왜 이런 결과가 나오는 것일까?
이는 WHERE 과 ON 의 실행 시점의 차이 와
LEFT OUTER JOIN 이 가지고 있는 기준 테이블을 모두 출력하려는 특징 이 결합되어 생기는 현상이다.
따라서,
무엇이 없다고는 할 수 없지만,
원하는 결과값의 형태에 따라서 WHERE 과 ON 을 고민할 필요가 있다.
