Search

์กฐ์ธ

์กฐ์ธ (JOIN)

: ์—ฌ๋Ÿฌ ํ…Œ์ด๋ธ”์„ ์กฐํ•ฉํ•˜์—ฌ ์กฐํšŒํ•˜๋Š” ๋ฐฉ์‹

ํ…Œ์ด๋ธ”์˜ ๋ณ„์นญ

: ํ…Œ์ด๋ธ”์˜ ๋Œ€ํ•œ ๋ณ„์นญ์€ ํ…Œ์ด๋ธ”๋ช… ๋’ค์— ํ•œ ์นธ์„ ๋„์šฐ๊ณ  ์ง€์ •ํ•œ๋‹ค.
SELECT * FROM ํ…Œ์ด๋ธ”๋ช…1 ๋ณ„์นญ1, ํ…Œ์ด๋ธ”๋ช…2 ๋ณ„์นญ2, ... ;
SQL
๋ณต์‚ฌ
โ€ข
๊ธด ์ด๋ฆ„์˜ ํ…Œ์ด๋ธ”๋ช…์„ ์งง์€ ์ด๋ฆ„์˜ ๋ณ„์นญ์œผ๋กœ ์ง€์ •ํ•˜์—ฌ ์ฝ”๋“œ๋ฅผ ๊ฐ€๋…์„ฑ์ด ๋†’๊ฒŒ ์ž‘์„ฑํ•ด์ฃผ๋Š” ๊ฒƒ์„ ๊ถŒ์žฅํ•œ๋‹ค.
โ€ข
ex)
SELECT * FROM employee emp, department dept;
SQL
๋ณต์‚ฌ

์กฐ์ธ ์ข…๋ฅ˜

๋‚ด๋ถ€์กฐ์ธ

โ€ข
๋™๋“ฑ ์กฐ์ธ
โ€ข
์„ธ๋ฏธ ์กฐ์ธ
โ€ข
์•ˆํ‹ฐ ์กฐ์ธ
โ€ข
์…€ํ”„ ์กฐ์ธ

์™ธ๋ถ€์กฐ์ธ

โ€ข
(+)
โ€ข
ANSI ์กฐ์ธ
โ—ฆ
LEFT OUTER JOIN
โ—ฆ
RIGTH OUTER JOIN
โ—ฆ
FULL OUTER JOIN
โ—ฆ
CROSS JOIN (์นดํƒ€์‹œ์•ˆ ์กฐ์ธ)

๋‚ด๋ถ€์กฐ์ธ (INNER JOIN)

๋™๋“ฑ์กฐ์ธ (EQUI JOIN)

: ๋“ฑํ˜ธ(=) ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ, 2๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”์„ ์—ฐ๊ฒฐ์ง€์–ด ์ถœ๋ ฅํ•˜๋Š” ๋ฐฉ์‹
SELECT * FROM A, B WHERE A.x = B.y;
SQL
๋ณต์‚ฌ
โ€ข
INNER JOIN
: ์™ผ์ชฝ ํ…Œ์ด๋ธ”๊ณผ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ์กฐ์ธ ์กฐ๊ฑด์— ๋งŒ์กฑํ•˜๋Š” ๊ต์ง‘ํ•ฉ์ด ๋˜๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•˜๋Š” ๋ฐฉ์‹
SELECT * FROM A INNER JOIN B ON (A.x = B.y);
SQL
๋ณต์‚ฌ

์„ธ๋ฏธ ์กฐ์ธ (SEMI JOIN)

: ์„œ๋ธŒ ์ฟผ๋ฆฌ์— ์กด์žฌํ•˜๋Š” ๋ฐ์ดํ„ฐ๋งŒ ๋ฉ”์ธ ์ฟผ๋ฆฌ์—์„œ ์ถ”์ถœํ•˜์—ฌ ์ถœ๋ ฅํ•˜๋Š” ๋ฐฉ์‹
โ€ข
IN ๋˜๋Š” EXISTS ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•œ ์กฐ์ธ

์•ˆํ‹ฐ ์กฐ์ธ (ANTI JOIN)

: ์„œ๋ธŒ ์ฟผ๋ฆฌ์— ์กด์žฌํ•˜๋Š” ๋ฐ์ดํ„ฐ๋งŒ ์ œ์™ธํ•˜๊ณ  ๋ฉ”์ธ ์ฟผ๋ฆฌ์—์„œ ์ถ”์ถœํ•˜์—ฌ ์ถœ๋ ฅํ•˜๋Š” ๋ฐฉ์‹

์…€ํ”„ ์กฐ์ธ (SELF JOIN)

: ๋™์ผํ•œ ํ•˜๋‚˜์˜ ํ…Œ์ด๋ธ”์„ 2๋ฒˆ ์ด์ƒ ์กฐํ•ฉํ•˜์—ฌ ์ถœ๋ ฅํ•˜๋Š” ๋ฐฉ์‹

์™ธ๋ถ€์กฐ์ธ (OUTER JOIN)

LEFT OUTER JOIN

: ์™ผ์ชฝ ํ…Œ์ด๋ธ”์„ ๋จผ์ € ์ฝ์–ด๋“œ๋ฆฐ ํ›„, ์กฐ์ธ ์กฐ๊ฑด์— ์ผ์น˜ํ•˜๋Š” ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์„ ํ•จ๊ป˜ ์กฐํšŒํ•˜๋Š” ๊ฒƒ
์กฐ๊ฑด์— ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š”, ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋Š” NULL ๋กœ ์กฐํšŒ๋œ๋‹ค.
โ€ข
ANSI
ํ…Œ์ด๋ธ”1 A LEFT [OUTER] JOIN ํ…Œ์ด๋ธ”2 B ON ์กฐ์ธ์กฐ๊ฑด; ํ…Œ์ด๋ธ”1 A LEFT [OUTER] JOIN ํ…Œ์ด๋ธ”2 B USING(๊ณตํ†ต์ปฌ๋Ÿผ);
SQL
๋ณต์‚ฌ
โ€ข
(+)
FROM ํ…Œ์ด๋ธ”1 A, ํ…Œ์ด๋ธ”2 B WHERE A.๊ณตํ†ต์ปฌ๋Ÿผ = B.๊ณตํ†ต์ปฌ๋Ÿผ(+)
SQL
๋ณต์‚ฌ

RIGHT OUTER JOIN

: ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์„ ๋จผ์ € ์ฝ์–ด๋“œ๋ฆฐ ํ›„, ์กฐ์ธ ์กฐ๊ฑด์— ์ผ์น˜ํ•˜๋Š” ์™ผ์ชฝ ํ…Œ์ด๋ธ”์„ ํ•จ๊ป˜ ์กฐํšŒํ•˜๋Š” ๊ฒƒ
์กฐ๊ฑด์— ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š”, ์™ผ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋Š” NULL ๋กœ ์กฐํšŒ๋œ๋‹ค.
โ€ข
ANSI
ํ…Œ์ด๋ธ”1 A RIGHT [OUTER] JOIN ํ…Œ์ด๋ธ”2 B ON ์กฐ์ธ์กฐ๊ฑด; ํ…Œ์ด๋ธ”1 A RIGHT [OUTER] JOIN ํ…Œ์ด๋ธ”2 B USING(๊ณตํ†ต์ปฌ๋Ÿผ);
SQL
๋ณต์‚ฌ
โ€ข
(+)
FROM ํ…Œ์ด๋ธ”1 A, ํ…Œ์ด๋ธ”2 B WHERE A.๊ณตํ†ต์ปฌ๋Ÿผ(+) = B.๊ณตํ†ต์ปฌ๋Ÿผ
SQL
๋ณต์‚ฌ

FULL OUTER JOIN

: ์กฐ์ธ ์กฐ๊ฑด์— ์ผ์น˜ํ•˜๋Š” ์–‘์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํ•ฉํ•˜์—ฌ ์กฐํšŒํ•˜๋Š” ๊ฒƒ
์กฐ๊ฑด์— ๋งŒ์กฑํ•˜์ง€ ์•Š๋Š”, ์–‘์ชฝ์— ๋ฐ์ดํ„ฐ๋Š” NULL ๋กœ ์กฐํšŒ๋œ๋‹ค.
ํ…Œ์ด๋ธ”1 A FULL [OUTER] JOIN ํ…Œ์ด๋ธ”2 B ON ์กฐ์ธ์กฐ๊ฑด;
SQL
๋ณต์‚ฌ

์นดํƒ€์‹œ์•ˆ ์กฐ์ธ (Catesian; CATASIAN PRODUCT) : CROSS ์กฐ์ธ

: WHERE ์ ˆ์— ์กฐ์ธ ์กฐ๊ฑด์ด ์—†๋Š” ์กฐ์ธ
โ€ข
A ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ ๊ฑด์ˆ˜ : X
โ€ข
B ํ…Œ์ด๋ธ”์˜ ๋ฐ์ดํ„ฐ ๊ฑด์ˆ˜ : Y
โ€ข
๊ฒฐ๊ณผ ๊ฑด์ˆ˜ : X * Y (๋‘ ํ…Œ์ด๋ธ”์˜ ๊ณฑ)
SELECT * FROM employees e ,departments d;
SQL
๋ณต์‚ฌ
โ€ข
ANSI
SELECT * FROM employees e CROSS JOIN departments d;
SQL
๋ณต์‚ฌ

HR, ํ•œ๊ธ€HR ์Šคํ‚ค๋งˆ ์‹ค์Šต ์ฝ”๋“œ

๋‚ด๋ถ€์กฐ์ธ

โ€ข
๋™๋“ฑ์กฐ์ธ
SELECT e.employee_id ,e.first_name ,d.department_id ,d.department_name FROM employees e ,departments d WHERE e.department_id = d.department_id;
SQL
๋ณต์‚ฌ
โ€ข
์„ธ๋ฏธ์กฐ์ธ
EXISTS ์‚ฌ์šฉ
SELECT department_id, department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE e.department_id = d.department_id AND e.salary > 3000 ) ORDER BY d.department_name;
SQL
๋ณต์‚ฌ
IN ์‚ฌ์šฉ
SELECT department_id, department_name FROM departments d WHERE d.department_id IN ( SELECT e.department_id FROM employees e WHERE e.salary > 3000 ) ORDER BY d.department_name
SQL
๋ณต์‚ฌ
โ€ข
์•ˆํ‹ฐ์กฐ์ธ
NOT EXISTS ์‚ฌ์šฉ
SELECT department_id, department_name FROM departments d WHERE NOT EXISTS(SELECT * FROM employees e WHERE e.department_id = d.department_id AND e.salary > 3000 ) ORDER BY d.department_name;
SQL
๋ณต์‚ฌ
NOT IN์‚ฌ์šฉ
SELECT e.employee_id ,e.first_name ,d.department_id ,d.department_name FROM employees e ,departments d WHERE e.department_id = d.department_id AND e.department_id NOT IN ( SELECT department_id FROM departments WHERE manager_id IS NULL ) ;
SQL
๋ณต์‚ฌ
โ€ข
์…€ํ”„์กฐ์ธ
โ—ฆ
โ€œ์‚ฌ์›๊ณผ ๊ด€๋ฆฌ์žโ€ ๋ฅผ ํ•จ๊ป˜ ์กฐํšŒํ•˜์‹œ์˜ค.
SELECT a.employee_id ์‚ฌ์›๋ฒˆํ˜ธ ,a.first_name ์‚ฌ์›๋ช… ,b.employee_id ๊ด€๋ฆฌ์ž๋ฒˆํ˜ธ ,b.first_name ๊ด€๋ฆฌ์ž๋ช… ,a.department_id ๋ถ€์„œ๋ฒˆํ˜ธ FROM employees a, employees b WHERE a.manager_id = b.employee_id AND a.department_id = b.department_id ;
SQL
๋ณต์‚ฌ

์™ธ๋ถ€์กฐ์ธ

โ€ข
LEFT OUTER JOIN
(+) : ์กฐ์ธ ์กฐ๊ฑด์—์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ์—†๋Š”(NULL) ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ์— ๊ธฐํ˜ธ๋ฅผ ๋ถ™์—ฌ์ค€๋‹ค.
SELECT e.emp_id ,e.emp_name ,d.dept_id ,d.dept_title FROM employee e ,department d WHERE e.dept_code = d.dept_id(+) ;
SQL
๋ณต์‚ฌ
ANSI
SELECT e.emp_id ,e.emp_name ,d.dept_id ,d.dept_title FROM employee e LEFT OUTER JOIN department d -- OUTER ์ƒ๋žต๊ฐ€๋Šฅ ON e.dept_code = d.dept_id ;
SQL
๋ณต์‚ฌ
โ€ข
RIGHT OUTER JOIN
(+)
: ์กฐ์ธ ์กฐ๊ฑด์—์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ์—†๋Š”(NULL) ํ…Œ์ด๋ธ”์˜ ์ปฌ๋Ÿผ์— ๊ธฐํ˜ธ๋ฅผ ๋ถ™์—ฌ์ค€๋‹ค.
SELECT e.emp_id ,e.emp_name ,d.dept_id ,d.dept_title FROM employee e ,department d WHERE e.dept_code(+) = d.dept_id ;
SQL
๋ณต์‚ฌ
ANSI
SELECT e.emp_id ,e.emp_name ,d.dept_id ,d.dept_title FROM employee e RIGHT OUTER JOIN department d -- OUTER ์ƒ๋žต๊ฐ€๋Šฅ ON e.dept_code = d.dept_id ;
SQL
๋ณต์‚ฌ

์™ธ๋ถ€์กฐ์ธ ์œ ์˜์‚ฌํ•ญ

: ๋‘ ํ…Œ์ด๋ธ”์˜ ์กฐ์ธ ์กฐ๊ฑด์ด ๋˜๋Š” ๊ณตํ†ต ์ปฌ๋Ÿผ์— ๋ชจ๋‘ (+) ๊ธฐํ˜ธ๋ฅผ ๋ถ™์—ฌ์•ผํ•œ๋‹ค.
โ€ข
ex) ๊ณตํ†ต์ปฌ๋Ÿผ : employee_id, department_id
SELECT e.employee_id ,e.first_name ,j.job_id ,j.department_id FROM employees e ,job_history j WHERE e.employee_id = j.employee_id(+) AND e.department_id = j.department_id(+) ;
SQL
๋ณต์‚ฌ
โ€ข
FULL OUTER JOIN
(+) ๊ธฐํ˜ธ๋กœ ๊ตฌํ˜„ ๋ถˆ๊ฐ€
SELECT * FROM employee e ,department d WHERE e.dept_code(+) = d.dept_id(+); -- (+) ๊ธฐํ˜ธ๋กœ๋Š” FULL JOIN ๋ถˆ๊ฐ€
SQL
๋ณต์‚ฌ
ANSI
SELECT e.emp_name ,d.dept_title FROM employee e FULL OUTER JOIN department d ON dept_code = d.dept_id;
SQL
๋ณต์‚ฌ