오라클 SQL의 OUTER JOIN과 MySQL의 OUTER 조인 구문이 다른데요. MySQL은 ANSI SQL을 사용하기 때문입니다. 결론부터 말하면 구문은 달라도 기본적으로 동일한 결과를 만들어 낼 수 있습니다. 하지만, 그 표현 방식에 있어서 차이가 있는데요. 잘 못 사용하면 전혀 다른 결과가 나오니 마지막까지 읽어봐 주세요.
Oracle과 MySQL의 구문 차이
1. 오라클 SQL
오라클에서 OUTER JOIN을 사용하려면, ANSI 표준이 아닌 오라클 고유의 구문을 사용했습니다. 오라클에서는 (+) 기호를 사용하여 OUTER JOIN을 표현합니다.
| select * from usr a, usr_opt b where a.usr_id = b.usr_id(+) ; |
위 예제에서 b.usr_id(+)는 usr 테이블이 기준 테이블임을 의미합니다. 이 구문은 usr 테이블을 기준으로 데이터를 모두 가져오고, usr_opt 테이블의 대응하는 값이 없으면 NULL로 반환합니다.
2. ANSI SQL
ANSI SQL 표준에서는 OUTER JOIN을 명시적으로 작성하기 위해 LEFT JOIN, RIGHT JOIN, FULL JOIN 등의 구문을 사용합니다. 이는 SQL 표준에 따른 방법으로, 가독성이 높고 이해하기 쉬운 구문입니다.
| select * from usr a left join usr_opt b on a.usr_id = b.usr_id ; |
위 예제는 usr 테이블의 모든 데이터를 가져오고, B 테이블에 매칭되는 데이터가 없으면 NULL로 채워지는 형태입니다. 이 구문은 LEFT OUTER JOIN을 의미합니다. 여기서 LEFT JOIN(=LEFT OUTER JOIN) 같은 표현입니다.
실수하면 안 되는 ANSI OUTER 방식
ANSI 아웃터 조인 방식은 where절에 있는지 on에 있는지에 따라 원하는 결과가 다릅니다. 이제부터 그 차이점을 알아보도록 하겠습니다. 우선 테스트를 위해 임시 테이블을 만들겠습니다. information_schema를 통해 만드는 스크립트이니 한번 따라 해 보세요.
| -- usr 테이블 생성 create table usr as select @U_NUM := @U_NUM+ 1 as usr_id from (select @U_NUM := 0 from information_schema.tables ) a limit 6 ; -- usr_opt 테이블 생성 create table usr_opt as select @U_NUM := @U_NUM+ 1 as usr_id , @O_NUM := @O_NUM+ 1 as opt_id , 'OFF' opt_st from (select @U_NUM := 3 , @O_NUM := 0 from information_schema.tables ) a limit 6 ; -- opt_id가 짝수인 경우만 opt_st를 ON으로 변경 update usr_opt set opt_st = 'ON' where mod(opt_id,2) = 0 ; |
1. 테이블 구조 확인
select * from usr;
| usr_id |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
select * from usr_opt;
| usr_id | opt_id | opt_st |
| 4 | 1 | OFF |
| 5 | 2 | ON |
| 6 | 3 | OFF |
| 7 | 4 | ON |
| 8 | 5 | OFF |
| 9 | 6 | ON |
2. 정상적 조인 확인
두 테이블을 usr_id를 1,2,3은 usr에만 있고, 4,5,6는 공통집합이고, 7,8,9는 usr_opt만 있게 만들었습니다. inner join을 하면 4,5,6만 조인되겠죠.
select *
from usr a inner join usr_opt b
on a.usr_id = b.usr_id
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 4 | 4 | 1 | OFF |
| 5 | 5 | 2 | ON |
| 6 | 6 | 3 | OFF |
이제 usr을 기준으로 LEFT JOIN을 해보겠습니다. 아래와 같이 나오네요.
select *
from usr a left outer join usr_opt b
on a.usr_id = b.usr_id
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 1 | |||
| 2 | |||
| 3 | |||
| 4 | 4 | 1 | OFF |
| 5 | 5 | 2 | ON |
| 6 | 6 | 3 | OFF |
3. RI 검증 SQL 확인
| ANSI | Oracle |
| select * from mig.usr a left outer join mig.usr_opt b on a.usr_id = b.usr_id where b.usr_id is null ; |
select * from usr a, usr_opt b where a.usr_id = b.usr_id(+) and b.usr_id is null ; |
결과는 아래와 같이 usr_id가 1,2,3이 나옵니다. usr에는 있는데, usr_opt에는 없는 경우를 찾을 때 사용하는 RI를 체크하는 SQL 입니다.
| a.usr_id | b.usr_id | opt_id | opt_st |
| 1 | |||
| 2 | |||
| 3 |
이번에는 ANSI SQL에서 on절에 and b.usr_id is null로 넣어 보겠습니다. 그러면 usr 테이블의 모든 데이터가 다 나와버립니다. 우리가 원하는 결과가 아닙니다.
select *
from mig.usr a left outer join mig.usr_opt b
on (a.usr_id = b.usr_id and b.usr_id is null)
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 1 | |||
| 2 | |||
| 3 | |||
| 4 | |||
| 5 | |||
| 6 |
4. ON절과 WHERE절 차이점 분석
ANSI SQL의 on절과 where절 차이를 알아보기 위해서 아래 테스트를 진행했습니다. 우선 일반적은 RIGHT JOIN을 해봤습니다.
select *
from mig.usr a right outer join mig.usr_opt b
on a.usr_id = b.usr_id
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 4 | 4 | 1 | OFF |
| 5 | 5 | 2 | ON |
| 6 | 6 | 3 | OFF |
| 7 | 4 | ON | |
| 8 | 5 | OFF | |
| 9 | 6 | ON |
역시 예상데로 정상적으로 나옵니다. usr_opt을 기준으로 모든 데이터가 나왔고, usr는 없는 7,89 데이터는 null로 채워졌네요. 이번에는 on절에 opt_st = 'ON' 라는 조건을 넣어 보겠습니다. 내가 원한 건 usr_opt의 opt_st = 'ON' 집합만 가지고 outer를 하길 원했는데 전혀 다른 결과가 나왔네요.
select *
from mig.usr a right outer join mig.usr_opt b
on (a.usr_id = b.usr_id and opt_st = 'ON')
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 4 | 1 | OFF | |
| 5 | 5 | 2 | ON |
| 6 | 3 | OFF | |
| 7 | 4 | ON | |
| 8 | 5 | OFF | |
| 9 | 6 | ON |
그렇다면 어떻게 구문을 변경해야 내가 원하는 구문이 될까요? 바로 where절에 opt_st = 'ON'을 넣어주면 됩니다. 오른쪽 오라클 구문과 동일한 결과를 나옵니다.
| ANSI | Oracle |
| select * from mig.usr a right outer join mig.usr_opt b on a.usr_id = b.usr_id where opt_st = 'ON' ; |
select * from usr a, usr_opt b where a.usr_id = b.usr_id(+) and b.usr_id(+) = 'ON' ; |
아래는 수행 결과입니다. 이제는 원하던데로 usr_opt의 opt_st가 ON인 집합으로 Outer 조인을 했네요.
| a.usr_id | b.usr_id | opt_id | opt_st |
| 5 | 5 | 2 | ON |
| 7 | 4 | ON | |
| 9 | 6 | ON |
5. 테스트 결론
ANSI SQL의 where에 조건을 넣으면 Fiter 집합으로 Outer 조인을 진행합니다. 하지만 on절에서 조건을 넣으면 집합은 그대로인데 outer join할 때에 처리합니다. 마지막으로 정확한 확인을 위해 where절에 opt_st = 'ON' and opt_id in(2,4) 넣어 보았습니다.
select *
from mig.usr a right outer join mig.usr_opt b
on a.usr_id = b.usr_id
where opt_st = 'ON' and opt_id in(2,4)
;
| a.usr_id | b.usr_id | opt_id | opt_st |
| 5 | 5 | 2 | ON |
| 7 | 4 | ON |
두 가지만 기억합시다.
- ON절은 조인에 참여하는 컬럼의 조건이다
- WHER절은 집합의 Filter조건이다.
마치며
오라클에서는 (+) 기호를 사용하여 JOIN을 표현하지만, ANSI SQL에서는 LEFT JOIN, RIGHT JOIN과 같은 명시적인 구문을 사용합니다.
ANSI SQL은 표준화된 SQL 구문이므로 대부분의 데이터베이스에서 동일하게 사용될 수 있는 장점이 있습니다. 반면, 오라클의 (+) 구문은 오라클 고유의 방식이어서 다른 DBMS에서는 사용할 수 없습니다.
가독성 측면은 오라클 (+) 기호에 익숙하신 사용자라면 오라클이 편하다고 이야기 할 수 있으나, ANSI SQL 구문이 더 직관적이고 널리 통용되는 측면은 있습니다. 대신 on과 where절에 쓰임새를 잘 알고 사용하시기 바랍니다.
조인(Join) 종류(2) - Inner 조인 vs Outer 조인
[SQLD] 조인(Join) 종류(2) - Inner 조인 vs Outer 조인
이번 포스팅은 SQL 조인의 두번째로 Inner 조인과 Outer 조인에 대해 어떻게 다른지 알아보도록 하겠습니다. Inner 조인은 쉬운데 Outer 조인은 어려워하는 경우가 많습니다. 이번에 Outer 조인을 어떻게
bommbom.tistory.com
'데이터베이스' 카테고리의 다른 글
| 데이터 이행 계획, 체크리스트 꼭 준비해야 하는 이유? (2) | 2024.09.03 |
|---|---|
| MySQL 내장 함수 99개 총정리 (0) | 2024.08.28 |
| MySQL 권한 종류 및 부여, 확인, 취소 방법 (0) | 2024.08.23 |
| 데이터 이행 검증 방법, 정합성, 무결성을 어떻게 보장할까? (0) | 2024.08.20 |
| MySQL 실행 계획의 "Extra" 필드로 튜닝 방법 (0) | 2024.08.14 |
