SQL (Structured Query Language)

1
SQL (Structured Query Language)

• Part 2

2
Multi-Table Queries (JOIN)

• INNER JOIN
• OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JION
• FULL OUTER JION

3
INNER JOIN

• ??????? join ????????? 2 ????????????????????????
  ??????
• ???????????? ?????????????????????????????????????
  ???????
• ?????? INNER JOIN ??????????????????? ??? JOIN
  ?????

4
OUTER JOIN

• ??????? join ????????? 2 ??????????? ??????
  INNER JOIN
• ?????????????????????????????????????????????????
  OUTER
• JOIN ????????????????????????????????????????????
  ????
• ?????????????????????????????? ???????????????????
  ?????????????
• ??? ??????????????????????????????? NULL ??

5
LEFT OUTER JOIN ( )

• ??????? join ????????? 2 ???????????
  ????????????????
• ??????????????? LEFT OUTER JOIN ????????
  ?????????????
• ?????????????????????????????? ???????????????????
  ??????
• ???????????????????????? ???????????????????????
  ????????
• ????????????????????? NULL

6
RIGHT OUTER JOIN ( )

• ??????? join ????????? 2 ???????????
  ????????????????
• ?????????????? RIGHT OUTER JOIN ????????
  ?????????
• ?????????????????????????????????
  ??????????????????????
• ???????????????????????????? ???????????????????
  ??????????
• ???????????????????????? NULL

7
FULL 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

9
Example INNER JOIN
10
Example INNER JOIN
11
Example INNER JOIN
12
Example LEFT OUTER JOIN
13
Example LEFT OUTER JOIN
14
Example RIGHT OUTER JOIN
15
Example RIGHT OUTER JOIN
16
Example 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)
17
Example FULL OUTER JOIN
18
Example 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
19
Example 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
20
Example Three Table JOIN