Part 11 Advanced SQLCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 2 Outer Joins select dept.deptno, dname, sum(bonus) from emp, dept where emp.deptno(+) = dept.deptno group by dept.deptno, dname; DEPTNO DNAME SUM(BONUS) 400 programming 1000 401 financial 600 402 academic 550 403 supportCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 3 Three tables to practice joins with: SQL> select * from div; DNO Dname 21 A 22 B 23 C 24 D SQL> select * from sec; SNO DNO Sname 41 21 E 42 22 F 43 33 G 44 34 H 45 21 I 46 22 J 47 33 K 48 34 L SQL> select * from grp; GNO SNO DNO Gname 61 41 21 e 62 42 23 f 63 43 33 g 64 44 35 h 65 55 21 i 66 56 23 j 67 57 33 k 68 58 35 lCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 4 Two table inner joins: SQL> select * from div, sec where div.dno = sec.dno; DIV. DNO DIV. DNAME SEC. SNO SEC. DNO SEC. SNAME 21 A 41 21 E 21 A 45 21 I 22 B 42 22 F 22 B 46 22 J SQL> select * from div, grp where div.dno = grp.dno; DIV. DNO DIV. DNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 21 A 61 41 21 e 21 A 65 55 21 i 23 C 62 42 23 f 23 C 66 56 23 j SQL> select * from sec, grp where sec.sno = grp.sno and sec.dno = grp.dno; SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 43 33 G 63 43 33 gCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 5 Two table outer joins SQL> select * from div, sec where div.dno(+) = sec.dno; DIV. DNO DIV. DNAME SEC. SNO SEC. DNO SEC. SNAME 21 A 41 21 E 21 A 45 21 I 22 B 42 22 F 22 B 46 22 J 43 33 G 47 33 K 44 34 H 48 34 L SQL> select * from div, sec where div.dno = sec.dno(+); DIV. DNO DIV. DNAME SEC. SNO SEC. DNO SEC. SNAME 21 A 41 21 E 21 A 45 21 I 22 B 42 22 F 22 B 46 22 J 23 C 24 DCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 6 More two table outer joins SQL> select * from div, grp where div.dno(+) = grp.dno; DIV. DNO DIV. DNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 21 A 61 41 21 e 21 A 65 55 21 i 23 C 62 42 23 f 23 C 66 56 23 j 63 43 33 g 67 57 33 k 64 44 35 h 68 58 35 l SQL> select * from div, grp where div.dno = grp.dno(+); DIV. DNO DIV. DNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 21 A 61 41 21 e 21 A 65 55 21 i 22 B 23 C 62 42 23 f 23 C 66 56 23 j 24 D SQL> select * from sec, grp where sec.sno(+) = grp.sno and sec.dno = grp.dno; SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 43 33 G 63 43 33 gCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 7 Still more two table outer joins SQL> select * from sec, grp where sec.sno = grp.sno(+) and sec.dno = grp.dno; SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 43 33 G 63 43 33 g SQL> select * from sec, grp where sec.sno = grp.sno and sec.dno(+) = grp.dno; SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 43 33 G 63 43 33 g SQL> select * from sec, grp where sec.sno = grp.sno and sec.dno = grp.dno(+); SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 43 33 G 63 43 33 g SQL> select * from sec, grp where sec.sno(+) = grp.sno and sec.dno = grp.dno(+); ORA-01416: two tables cannot be outer-joined to each other SQL> select * from sec, grp where sec.sno = grp.sno(+) and sec.dno(+) = grp.dno; ORA-01416: two tables cannot be outer-joined to each otherCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 8 Even more two table outer joins SQL> select * from sec, grp where sec.sno(+) = grp.sno and sec.dno(+) = grp.dno; SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 62 42 23 f 43 33 G 63 43 33 g 64 44 35 h 65 55 21 i 66 56 23 j 67 57 33 k 68 58 35 l SQL> select * from sec, grp where sec.sno = grp.sno(+) and sec.dno = grp.dno(+); SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP. GNAME 41 21 E 61 41 21 e 42 22 F 43 33 G 63 43 33 g 44 34 H 45 21 I 46 22 J 47 33 K 48 34 LCopyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 9 Three table inner join SQL> select * from div, sec, grp where div.dno = sec.dno and div.dno = grp.dno and sec.sno = grp.sno and sec.dno = grp.dno; DIV. DNO DIV. DNAME SEC. SNO SEC. DNO SEC. SNAME GRP. GNO GRP. SNO GRP. DNO GRP.GNAME21 A 41 21 E 61 41 21 e Attempts at 3 table outer joins All of the following attempts at 3 table outer joins yield the result above, a single table that is the inner join: SQL> select * from div, sec, grp where div.dno(+) = sec.dno and sec.sno = grp.sno and div.dno = grp.dno(+); SQL> select * from div, sec, grp where div.dno = sec.dno(+) and sec.sno = grp.sno and sec.dno = grp.dno; SQL> select * from div, sec, grp where div.dno = sec.dno(+) and sec.sno = grp.sno(+) and sec.dno = grp.dno; SQL> select * from div, sec, grp where div.dno = sec.dno(+) and sec.sno = grp.sno and sec.dno = grp.dno(+); SQL> select * from div, sec, grp where div.dno = sec.dno(+) and sec.sno = grp.sno and div.dno(+) = grp.dno;Copyright © 1971-2002 Thomas P. Sturm Advanced SQL Part 11, Page 10 More unsuccessful 3 table outer joins SQL> select * from div, sec, grp where div.dno = sec.dno and sec.sno(+) = grp.sno and div.dno = grp.dno; SQL> select * from div, sec, grp where div.dno = sec.dno and sec.sno(+) = grp.sno and div.dno(+) = grp.dno; SQL> select * from div, sec, grp where div.dno = sec.dno and sec.sno(+) = grp.sno and div.dno = grp.dno(+); SQL> select * from div, sec, grp where div.dno = sec.dno and sec.sno(+) = grp.sno and sec.dno(+) = grp.dno; SQL> select * from div, sec, grp where div.dno = sec.dno and sec.sno = …