Title: SQL (Structured Query Language)
1SQL (Structured Query Language)
2Multi-Table Queries (JOIN)
- INNER JOIN
- OUTER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JION
- FULL OUTER JION
3INNER JOIN
- ??????? join ????????? 2 ????????????????????????
?????? - ???????????? ?????????????????????????????????????
??????? - ?????? INNER JOIN ??????????????????? ??? JOIN
?????
4OUTER JOIN
- ??????? join ????????? 2 ??????????? ??????
INNER JOIN - ?????????????????????????????????????????????????
OUTER - JOIN ????????????????????????????????????????????
???? - ?????????????????????????????? ???????????????????
????????????? - ??? ??????????????????????????????? NULL ??
5LEFT OUTER JOIN ( )
- ??????? join ????????? 2 ???????????
???????????????? - ??????????????? LEFT OUTER JOIN ????????
????????????? - ?????????????????????????????? ???????????????????
?????? - ???????????????????????? ???????????????????????
???????? - ????????????????????? NULL
6RIGHT OUTER JOIN ( )
- ??????? join ????????? 2 ???????????
???????????????? - ?????????????? RIGHT OUTER JOIN ????????
????????? - ?????????????????????????????????
?????????????????????? - ???????????????????????????? ???????????????????
?????????? - ???????????????????????? NULL
7FULL OUTER JOIN
- ??????? join ????????? 2 ???????????
??????????????????? - ??????????????? join ????????????? null
?????????????????? - ???????????????????????????
8????????? JOIN
- SELECT column_name ,...n
- FROM table_name , ...n
- INNER LEFT RIGHT FULL OUTER
- JOIN table_name
- ON search_conditions
9Example INNER JOIN
10Example INNER JOIN
11Example INNER JOIN
12Example LEFT OUTER JOIN
13Example LEFT OUTER JOIN
14Example RIGHT OUTER JOIN
15Example RIGHT OUTER JOIN
16Example FULL OUTER JOIN
SELECT b., p. FROM Branch1 AS b FULL JOIN
PropertyForRent1 AS p ON b.bCity
p.pCity
(SELECT b., p. FROM Branch1 AS b LEFT
JOIN PropertyForRent1 AS p ON
b.bCity p.pCity) UNION (SELECT b., p. FROM
Branch1 AS b RIGHT JOIN PropertyForRent1 AS
p ON b.bCity p.pCity)
17Example FULL OUTER JOIN
18Example Three Table JOIN
- ??????????? ??????????????????????????????????????
?? ???????????????? - ??????????????????? ??????????????????????????????
???????????
?????? 1 ANSI SQL-92 Syntax
SELECT b.branchNo, b.city, e.empNo, e.empName,
p.propertyNo, p.street FROM Branch AS
b JOIN Employee AS e ON b.branchNo
e.branchNo JOIN Property_For_Rent AS p ON
e.empNo p.empNo
19Example Three Table JOIN
?????? 2 Transact - SQL
SELECT b.branchNo, b.city, e.empNo, e.empName,
p.propertyNo, p.street FROM Branch AS
b, Employee AS e, Property_For_Rent AS
p WHERE b.branchNo e.branchNo AND
e.empNo p.empNo
20Example Three Table JOIN