My-SQL 도와주세요 내공 100

1. JOIN 은 크게 4가지로 구분하자면 Equijoin, Non-Equijoin, Outer-Join, Self-join 으로 나눌 수 있습니다.

각각에 대해 간단히 예를 들어 설명하자면 (예는 ANSI 문법을 적용하진 않았습니다.)

Equijoin

여러개의 TABLE 들로부터 정보를 검색하려면, SELECT 문장의 FROM 절에 TABLE명들을 적고
WHERE 절에 각 TABLE의 ROW들을 연결시킬 조건식을 기술한다.
각 TABLE 의 COLUMN명이 중복될 때는 반드시 COLUMN명 앞에 TABLE명을 붙여야 한다.
(중복되지 않을 때는 붙이지 않아도 되지만 명확성을 위해서나 ACCESS 를 위해서 붙이는 것이 좋다.)
N 개의 TABLE 을 JOIN 할 때는 최소한 N-1 개의 조건식이 필요하다.
복합 COLUMN 으로 JOIN 할 때는 더 많은 조건식이 필요하다.
2개 이상의 TABLE 에서 = 조건식을 만족시키는 ROW 들을 연결하여 검색한다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column명 ;
[ 예제 ]
S_EMP TABLE 과 S_DEPT TABLE 을 사용하여 사원들의 LAST_NAME, DEPT_ID,
NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID ;
 
특정 row의 join
JOIN 문장을 기술할 때 JOIN 조건식 이외에 다른 조건식을 AND 로 연결할 수 있다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명 AND condition ;
[ 예제 ]
S_EMP TABLE과 S_DEPT TABLE 을 사용하여 LAST_NAME 이 Menchu 인 사원의
LAST_NAME, DEPT_ID, NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_EMP.LAST_NAME = 'Smith' ;
 
Table alias
JOIN 문장에서 TABLE명이 긴 경우 TABLE명.COLUMN명 으로 적는 것이 매우 불편하다.
그런데 TABLE명 대신 ALIAS 를 사용하면 편하게 사용할 수 있다.
(SELECT 문장에서 TABLE명 대신 ALIAS 를 지정했다면 그 문장에서는 계속해서
ALIAS 로 사용하여야 한다.)
TABLE ALIAS를 사용하여 JOIN 문장을 간단하게 기술한다.
SELECT alias명.column명, alias명.column명
FROM table1명 alias1명, table2명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]
S_CUSTOMER TABLE과 S_REGION TABLE 을 사용하여 고객 명,지역번호,지역 명을 검색하시오.
단, COLUMN ALIAS 와 TABLE ALIAS 를 사용하시오.
SELECT C.NAME "Customer Name", C.REGION_ID "Region Id",
R.NAME "Region Name"
FROM S_CUSTOMER C, S_REGION R
WHERE C.REGION_ID = R.ID ;
 
Non-Equijoin

JOIN 문장에서 두 TABLE 을 JOIN 하는 조건식에 = OPERATOR 가 사용되지 않고
다른 OPERATOR 가 사용되는 것을 말한다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE 조인조건식 ;
[ 예제 ]
EMP TABLE 과 SALGRADE TABLE 을 사용하여 사원의 ENAME, JOB, SAL,GRADE를 검색하시오.
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
(BETWEEN OPERATOR 대신에 <= 와 >= 를 사용해도 되지만 BETWEEN 이 간편하다.)
 
Outer Join

두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다.
그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다.
(+) OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다.
JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다.
조건식을 만족시키지 못하는 데이타도 검색한다.
SELECT table명.column명, table명.column명
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명(+)
[ 예제 ]
S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME,
SALES_REP_ID, NAME 을 검색하시오.
단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오.

SELECT E.LAST_NAME, C.SALES_REP_ID,
C.NAME
FROM   S_EMP E, S_CUSTOMER C
WHERE  E.ID(+) = C.SALES_REP_ID ;  -->(이 경우는 ANSI 문법으로 RIGHT OUTER JOIN 에 해당합니다.)
 
Self Join

TABLE 의 ALIAS 를 사용하여, 마치 2 개의 TABLE 처럼 생각하여 자신의 TABLE 과 자신의 TABLE 을 JOIN 한다.
SELECT alias명.column명, alias명.column명...
FROM table명 alias1명, table명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]
S_EMP TABLE 에서 사원들의 LAST_NAME 과 그들의 상사 LAST_NAME 을  검색하시오.
SELECT W.LAST_NAME "Woker",
             M.LAST_NAME "Manager"
FROM   S_EMP W, S_EMP M
WHERE  W.MANAGER_ID = M.ID ;
 

* 문제에 대한 쿼리가 약간씩 틀려서 조금씩만 수정하였습니다. 보통 ANSI 문법에서 JOIN ON 을 사용하는 경우는 조인에 사용할 Column 명이 다를 경우입니다. 위와 같이 두 테이블 모두 EMP_ID 로 동일할 경우는 보통 JOIN USING 을 사용하시면 됩니다.^^

[답안]
2.1 (기존에 작성하신 쿼리는 EMP_ID 컬럼이 두 번씩 나오게됩니다.그래서 수정!)
select *
   from stu_mas mas
    join stu_add sa using (emp_id);
 
2.2 (성이 김씨인 학생을 구해야하므로 WHERE 절에 조건 추가했습니다. 다만 '김경만' 학생의 나이는 알 수 없으므로 결과는 나오지 않겠죠.)
select *
   from stu_mas
   join stu_add using (emp_id)
 where age between 20 and 29
   and name like '김%';

2.3 (alias 를 사용하셔서 mas.* 라고 하셨는데 이렇게 되면 조인을 맺은 결과테이블에서 가져오지 않게되죠. mas.* 라고 하시면 STU_MAS 테이블의 모든 Column 만을 가져오므로 그냥 * 로 수정!)
select *
  from stu_mas mas
  left outer join stu_add ae using (emp_id);

2.4 (작성하신 쿼리로도 결과는 같게 나옵니다. 조금 간단하게만 수정했습니다.) 
select *
  from stu_mas sm
 where emp_id not in (select emp_id
                                     from stu_add sa); 

2.5 (기존 쿼리 잘작성하셨네요. alias만 달았습니다.)             
select avg(age) AS '여학생 평균나이'
  from stu_add
 where sex='여';  

ㅋㅋ