[SQL]조인(JOIN)

/*JOIN*/

/* 일반적 표현 ,물리적으로 실제로 존재하는 테이블 */

SELECT * FROM EMP;
SELECT * FROM DEPT;

 

/*두 테이블을 곱*/
SELECT * FROM EMP,DEPT;
SELECT COUNT(*) FROM DEPT;

 

/* JOIN 문 (별칭도 가능) */
SELECT * FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

/* 등가 조인 */
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND SAL>=3000;

/* 비등가 조인 */

SELECT * FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

/*자체 조인*/
SELECT E1.EMPNO,E1.ENAME,E1.MGR,
E2.EMPNO,E2.ENAME,E2.MGR
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO;

 

/*JOIN USING */
SELECT E.EMPNO,E.ENAME,E.MGR,E.HIREDATE,E.SAL,E.COMM,
DEPTNO,D.DNAME,D.LOC
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL>=3000
ORDER BY DEPTNO,E.EMPNO;