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 lTwo 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 gTwo 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 DMore 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 gStill 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 otherEven 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 LThree 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;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 = 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; SQL> select * from div, sec, grp where div.dno = sec.dno(+) and sec.sno = grp.sno and div.dno = grp.dno(+);3 Table Partial Outer Join All of the following queries produce the same result below. The result is a “partial” outer join – those rows with matching section and group information. 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(+) = …
View Full Document