🔧 카티션 곱과 NATRUAL JOIN
🔧 카티션 곱
SELECT a,b
FROM A,B
WHERE A.dept_name = B.dept_name;
두 릴레이션의 모든 tuple들에 대한 조합을 가진 릴레이션을 생성한다.
🔧 Natural Join —> MySQL 의 NATURAL JOIN
카티션 곱과의 차이 : 두 릴레이션의 스키마에서 나타나는 {{ 공통속성 ] 의 값이 같은 Tuple의 짝만 } } 고려 한다.
결과 릴레이션에서는, { 하나의 속성 } 으로 통합된다.
즉, A relation에 name attribute가 존재, B relation에 name attribute가 존재했다면, 결과 릴레이션에 존재하는 name attribute는 하나다.( 두 개가 아니다 )
🔧 속성을 잘못 동일시 하는 경우가 존재한다.
아래의 예시에서는, 동일한 title을 가진 task와 alarm에 대한 join을 하는 것을 목표로 하였다.
하지만 좌측 릴레이션의 id,start 우측 릴레이션의 id,start은 [ 공통속성이 아님. ( 각각의 id,start를 이름이 같은 속성이라는 이유로 natural join시 , 이들을 동일시하는 결과가 발생한다 )
MySQL 에서 직접 실행해보자.
위의 그림보다 간단한 예시를 만들어보았다.
SELECT *
FROM facebook NATURAL JOIN linkedin;
아래와 같은 테이블이었다면
이런 결과가 나오게 될 것이다
이런 예시를 보니, JOIN 시에 “ 실제 공통속성이 속성에 대해서만 “ JOIN 되어야 한다는 것에 주의해야 할 필요성이 느껴진다. 하지만 NATURAL JOIN 에서는 해당 속성들로서 ‘같은 이름을 가진 속성’ 들이 자동으로 사용된다.
그렇다면 JOIN 기준이 되는 속성을 우리가 명시해 줄 수는 없을까??
이를 위해 USING, ON 이 존재한다.
🔧 join using , ON ..특정한 속성 (USING 과 ON 의 차이 )
using(특정 attribute) 를 하는 경우, 속성을 잘못 동일시 하는 상황을 피할 수 있다.
- USING 은 “두 테이블간 field 이름이 같은 경우”에만!! 사용 할 수 있다.
- ON 은 “join 하는 두 테이블의 컬럼들의 이름이 다를 경우” 에도 사용할 수 있다.
예시를 보자
create table task(
id bigint,
title varchar(50),
start datetime
);
create table alarm(
title varchar(50),
start datetime,
duration bigint
);
mysql> select * from alarm;
select * from task;
+-------+---------------------+----------+
| title | start | duration |
+-------+---------------------+----------+
| a | 2021-11-07 17:55:31 | 3000 |
| a | 2021-11-07 00:00:00 | 4000 |
+-------+---------------------+----------+
2 rows in set (0.00 sec)
+------+-------+---------------------+
| id | title | start |
+------+-------+---------------------+
| 1 | a | 2021-11-07 17:55:31 |
+------+-------+---------------------+
1 row in set (0.00 sec)
위와 같은 상황에서
select * from task natural join alarm;
+-------+---------------------+------+----------+
| title | start | id | duration |
+-------+---------------------+------+----------+
| a | 2021-11-07 17:55:31 | 1 | 3000 |
+-------+---------------------+------+----------+
1 row in set (0.00 sec)
select * from task join alarm using(title);
+-------+------+---------------------+---------------------+----------+
| title | id | start | start | duration |
+-------+------+---------------------+---------------------+----------+
| a | 1 | 2021-11-07 17:55:31 | 2021-11-07 17:55:31 | 3000 |
| a | 1 | 2021-11-07 17:55:31 | 2021-11-07 00:00:00 | 4000 |
+-------+------+---------------------+---------------------+----------+
2 rows in set (0.00 sec)
즉, natural join의 경우, id, title, start가 모두 같은 tuple들에 대한 join결과를 보여주며
아래의 경우, title이 같은 tuple들에 대한 join결과를 보여주고 있다.
이를 조금 더 식별가능한 attribute name을 갖도록 출력해보면
mysql> select id,title,task.start as task_start,alarm.start as alarm_start,duration from task join alarm using(title);
+------+-------+---------------------+---------------------+----------+
| id | title | task_start | alarm_start | duration |
+------+-------+---------------------+---------------------+----------+
| 1 | a | 2021-11-07 17:55:31 | 2021-11-07 17:55:31 | 3000 |
| 1 | a | 2021-11-07 17:55:31 | 2021-11-07 00:00:00 | 4000 |
+------+-------+---------------------+---------------------+----------+
2 rows in set (0.00 sec)
🔧 당연하지만 outer join 을 할 때에도, join 컬럼 을 명시 해 주어야 한다.
SELECT ....
FROM Post p LEFT OUTER JOIN likes l
ON p.seq = l.post_seq AND l.user_seq = ?
....
🔧 JOIN 에서 ON 과 WHERE 의 차이
나는 SQL 문제를 풀 때 , 어떤 필터링 조건이 존재한다면 이러한 필터링 조건은 WHERE 절에 기재하였다.
그런데 다른 분께서는 이를 JOIN 에 사용하는 ON 절 에다가 AND 를 통해 조건을 기재 한 것을 보았다.
LEFT OUTER JOIN likes l
ON p.seq = l.post_seq AND l.user_seq = ?
ON 에다가 검증 조건을 적어도 된다고??? 그럼 join 하는 상황에서는 ON 만을 사용해서 모든 조건을 기재하는 것도 가능도 하고 “그렇게 해도 되는걸까?”
🔧 ON 과 WHERE 의 차이 ( 예시 -> INNER JOIN )
실행 계획까지 직접 확인 해 보지는 않았다. 실행플랜은 사용중인 DBMS 마다 다를 수 있기에 직접 테스트 해 보는 것이 필요할 것 같다.
ON
SELECT o.*, review.*
FROM orders o
INNER JOIN review
ON review.orderId = o.Id AND review.userId = 3
- JOIN 에 사용하는 조건 절.
- 물론 JOIN 에서 도 WHERE 사용 가능 하다.
- 위에서 봤었지만 USING 과 달리, “컬럼 명이 서로 다르더라도” JOIN 의 조건절로 사용할 수 있다.
- p.seq = l.post_seq
- ON 조건 절에 WHERE 을 통해 나타내던 검색조건을 나타낼 수 있다.
- ON 에 나타낸 조건은
- JOIN 을 하기 전에 필터링 된다!! ⇒ ON 조건으로 필터링 된 레코들 간의 JOIN 이 이루어진다.
WHERE
SELECT o.*, review.*
FROM orders o
INNER JOIN review
ON review.orderId = o.Id
WHERE review.userId = 3
- ON 을 사용하는 것과 동일한 쿼리 실행 계획을 만들어낸다.
- JOIN 을 한 이후에 !! 필터링을 한다.
INNER JOIN 에서 ON, WHERE 의 차이? → 결론
사실상 여기서는 ON 을 사용하던 WHERE 을 사용하던 동일한 결과 를 만들어준다.
심지어 ON 에다가 명시하던 JOIN 조건 컬럼을 WHERE 에다가 만을 사용하여 아래와 같이 만들어주더라도
SELECT o.*, review.*
FROM orders o
INNER JOIN review
WHERE review.orderId = o.Id AND review.userId = 3
동일한 결과를 만들어 준다.
하지만 OUTER JOIN 에서 ON 을 통해 조건문을 추가할 때면 주의해야 한다
🔧 OUTER JOIN 에서 ON 절을 통한 필터링 은 예측하지 못한 결과를 낼 수도 있다
JOIN 에서 ON 과 WHERE 을 찾게 된 계기는 OUTER JOIN 에서 ON 절에 join 조건 외의 것도 나타내기도 하는 것을 본 것이었다.
그런데 찾다보니, OUTER JOIN 에서 ON 절에 join 조건 외의 필터링 조건을 기재하는 것은 예측하지 못한 결과를 낼 수도 있다고 한다.
아래와 같은 차이가 발생한다.
예시를 살펴보자
🔧 WHERE 절을 통해 필터링 하는 경우
상황
- facebook 테이블
- linkedin 테이블
SELECT *
FROM facebook
LEFT JOIN linkedin
ON facebook.name = linkedin.name
WHERE facebook.city = 'SF'
다음과 같은 과정을 거치게 되는 것이다
- LEFT JOIN
left table 인 facebook 의 모든 튜플들을 가져오고 ( 이게 left join 이니까 )
linkedin 에서는 join condition 을 만족하는 것들만을 가져와 join 한다 .
- 그리고 LEFT JOIN 을 한 결과에 ! WHERE 조건을 적용하여 필터링
🔧 ON 절을 통해 필터링 하는 경우
SELECT *
FROM facebook
LEFT JOIN linkedin
ON facebook.name = linkedin.name AND facebook.city = 'SF'
WHERE 절을 통해 필터링 하는 경우와 결과가 다르다.
- facebook 테이블의 모든 row 를 가져오고는
- linkedin 테이블에서는 join 컨디션 (facebook.name = linkedin.name 이면서 facebook.city = ‘SF’) 를 만족시키는 것들에 대해서만 join 이 일어나기 때문이다.
- 따라서 facebook.city = ‘SF” 가 아닌 다른 row 들에 대해서는 필터링이 일어나지 않는다.
OUTER JOIN 의 경우, 모든 데이터를 가져오는 테이블이 존재하는데, 여기에 더해 필터링이 일어나는 시점이 다르다 보니 이렇게 우리가 생각했던 조건을 모두 만족하지 않는 row들 까지 모두 출력이 되는 것 같다. ( 애초에 INNER JOIN 이었다 면 OUTER JOIN 처럼 한쪽 테이블의 모든 row 들 까지 다 가져오는 과정은 없기 때문에 이런 일이 일어나지 않았을 것이다 )
특히나 이렇게, 필터링 조건이 ‘OUTER JOIN 에서 모든 데이터를 가져오는 테이블’ 에 대한 필터링 조건이라면 이런 상황이 발생하게 될 것이다.
만약 TABLE_A LEFT OUTER JOIN TABLE_B 인 상황에서 TABLE_B 에 대한 필터링 조건이 주어졌다면 위와 같은 일은 일어나지 않게 된다 ( 당연함 )
SELECT *
FROM facebook
LEFT JOIN linkedin ON facebook.name = linkedin.name AND linkedin.city = 'SF';
결론
Join 을 할 때 ON, WHERE 둘 모두를 통해서 필터링 조건을 명시 할 수 있다.
하지만 OUTER JOIN 시에는 ON 을 사용할 경우 예상하지 못한 결과가 나올 수도 있다. 이는 ON 조건절이 적용되는 순서가 WHERE 절과 다르기 때문에 일어나는 결과다.
OUTER JOIN 에 대해 ON 을 사용하여 JOIN 이외의 조건을 명시하고 싶다면 '모든 레코드를 가져오는 테이블' 이 아닌!! 다른 테이블에 대한 조건을 명시 할 때에만 사용하는 것을 고려하는 것이 좋을 것 같다.
실행계획은 다를 수 있다.
앞서 말했듯이 실행계획은 어떤 DBMS 를 사용하고 버전은 무엇인가에 따라 다를 수 있다.
실행 계획에서도 joining 단계에서 필터링이 일어났다면 훨씬 빠를 것이고
필터링이 일어나기전에 full table 이 먼저 만들어졌다면 상대적으로 느릴 것이다.
참조
https://dataschool.com/how-to-teach-people-sql/difference-between-where-and-on-in-sql/
'DB' 카테고리의 다른 글
| SELECT DISTINCT 와 ORDER BY 가 함께 쓰이는 경우 정렬은 예상하지 못하게 일어난다 (0) | 2022.10.28 |
|---|---|
| Database - "status" column을 갖는 것에 관하여 (0) | 2022.02.27 |