-
ANSI JOIN(SQL-99 표준문법 조인)
DBMS 공통 표준 문법
JOIN ~ USING
SELECT E.EMPNO, E.ENAME, E.JOB, 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;
JOIN ~ ON : 기존 WHERE절 조인 조건식을 ON 옆에 작성
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO, D.DNAME, D.LOC FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE SAL > = 3000 ORDER BY E.DEPTNO, EMPNO;
OUTER JOIN : FROM 절에서 외부 조인 선언 (각각 LEFT, RIGHT, FULL ~ ON 으로 확인할 수 있다.)
SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO) ORDER BY E1.EMPNO; SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO) ORDER BY E1.EMPNO, MGR_EMPNO; SELECT E1.EMPNO, E1.ENAME, E1.MGR, E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO) ORDER BY E1.EMPNO;
OUTER JOIN : FROM 절에서 외부 조인 선언
*UNION 중복 제거 / UNION ALL 중복제거 X
'개발 > DB' 카테고리의 다른 글
DB_Oracle,SQL 그룹 함수(Group By), sum, count, min, max, avg (0) 2021.11.08 DB_Oracle,SQL 날짜형 함수, NULL 처리 함수 (0) 2021.11.08 DB_Oracle,SQL 조인, 등가조인, 비등가조인, 자체조인, 외부조인 (0) 2021.11.08 DB_Oracle,SQL Having, ROLLUP, CUBE, GROUPING SETS, LISTAGG ~ WITHIN GROUP, PIVOT, UNPIVOT (0) 2021.11.05 DB_Oracle 서브쿼리, 단일행/다중행 서브쿼리, 인라인 뷰, 스칼라 서브쿼리 (0) 2021.11.04 댓글 (비로그인 댓글 허용하지 않습니다.)