SQL 바보인 나를 위해 글을 작성해보았다. (25년 4월 업데이트)
이 글을 처음 작성할 때는 LEFT OUTER JOIN 은 조건을 ON 과 WHERE 절 중 어디에 작성하는가에 따라 결과가 달라짐을 작성했었다.
그런데 이게 또.. 조건이 left table 에 대한 것이냐, right table 에 대한 것이냐에 따라 다르게 신경을 써야 하는 부분이 존재하여 이를 추가하였다.
물론 이는 그저 OUTER JOIN 에서 결과를 만들어내는 방식에 대한 이해가 있다면 괜찮은 부분이나, 나같이 머리가 빠르게 돌아가지 않는 사람들은 케이스별로 살펴보는게 도움이 될 것 같다
✅ 카티션 곱과 NATRUAL JOIN
JOIN 을 하기 전에 카티션 곱과 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 되어야 한다는 것에 주의해야 할 필요성이 느껴진다. ( 특히나 깊은 레거시 시스템 이라면 여러 팀에서 하나의 DB 를 사용하며, 테이블은 쪼개져 있을 수는 있다. 그런데 그 각 테이블들은 예전에 하나의 팀에서 개발하며 비슷한 이름 또는 동일한 이름이면서 다른 속성인 경우일 수 있다. 주의해야함 . 그 두 테이블의 그 두 컬럼은 정말로 동일한 속성인가?! )
하지만 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
);
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)
-- 알아서 title 과 start 두 컬럼이 모두 동일한 것들에 대해서만 join 이 일어남
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)
-- 명시한 title 컬럼에 이 동일한 row 들 끼리의 join즉, natural join의 경우, id, title, start가 모두 같은 tuple들에 대한 join결과를 보여주며
아래의 경우, title이 같은 tuple들에 대한 join결과를 보여주고 있다.
이를 조금 더 식별가능한 attribute name을 갖도록 출력해보면
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)
JOIN 에서 ON 과 WHERE 의 차이 1) Inner Join
나는 SQL 을 처음 시작 할 때는 어떤 필터링 조건이 존재하는 경우 거의 모두 WHERE 절에 해당 조건을 기재했었다.
그런데 필터링 조건은 WHERE 절 뿐만 아니라, JOIN 을 하는 경우 ON 절에도 기재가 가능하다.
그렇다면 WHERE 절 대신 JOIN 의 ON 절에다가 모두 사용하면 되는가????????
케바케다. 이에 대해 살펴보도록 하자
INNER JOIN 에 대한 ON 과 WHERE 의 차이
실행 계획까지 직접 확인 해 보지는 않았다. 실행플랜은 사용중인 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 을 통해 조건문을 추가할 때면 주의해야 한다
✅ JOIN 에서 ON 과 WHERE 의 차이 2) OUTER JOIN
outer join 은 inner join 때와 다르게 차이가 다소 존재한다
처음 이 글을 작성할 때는 left table 에 대한 조건인 케이스에 서만 차이가 있다고 단순하게 생각했었다.
작업을 하다 보니 right table 에 대한 조건인 경우에도 inner join 과 달리 where 절이냐 on 절이냐에 따라 결과가 달라진다. 어려운 내용은 아니나 생각을 하고 사용하도록 하자.
공통 상황
다음과 같은 DDL 로 테이블을 생성해보자 ( claude 짱 )
-- Facebook 테이블 생성
CREATE TABLE facebook_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL
);
-- LinkedIn 테이블 생성
CREATE TABLE linkedin_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL
);
-- Facebook 테이블에 데이터 삽입
INSERT INTO facebook_table (name, city) VALUES
('Matt', 'SF'),
('Lisa', 'NY'),
('Jeff', 'NY'),
('Dave', 'LA'),
('Matt', 'LA');
-- LinkedIn 테이블에 데이터 삽입
INSERT INTO linkedin_table (name, city) VALUES
('Matt', 'SF'),
('Dave', 'LA'),
('Matt', 'LA');
✔️ left table 에 대한 조건인 경우
WHERE 절
SELECT *
FROM facebook_table
LEFT JOIN linkedin_table
ON facebook_table.name = linkedin_table.name
WHERE facebook_table.city = 'SF'
✔️ JOIN
left table 인 facebook 의 모든 튜플들을 가져오고 ( 이게 left join 이니까 )
linkedin 에서는 join condition 을 만족하는 것들만을 가져와 join 한다 .
✔️ WHERE
그리고 LEFT JOIN 을 한 결과에 ! WHERE 조건을 적용하여 필터링
ON 절
SELECT *
FROM facebook_table
LEFT JOIN linkedin_table
ON facebook_table.name = linkedin_table.name
AND facebook_table.city = 'SF'
WHERE 절을 통해 필터링 하는 경우와 결과가 다르다.
“내가 무엇을 원하고 있는가” 에 따라 예상하지 못한 결과를 받는 것이 될 수도 있다.
JOIN ON 절은 그저 JOIN 에 대한 조건을 명시한 것이다.
- facebook 테이블의 모든 row 를 가져오고는
- linkedin 테이블에서는 join 컨디션 (facebook.name = linkedin.name 이면서 facebook.city = ‘SF’) 를 만족시키는 것들에 대해서만 join 이 일어난다.
- 따라서 left table 인 facebook 테이블에 대해서는 오히려 facebook.city = ‘SF” 가 아닌 다른 row 들에 대해서는 필터링이 일어나지 않는다.
따라서 다음과 같은 결과가 나온다
+----+------+------+------+------+------+
| id | name | city | id | name | city |
+----+------+------+------+------+------+
| 1 | Matt | SF | 3 | Matt | LA |
| 1 | Matt | SF | 1 | Matt | SF |
| 2 | Lisa | NY | NULL | NULL | NULL |
| 3 | Jeff | NY | NULL | NULL | NULL |
| 4 | Dave | LA | NULL | NULL | NULL |
| 5 | Matt | LA | NULL | NULL | NULL |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)
이로인해, facebook_table.city = 'SF' 가 아닌 row 들도 결과집합에 포함되어 있는 것이다.
애초에 INNER JOIN 이었다 면 OUTER JOIN 처럼 한쪽 테이블의 모든 row 들 까지 다 가져오는 과정은 없기 때문에 이런 일이 일어나지 않았을 것이다.
✔️ right table 에 대한 조건인 경우
역시, “내가 무엇을 원하고 있는가” 에 따라 예상하지 못한 결과를 받는 것이 될 수도 있다.
left table 때와 동일한 동작 방식에 의한 결과임에도 불구하고 ㅎㅎ 그렇다
예를들면, 페이스북에 있는 사용자라면 정보를 모두 받아오고 싶다. 그러면서 해당 사용자가 링크드인 에도 등록되어 있다면, 특정 조건을 만족하는 경우에만 받아오고 싶다고 하자. ( 뭔가 이상하긴 하지만 )
ON 절
SELECT * from facebook_table ft
LEFT OUTER JOIN linkedin_table lt
ON ft.name = lt.name
AND lt.city = 'SF'
;
앞에서 말한 것 처럼 left table 의 모든 row 를 가져오고는, linkedin 테이블에서는 조건을 만족 시키는 것들에 대해서만 join 이 일어난다.
+----+------+------+------+------+------+
| id | name | city | id | name | city |
+----+------+------+------+------+------+
| 1 | Matt | SF | 1 | Matt | SF |
| 2 | Lisa | NY | NULL | NULL | NULL |
| 3 | Jeff | NY | NULL | NULL | NULL |
| 4 | Dave | LA | NULL | NULL | NULL |
| 5 | Matt | LA | 1 | Matt | SF |
+----+------+------+------+------+------+
5 rows in set (0.01 sec)
WHERE 절
반면
SELECT * from facebook_table ft
LEFT OUTER JOIN linkedin_table lt
ON ft.name = lt.name
WHERE lt.city = 'SF';
의 경우 분명 JOIN 시점 까지는
+----+------+------+------+------+------+
| id | name | city | id | name | city |
+----+------+------+------+------+------+
| 1 | Matt | SF | 3 | Matt | LA |
| 1 | Matt | SF | 1 | Matt | SF |
| 2 | Lisa | NY | NULL | NULL | NULL |
| 3 | Jeff | NY | NULL | NULL | NULL |
| 4 | Dave | LA | 2 | Dave | LA |
| 5 | Matt | LA | 3 | Matt | LA |
| 5 | Matt | LA | 1 | Matt | SF |
+----+------+------+------+------+------+
였을 결과가 WHERE 절의 필터조건에 의해 필터링 되며 아래와 같은 결과가 나오게 된다.
+----+------+------+------+------+------+
| id | name | city | id | name | city |
+----+------+------+------+------+------+
| 1 | Matt | SF | 1 | Matt | SF |
| 5 | Matt | LA | 1 | Matt | SF |
+----+------+------+------+------+------+
2 rows in set (0.00 sec)
where 에 조건을 둘 경우에는 아예 이렇게 left outer join 으로 생성된 결과에서 필터링을 하는 것이 된다.
따라서 right table 에 대해서는 null 로 채워져 있던 행(left table 에 있는 데이터는 들어가 있는 )조차 걸러지게 된다. 만약 null 로 채워진 결과라도 받고 싶었던 경우라면 이게 예상하지 못한 결과이겠다. ㅎㅎ;
INNER JOIN 의 경우는 left table 의 데이터도 다 필터링 되어서 결과에서 볼 수 없는게 당연하고,
LEFt JOIN 은 right table 에는 없더라도 left table 에 있는 데이터라도 얻어야 하는 상황에서 주로 사용 하기 때문에 주의해야 할 것 같다.
결론
join 을 할 때 ON, WHERE 둘 모두를 통해서 필터링 조건을 명시 할 수 있다.
OUTER JOIN 의 경우 모든 row 를 가져오는 테이블 (left outer join 의 경우에는 left table) 이 존재한다.
LEFT OUTER JOIN 에서 left table 에 대한 조건을 on 절에 명시하더라도, 이는 join 에 대한 조건이기 때문에 left table row 는 모두 가져오게 된다. 반면 right table 에 대한 조건이라면, 조건을 만족하는 row 들 외에는 null 로 채워져 join 이 일어나게 된다.
WHERE 절은 결과에 대한 필터링을 하기 때문에 당연히 null 로 채워진 right table 에 서 끝나지 않고 row 자체를 결과에서 볼 수 없다.
실행계획은 다를 수 있다.
앞서 말했듯이 실행계획은 어떤 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 |