SQLD 4-1 ] 표준 조인
❏ 표준 조인 (STANDARD JOIN)
- 사용자는 테이블간의 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있다
- 대표적인 ANSI/ISO 표준 SQL 의 기능
1. STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
2. SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들
3. ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
4. WINDOW FUNCTION 같은 새로운 개념의 분석 기능들
뭐. 이런 기능들이 요즘 표준으로 기준된 SQL 의 기능들인가 보다.
이게 시험에 나왔다고 한다...;; 이걸 왜..
1970년 : Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문 발표
1974년 : IBM SQL 개발
1979년 : Oracle 상용 DBMS 발표
1980년 : Sybase SQL Server 발표 (이후 Sybase ASE로 개명)
1983년 : IBM DB2 발표
1986년 : ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)
1992년 : ANSI/ISO SQL 표준 개정 (SQL-92, SQL2)
1993년 : MS SQL Server 발표 (Windows OS, Sybase Code 활용)
1999년 : ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)
2003년 : ANSI/ISO SQL 표준 개정 (SQL-2003)
2008년 : ANSI/ISO SQL 표준 개정 (SQL-2008)
SQL1-1986, SQL2-1992, SQL3-1999
1> 일반 집합 연산자
E.F.COOD 박사가 발표한 8가지 관계형 대수가 있다.
4개의 일반 집합 연산자와 4개의 순수 관계 연산자로 나눌수 있다고 한다.
일반 집합 연산자 저 4개를 SQL 로 맞춰보자면
집합연산자 -> SQL
1. Union -> UNION
2. Intersection -> INTERSECT
3. Difference -> EXCEPT(oracle 은 MINUS)
4. Product -> CROSS JOIN
기능으로 구현되있다. 그림만 봐도 알것같다. 1.합집합 2.교집합 3.차집합 4.곱집합(모든데이터의 조합)
2> 순수 관계 연산자
그 8개 중 나머지 4개 순수 관계 연산자들이다.
순수관계연산자 -> SQL
5. Select -> WHERE
6. Project -> SELECT
7. (Natural) Join -> 여러가지 JOIN 들 (inner join, outer join, ... 등 )
8. Divide -> 사용 안함.
으로 비교할 수 있다.
1. Union -> UNION
2. Intersection -> INTERSECT
3. Difference -> EXCEPT(oracle 은 MINUS)
4. Product -> CROSS JOIN
5. Select -> WHERE
6. Project -> SELECT
7. (Natural) Join -> 여러가지 JOIN 들 (inner join, outer join, ... 등 )
8. Divide -> 사용 안함.
UNION / INTERSECT / MINUS(EXCEPT) / CROSS JOIN / WHERE / SELECT / 여러가지 JOIN
❏ FROM 절 JOIN 형태
- ANSI/ISO SQL 에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
이렇게나 많은 JOIN 방식이 있다니.
ANSI/ISO SQL 에서 규정한 JOIN 은 이렇다
'원래 where절 에서 join 쓰고 했었는데, 그렇게 쓸사람은 쓰고
추가된 선택 기능들.. 위의 목록들 제공할테니까 from 절에서 쓸수있으니까 쓸사람은 써보세요...!'
지금까지는 아직 where 절에서 쓰는게 편한것 같지만..
한번 다 알아보긴 해야지.
하나씩 알아봅시다!
❏ INNER JOIN
- INNER JOIN (내부) <--> OUTER JOIN (외부)
- JOIN 조건에서 동일한 값이 있는 행만 반환한다
기본 where 절에서 사용하는 join 조건.. (이번 예로만 들고 이제부턴 where 절에 join 문 안쓸꺼야)
select EMP.DEPTNO, EMPNO, ENAME, DNAME from EMP, DEPT where EMP.DEPT_NO = DEPT.DEPT_NO; |
원래 저렇게 사용했는데
INNER JOIN 을 쓰면
select EMP.DEPTNO, EMPNO, ENAME, DNAME from EMP INNER JOIN DEPT on EMP.DEPT_NO = DEPT.DEPT_NO; |
이렇게 쓴다. INNER JOIN 은 JOIN 중에 기본중에 기본이니 생략 하고 (INNER을..)
그냥 from EMP JOIN DEPT 이렇게만 써도 INNER 인줄 안다.
JOIN 조건에서 동일한 값이 있는 행만 반환한다는건
괜히 말이 좀 어렵게 동일한 값 어쩌고 써놨지
그냥 테이블 두개 조인해서 on 조건에 맞는 행들로 한 테이블 만든다는 말.
❏ NATURAL JOIN
- 두 테이블 간의 동일한 이름을 갖는 "모든" 칼럼들에 대해 EQUI(=) JOIN 을 수행한다
- NATURAL JOIN 이라고 쓴다고 하면. using 조건절, on 조건절, where 절에서 join 조건들을 정의할 수 없다.
- SQL SERVER 에서는 지원 안한다.
select DEPTNO, EMPNO, ENAME, DNAME from EMP NATURAL JOIN DEPT; |
이게 끝..?
자연 조인이라...
EMP 테이블이랑 DEPT 테이블을 보면
DEPTNO 로 공통된 이름의 칼럼이 있다. 자동으로 공통된 이름의 칼럼을 인식해서 JOIN 을 처리한것! (물론 내용도 두 칼럼 다 완벽히같아야하지)
편하네.
대신 같은 데이터 타입이여야 하고, ALIAS 별명이나 테이블명 과 같은 접두사(EMP.DEPTNO) 이런거. 쓰면 안된다.
자연 조인 그대로 순수하게 냅둬야한다
select * from EMP NATURAL JOIN DEPT; |
이렇게 다 출력해주세요!
하면. NATURAL JOIN 의 기준이 되는 DEPTNO 이 칼럼이 제일 맨 앞에 온다. (첫번째 칼럼이 됨, 그리고 DEPTNO 은 한번만나온다)
그리고 EMP 테이블 칼럼 순서대로 다 나열했으면 이제 DEPT 칼럼 순서대로.
반면,
INNER JOIN 은 첫번째 테이블, 두번째 테이블의 칼럼 순서대로 출력된다.
그리고 NATURAL JOIN 은 DEPTNO 한번만 나왔는데 INNER JOIN 은 별개의 칼럼으로 표시한다.
❏ USING 조건절
- 내가 하나 원하는 조인 칼럼 찝겠다!
- SQL SERVER 에서는 지원 안합니다
select * from DEPT JOIN DEPT_TEMP USING (DEPTNO); |
이렇게 DEPT 랑 DEPT_TEMP 랑 (INNER) JOIN 하는데 기준칼럼을 DEPTNO 로 할래요!
dept_temp 테이블은 dept 테이블 복사한 테이블. (내용만 dname 안에 2개만 바꿈)
INNER JOIN 처럼 두개 테이블 모두 나열하긴 하는데.. 다른점이
NATURAL JOIN 처럼 사용된 칼럼의 이름을 하나로 처리하네. DEPTNO 가 한번밖에 안나온다
❏ ON 조건절
- 기본적. from 절에 따라오는 on 조건절 이고, 두 테이블간 칼럼명이 다르더라도 JOIN 할 수 있다.
(1) 가장 기본적
(2) from ~ on 기본적 구조에 where 조건 추가
where 조건절을 사용했다.
(3) from ~ on ~ and ..
ON 조건절에 and 를 쓸수있다. 조건이긴 조건인데,, 검색이 목적이면 where 에서 쓰는것을 권장합니다.
(4) 여러가지 ON 조건절 예제
select TEAM_NAME, TEAM.STAIUM_ID, STADIUM_NAME from TEAM JOIN STADIUM on TEAM.STADIUM_ID = STADIUM.STADIUM_ID order by STADIUM_ID; |
기본적인 from ~ on
근데, select 에서 테이블명.칼럼명 이 아니라 그냥 칼럼명 한게 있네 TEAM_NAME, STADIUM_NAME.
자동으로 잡아오는건가? 하긴 중복된게 없긴 한데.. (team_name 은 team테이블에 하나밖에 없고 stadium_name 도 stadium 밖에 없다)
* 고유 이름의 칼럼일 경우 저렇게 단독으로 칼럼명 사용 가능
select TEAM_NAME, TEAM.STAIUM_ID, STADIUM_NAME from TEAM JOIN STADIUM using (STADIUM_ID) order by STADIUM_ID; |
로도 쓸 수 있다. 공통된 STADIUM_ID 로 사용한다는 using
또.. 고전 방식인 where 쓰는것.
select TEAM_NAME, TEAM.STAIUM_ID, STADIUM_NAME from TEAM, STADIUM where TEAM.STADIUM_ID = STADIUM.STADIUM_ID order by STADIUM_ID; |
(5) 다중 테이블 JOIN
이렇게 하나 조인 시켜놓고 그 결과에 join ~ on 을 하나 더시킨다
고전적인 where 방식으로 하면 이렇다
select E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME as NEW_DNAME from EMP E, DEPT D, DEPT_TEMP T where E.DEPTNO = D.DEPTNO and E.DEPTNO = T.DEPTNO; |
계속 where 방식이 고전적이라고 하니.. from on 을 주로 쓰는 join 기법으로 해야겠다....
홈팀이 3점 이상 차이로 승리한 경기의 경기장 이름, 경기 일정, 홈팀 이름, 원정팀 이름 출력해보기
table : SCHEDULE, STADIUM, TEAM
column : STADIUM_NAME, STADIUM_ID, SCHE_DATE, TEAM_NAME(HOME), TEAM_NAME(AWAY), HOME_SCORE, AWAY_SCORE
똑같은 TEAM 을 별명을 다르게 해서 조인 시켰네. 홈 팀네임이랑 어웨이 팀네임 따로따로 조인시켰네.
❏ CROSS JOIN
- 맨 처음 일반집합 연산자 말할때 4번 PRODUCT 의 개념으로.
테이블간 JOIN 각이 안나온다... 그냥 모든 데이터 조합시켜버리자!!
그냥 테이블 CROSS JOIN 테이블 하면 M * N 건의 조합이 나온다.
NATURAL JOIN 은 where 조건이나 별명 이런거 아무것도 건드릴 수 없지만
CROSS JOIN 의 경우 where 절을 추가로 쓸 수 있다. 그러면 CROSS JOIN 쓰는 의미가 없으므로.. 추천하지는 않는다
❏ OUTER JOIN
- INNER JOIN 의 반대. 외부 JOIN.. 무슨뜻일까? JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다 ?
TABLE1 이 TABLE 2 를 JOIN 할때
JOIN 할께 마땅히 없다.. 모든값이 같은 칼럼이 없어!! JOIN 은 하고싶은데 ...
이럴때 OUTER JOIN 을 사용하여 해결이 가능하다고 합니다
방법이 3가지가 있습니다. LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN
하나하나씩 알아봅시다
1. LEFT OUTER JOIN
- 예를들어.. 테이블 A와 B가 있을 때, LEFT OUTER JOIN 시키면.. (left 니까 좌측. 좌측 테이블을 먼저 읽는다.)
A left outer join B 면 A 테이블부터 훑어본다. 훑어보고 B의 테이블에 가서 on 조건에서 명시한 칼럼에서
같은 값이 있을 때에는 갖고오고, 같은 값이 없을 때에는 NULL 로 채운다.
from stadium left (outer) join team
OUTER 는 생략 가능. left join 만 써도 된다.
INNER JOIN 와 다르게 HOMETEAM_ID 랑 TEAM_NAME 이 공백으로 추가로 갖고왔다.
2. RIGHT OUTER JOIN
- 반대로. 우측 테이블이 기준이다. 기준.
A RIGHT OUTER JOIN B 하면 기준이 아까 LEFT 일때는 A 테이블이 기준!! 이였는데 지금은 B 가 기준이다.
마찬가지로 RIGHT JOIN 만 써도 된다. A RIGHT (OUTER) JOIN B
이렇게 기준이 되는 테이블 (DEPT) 에는 DEPTNO 가 10, 20, 30, 40 이 있는데 당하는 (EMP) 테이블에선
안타깝게도 DEPTNO 이 40이 해당하는게 없다.. ㅠㅠ 그렇지만 OUTER JOIN 을 썼기 때문에 공백으로라도 넣어야한다!!
3. FULL OUTER JOIN
- 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다
즉, A 와 B 가 모두 기준이 된다. RIGHT OUTER JOIN 과 LEFT OUTER JOIN 의 결과를 합집합으로 처리한것과 같다
단, UNION ALL 이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다. (다음절에서 설명)
이것도 FULL (OUTER) JOIN 으로 OUTER 생략가능
이런 DEPT_TEMP 과 DEPT 테이블이 있다.
INNER JOIN 같으면 동일한 30, 40 이 2개 행만 출력되었을텐데
FULL OUTER JOIN 으로 DEPT 테이블에만 있는 10, 20 내용과 DEPT_TEMP 에 있는 50, 60 의 내용도 같이 출력됬다.
❏ INNER vs OUTER vs CROSS JOIN
