MSSQL JOIN 연습하기위해 예제를 만들었습니다.
인터넷에 나와있는 JOIN 연습문중에 대부분 컬럼이 유니크키로 되어 연습에 제한적이기에
EMP_LOG라는것을 추가하여 좀 더 JOIN을 연습하고자 합니다.
-- TEST DATA--
CREATE TABLE EMPLOYEE (
EMP_NO INT PRIMARY KEY ,
EMP_NAME VARCHAR(32) NOT NULL,
EMP_DEPT INT FOREIGN KEY REFERENCES DEPT(DEPT_NO)
)
CREATE TABLE DEPT(
DEPT_NO INT PRIMARY KEY,
DEPT_NAME VARCHAR(50) NOT NULL
)
CREATE TABLE EMP_LOG(
LOG_NO INT PRIMARY KEY,
EMP_NO INT FOREIGN KEY REFERENCES EMPLOYEE(EMP_NO),
L_LOG VARCHAR(200)
)
INSERT INTO DEPT VALUES(1,'연구소')
INSERT INTO DEPT VALUES(2,'경영지원')
INSERT INTO DEPT VALUES(3,'고객지원')
INSERT INTO DEPT VALUES(4,'전략사업부')
INSERT INTO EMPLOYEE VALUES(1,'최호영',1)
INSERT INTO EMPLOYEE VALUES(2,'김문수',2)
INSERT INTO EMPLOYEE VALUES(3,'이정현',3)
INSERT INTO EMPLOYEE VALUES(4,'사마의',1)
INSERT INTO EMPLOYEE VALUES(5,'리동욱',2)
INSERT INTO EMPLOYEE VALUES(6,'김정서',3)
INSERT INTO EMPLOYEE VALUES(7,'희민정',1)
INSERT INTO EMPLOYEE VALUES(8,'김환욱',2)
INSERT INTO EMPLOYEE VALUES(9,'이만우',3)
INSERT INTO EMPLOYEE VALUES(10,'지형서',1)
INSERT INTO EMPLOYEE VALUES(11,'우나라',2)
INSERT INTO EMPLOYEE VALUES(12,'김형욱',NULL)
INSERT INTO EMPLOYEE VALUES(13,'세나개',NULL)
INSERT INTO EMP_LOG VALUES(1,3,'반갑습니다')
INSERT INTO EMP_LOG VALUES(2,2,'인사업무기록')
INSERT INTO EMP_LOG VALUES(3,1,'랜덤')
INSERT INTO EMP_LOG VALUES(4,2,'음…')
INSERT INTO EMP_LOG VALUES(5,2,'TEST_DB구축')
INSERT INTO EMP_LOG VALUES(6,5,'반갑습니다')
INSERT INTO EMP_LOG VALUES(7,1,'인사업무기록')
INSERT INTO EMP_LOG VALUES(8,2,'랜덤')
INSERT INTO EMP_LOG VALUES(9,5,'음…')
INSERT INTO EMP_LOG VALUES(10,1,'TEST_DB구축')
INSERT INTO EMP_LOG VALUES(11,5,'반갑습니다')
INSERT INTO EMP_LOG VALUES(12,6,'인사업무기록')
INSERT INTO EMP_LOG VALUES(13,1,'랜덤')
INSERT INTO EMP_LOG VALUES(14,5,'음…')
INSERT INTO EMP_LOG VALUES(15,2,'반갑습니다')
INSERT INTO EMP_LOG VALUES(16,1,'인사업무기록')
INSERT INTO EMP_LOG VALUES(17,2,'랜덤')
INSERT INTO EMP_LOG VALUES(18,5,'음…')
INSERT INTO EMP_LOG VALUES(19,1,'TEST_DB구축')
각 테이블 사진 :
EMPLOYEE TABLE
DPET TABLE
EMP_LOG TABLE
JOIN, INNER JOIN, LEFT JOIN , RIGHT JOIN , OUTER JOIN,FULL OUTER JOIN 순 진행
-
EMP , DEPT
-
EMP , EMP_LOG
- EMP , DEPT 결과
JOIN :
SELECT *
FROM EMPLOYEE E
JOIN DEPT D ON E.EMP_DEPT = D.DEPT_NO
INNER JOIN :
SELECT *
FROM EMPLOYEE E
INNER JOIN DEPT D ON E.EMP_DEPT = D.DEPT_NO
LEFT JOIN :
SELECT *
FROM EMPLOYEE E
LEFT JOIN DEPT D ON E.EMP_DEPT = D.DEPT_NO
RIGHT JOIN :
SELECT *
FROM EMPLOYEE E
RIGHT JOIN DEPT D ON E.EMP_DEPT = D.DEPT_NO
FULL OUTER JOIN :
SELECT *
FROM EMPLOYEE E
FULL OUTER JOIN DEPT D ON E.EMP_DEPT = D.DEPT_NO
- EMP , EMP_LOG 결과
JOIN :
SELECT *
FROM EMPLOYEE E
JOIN EMP_LOG EL ON E.EMP_NO = EL.EMP_NO
INNER JOIN :
SELECT *
FROM EMPLOYEE E
INNER JOIN EMP_LOG EL ON E.EMP_NO = EL.EMP_NO
LEFT JOIN :
SELECT *
FROM EMPLOYEE E
LEFT JOIN EMP_LOG EL ON E.EMP_NO = EL.EMP_NO
RIGHT JOIN :
SELECT *
FROM EMPLOYEE E
RIGHT JOIN EMP_LOG EL ON E.EMP_NO = EL.EMP_NO
FULL OUTER JOIN :
SELECT *
FROM EMPLOYEE E
FULL OUTER JOIN EMP_LOG EL ON E.EMP_NO = EL.EMP_NO
