DB

โ˜…select-join

byeol_dev 2023. 2. 27. 17:21

EMP ํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์›์˜ ๋ถ€์„œ๋ช…์ด ๋ฌด์—‡์ธ์ง€ ์•Œ ์ˆ˜ ์žˆ์„๊นŒ?

SELECT * FROM EMP;
SELECT * FROM DEPT;

EMP๋ฅผ ์กฐํšŒํ•ด์„œ๋Š” ์‚ฌ์›๋“ค์˜ ๋ถ€์„œ๋ฒˆํ˜ธ๋Š” ์กฐํšŒ ๊ฐ€๋Šฅํ•˜์ง€๋งŒ ๋ถ€์„œ๋ช…์ด ๋ฌด์—‡์ธ์ง€ ์•Œ ์ˆ˜ ์—†์Œ.

๋ถ€์„œ๋ช…์€ DEPT์— ์ •๋ณด๊ฐ€ ์žˆ๊ธฐ ๋•Œ๋ฌธ์— 2๋ฒˆ ์กฐํšŒํ•ด์•ผ ํ•จ.

 

์กฐ์ธ ํ•™์Šต์„ ์œ„ํ•œ ์‚ฌ์ „ ์ง€์‹

SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL
FROM EMP;

์œ„ ์ฟผ๋ฆฌ ๊ฐ€๋Šฅ! ์ปฌ๋Ÿผ๋ช… ์•ž์—๋Š” ํ…Œ์ด๋ธ”๋ช…์ด ์ˆจ์–ด์ ธ ์žˆ์–ด์„œ ํ‘œํ˜„ํ•ด๋„ ์ฟผ๋ฆฌ ์‹คํ–‰ ๋จ.

ํ…Œ์ด๋ธ”๋ช…์— ๋ณ„์นญ๋„ ์ค„ ์ˆ˜ ์žˆ๋‹ค! AS๋Š” ์ƒ๋žตํ•จ

SELECT E.EMPNO, E.ENAME, E.SAL
FROM EMP E;

์‚ฌ์›๋“ค์˜ ์‚ฌ๋ฒˆ, ์‚ฌ์›๋ช…, ๋ถ€์„œ๋ฒˆํ˜ธ, ๋ถ€์„œ๋ช…, ๋ถ€์„œ์ง€์—ญ ์กฐํšŒํ•ด๋ณด์ž

SELECT EMPNO
    , ENAME
    , DEPTNO
    , ENAME
    , LOC
FROM EMP, DEPT;

์œ„ ์ฟผ๋ฆฌ ์˜ค๋ฅ˜ > DEPTNO๋Š” EMP, DEPT์— ๋‘˜ ๋‹ค ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ์–ด๋–ค ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ์„ ์˜๋ฏธํ•˜๋Š”์ง€ ๋ชจ๋ฆ„

 


JOIN :

์—ฌ๋Ÿฌ ํ…Œ์ด๋ธ”์˜ ์ •๋ณด๋ฅผ ํ•œ๋ฒˆ์— ์กฐํšŒ

JOIN์„ ์ด์šฉํ•˜๋ฉด SELECT๋ฌธ์„ 1๋ฒˆ ์‚ฌ์šฉํ•˜์—ฌ ์—ฌ๋Ÿฌ ๊ฐ€์ง€ ํ…Œ์ด๋ธ” ๋™์‹œ ์กฐํšŒ ๊ฐ€๋Šฅ.

๋‘ ํ…Œ์ด๋ธ”์ด ๊ณตํ†ต์ ์œผ๋กœ ์ง€๋‹ˆ๊ณ  ์žˆ๋Š” ์ปฌ๋Ÿผ ํ™•์ธ ํ›„ ๊ทธ ๋™์‹œ์— ์ง€๋Š” ์ปฌ๋Ÿผ์ด ๊ฐ™์€ ๊ฐ’์ธ์ง€๋ฅผ ์กฐํšŒํ•˜๋ฉด JOIN์ด ๋œ๋‹ค.

JOIN์€ ํ†ต์ƒ์ ์œผ๋กœ ์™ธ๋ž˜ํ‚ค๋กœ ๊ฑด๋‹ค.

์™ธ๋ž˜ํ‚ค๋Š” ๋‘ ํ…Œ์ด๋ธ”์ด ๊ณตํ†ต์ ์ด ๋‹ค ๊ฐ€์ง€๊ณ  ์žˆ๊ธฐ ๋•Œ๋ฌธ

SELECT EMPNO
    , ENAME
    , EMP.DEPTNO
    , DNAME
    , LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY ENAME;

์—ฐ์Šต๋ฌธ์ œ1,2

--1. 20๋ฒˆ ๋ถ€์„œ๋ฅผ ์ œ์™ธํ•œ ์‚ฌ์›๋“ค์˜ ์‚ฌ์›, ๋ถ€์„œ๋ฒˆํ˜ธ, ๋ถ€์„œ๋ช…, ์ง€์—ญ์„ ์กฐํšŒ
SELECT JOB
    , D.DEPTNO
    , DNAME
    , LOC
FROM EMP E, DEPT D
WHERE D.DEPTNO != 20
AND E.DEPTNO = D.DEPTNO;
--2.์ด๋ฌธ์„ธ ์‚ฌ์›์ด ์†ํ•œ ๋ถ€์„œ์˜ ๋ถ€์„œ๋ช…์„ ์กฐํšŒ
SELECT DNAME
FROM DEPT D, EMP E
WHERE ENAME = '์ด๋ฌธ์„ธ'
AND D.DEPTNO = E.DEPTNO;

์—ฐ์Šต๋ฌธ์ œ3

--๊ฒŒ์‹œ๊ธ€ ์ œ๋ชฉ์— 'java'๋ผ๋Š” ๊ธ€์ž๊ฐ€ ํฌํ•จ๋œ ๊ฒŒ์‹œ๊ธ€์˜ ๊ธ€๋ฒˆํ˜ธ, ์ œ๋ชฉ, ์ž‘์„ฑ์ž id, ์ž‘์„ฑ์ผ, ์ž‘์„ฑ์ž๋ช…์„ ์กฐํšŒ

SELECT BOARD_NUM
    , TITLE
    , M.MEM_ID
    , CREATE_DATE
    , MEM_NAME
FROM BOARD_MEMBER M, BOARD B
WHERE TITLE LIKE '%java%'
AND M.MEM_ID = B.WRITER;

์—ฐ์Šต๋ฌธ์ œ4

--๊ธ€๋ฒˆํ˜ธ๊ฐ€ 3๋ฒˆ์ธ ๊ฒŒ์‹œ๊ธ€์— ๋‹ฌ๋ฆฐ ๋Œ“๊ธ€์˜ ๋‚ด์šฉ, ๋Œ“๊ธ€ ์ž‘์„ฑ์ž, ๋Œ“๊ธ€์ด ๋‹ฌ๋ฆฐ ๊ฒŒ์‹œ๊ธ€์˜ ์ œ๋ชฉ์„ ์กฐํšŒ

SELECT P.CONTENT  --๋‚ด์šฉ์€ B,P ํ…Œ์ด๋ธ”์— ๋‹ค ์žˆ์Œ ๋Œ“๊ธ€์˜ ๋‚ด์šฉ์„ ์กฐํšŒํ•˜๊ธฐ ๋•Œ๋ฌธ์— P ๋ถ™์–ด์•ผ ๋จ.
    , P.WRITER
    , TITLE
FROM BOARD B, BOARD_REPLY P
WHERE B.BOARD_NUM = 3 --๊ธ€๋ฒˆํ˜ธ๋„ ์–ด๋–ค ํ…Œ์ด๋ธ”์˜ ๊ธ€๋ฒˆํ˜ธ์ธ์ง€ ์ง€์ •
AND B.BOARD_NUM = P.BOARD_NUM;

์—ฐ์Šต๋ฌธ์ œ5

--EMP ํ…Œ์ด๋ธ”์— MGR ์ปฌ๋Ÿผ์€ ํ•ด๋‹น ์‚ฌ์›์˜ ์ง์†์ƒ๊ด€ ์‚ฌ๋ฒˆ์ด๋‹ค.
--์ด๋ฅผ ์ฐธ๊ณ ํ•ด์„œ ์‚ฌ์›๋ฒˆํ˜ธ, ์‚ฌ์›๋ช…, ์ง์†์ƒ๊ด€ ์‚ฌ๋ฒˆ, ์ง์†์ƒ๊ด€๋ช…์„ ์กฐํšŒํ•˜๋Š” ์ฟผ๋ฆฌ๋ฌธ

SELECT E1.EMPNO
    , E1.ENAME
    , E1.MGR ์ง์†์ƒ๊ด€์‚ฌ๋ฒˆ
    , E2.ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

--JOIN ์‚ฌ์šฉ / ๊ฐ™์€ ํ…Œ์ด๋ธ” ๋‘๋ฒˆ ์กฐํšŒํ•˜๋Š” ๋ฐฉ๋ฒ• ์‚ฌ์šฉํ•ด์•ผ ํ•จ.