8 joins

(1)

=================================== =================================== JOINS JOINS =================================== =================================== -- Joins Means as a name combined somthing.

-- Joins Means as a name combined somthing.

-- In The Oracle SQL Join means combined Two or More Tables Together. -- In The Oracle SQL Join means combined Two or More Tables Together. ---TYPES OF TYPES OF JOINS:-

---1.Natural Joins. This Natural

1.Natural Joins. This Natural Joins TJoins Two table based On wo table based On Same !olumn Name.Same !olumn Name. ".Inner Join # Sim$le Join

".Inner Join # Sim$le Join %.&'ui(oins %.&'ui(oins ).NON&'ui(oins ).NON&'ui(oins *.Outer Join *.Outer Join i.

i. Le+t Le+t Outer Outer JoinJoin i

ii. i. ,ight ,ight Outer JOuter Joinoin iii. ull Outer Join iii. ull Outer Join .Sel+ Joins. .Sel+ Joins. /.!ross Join /.!ross Join ---1. Natural Joins: 1. Natural Joins: --- 0

0ou can Join Tou can Join Table utometical2 baable utometical2 based on the coloumn in the Tsed on the coloumn in the Two Two Tables that ha3eables that ha3e matching datat2$es and names. 0ou this b2 4sing NT4,L JOIN b2 This 5e2word. matching datat2$es and names. 0ou this b2 4sing NT4,L JOIN b2 This 5e2word. &.g.1-6Join Two Tables using Natural Joins

&.g.1-6Join Two Tables using Natural Joins SQL6desc em$lo2ees7

SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7

SQL6select em$lo2ee8id9 (ob8id9 salar29 de$artment8id9 manager8id9 SQL6select em$lo2ee8id9 (ob8id9 salar29 de$artment8id9 manager8id9 de$artment8name +rom em$lo2ees

de$artment8name +rom em$lo2ees NNT4,L JOIN de$artments7T4,L JOIN de$artments7 &.g."-6Join More than Two Tables 4sing Natural Joins

&.g."-6Join More than Two Tables 4sing Natural Joins SQL6desc em$lo2ees7 SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7 SQL6desc locations7 SQL6desc locations7 SQL6desc countries7 SQL6desc countries7

SQL6select em$lo2ee8id9 (ob8id9 salar29 de$artment8id9 manager8id9 SQL6select em$lo2ee8id9 (ob8id9 salar29 de$artment8id9 manager8id9 de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees

NT4,L JOIN :&

NT4,L JOIN :&;,;,TM&NTSTM&NTS NT4,L JOIN LO!TIONS NT4,L JOIN LO!TIONS NT4,L JOIN !O4NT,I&S7 NT4,L JOIN !O4NT,I&S7

&.g.)-6 Natural Joins <ith <>&,& !L4S&. &.g.)-6 Natural Joins <ith <>&,& !L4S&. SQL6 select em$lo2ee8id9 (ob8id9 salar29

SQL6 select em$lo2ee8id9 (ob8id9 salar29 de$artment8id9 manager8id9de$artment8id9 manager8id9 de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees

NT4,L JOIN :&

NT4,L JOIN :&;,;,TM&NTSTM&NTS NT4,L JOIN LO!TIONS NT4,L JOIN LO!TIONS NT4,L JOIN !O4NT,I&S NT4,L JOIN !O4NT,I&S

<>&,& de$artment8I: IN?"@9*@A7 <>&,& de$artment8I: IN?"@9*@A7

(2)

(3)

&.g.*.i-6 Join T

&.g.*.i-6 Join Tables using Natural ables using Natural Join and use Join and use the 4SINB !lause.the 4SINB !lause. Note

Note a. 4SINB !lause caa. 4SINB !lause can be used to s$eciCe Onl2 those cn be used to s$eciCe Onl2 those coloumns that should beoloumns that should be used +or an e'ui(oins.

used +or an e'ui(oins. b.

b. <e <e can can use use onl2 onl2 those those columns columns who who are are same same or or comun comun +or +or both both table table inin using clause.

using clause. c.

c. I+ I+ one one TTable able ha3e ha3e tow tow comon comon column column with with another another table table i+ i+ i i mention mention onl2onl2 one column in 4SINB clause and this column not mention in select statemnt then it one column in 4SINB clause and this column not mention in select statemnt then it will

will gi3e gi3e the the ambigusl2 ambigusl2 deCned deCned errorerror. . so so 2ou 2ou ha3e ha3e to to mention mention onl2 onl2 thosethose column name which 2ou want to use in 4SINB clause.

column name which 2ou want to use in 4SINB clause. SQL6

SQL6 select em$select em$lo2ee8id9 lo2ee8id9 (ob8id9 (ob8id9 salar29 masalar29 manager8id9 dnager8id9 de$artment8name9e$artment8name9 street8addr

street8address9 cit29 ess9 cit29 countr28name +rom em$lo2eescountr28name +rom em$lo2ees JOIN :&;,TM&NTS JOIN :&;,TM&NTS 4SINB?manager8idA 4SINB?manager8idA JOIN LO!TIONS JOIN LO!TIONS 4SINB?location8idA 4SINB?location8idA JOIN !O4NT,I&S JOIN !O4NT,I&S 4SINB

4SINB ?countr28idA7?countr28idA7 &.g.*.ii-6

&.g.*.ii-6

SQL6 select em$lo2ee8id9 (ob8id9 salar29

SQL6 select em$lo2ee8id9 (ob8id9 salar29 manager8id9de$artment8manager8id9de$artment8id9id9 de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees de$artment8name9 street8address9 cit29 countr28name +rom em$lo2ees

JOIN :&;,TM&NTS JOIN :&;,TM&NTS 4SINB?manager8id9

4SINB?manager8id9 de$artment8idAde$artment8idA JOIN LO!TIONS JOIN LO!TIONS 4SINB?location8idA 4SINB?location8idA JOIN !O4NT,I&S JOIN !O4NT,I&S 4SINB

4SINB ?countr28idA7?countr28idA7 &.g.-6 !,&

&.g.-6 !,&TINB JOINS <IT> ON TINB JOINS <IT> ON !L4S&.!L4S&. use ON !lause to s$eciCed a (oin

use ON !lause to s$eciCed a (oin !onditions. <ith This 2ou can s$eciCed (oin!onditions. <ith This 2ou can s$eciCed (oin condition se$rate +rom an2 search or ilt

condition se$rate +rom an2 search or ilter !ondition in the <>&,& er !ondition in the <>&,& clause.clause. SQL6select e.em$lo2ee8id9 e.(ob8id9

SQL6select e.em$lo2ee8id9 e.(ob8id9 e.salar29 e.manager8id9d.manager8id9e.salar29 e.manager8id9d.manager8id9 e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 l.street8address9 l.cit29 e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 l.street8address9 l.cit29 c.countr28name

c.countr28name +rom +rom em$lo2ees em$lo2ees ee JOIN :&;

JOIN :&;,,TM&NTS dTM&NTS d

ON?e.de$artment8id=d.de$artment8idA ON?e.de$artment8id=d.de$artment8idA

JOIN LO!

JOIN LO!TIONS lTIONS l ON

ON ?d.location8id=l?d.location8id=l.location8idA.location8idA JOIN !O4NT,I&S c

JOIN !O4NT,I&S c ON

ON ?l.countr28id=c.countr28idA?l.countr28id=c.countr28idA77 &.g./-6 0

&.g./-6 0ou can use ou can use ON !L4S& ON !L4S& and 4SINB !Land 4SINB !L4S& at One 4S& at One Quer2Quer2.. SQL6select e.em$lo2ee8id9 e.(ob8id9

SQL6select e.em$lo2ee8id9 e.(ob8id9 e.salar29 e.manager8id9d.manager8id9e.salar29 e.manager8id9d.manager8id9 e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 l.street8address9 l.cit29 e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 l.street8address9 l.cit29 c.countr28name

c.countr28name +rom +rom em$lo2ees em$lo2ees ee JOIN :&;

JOIN :&;,,TM&NTS dTM&NTS d

ON?e.de$artment8id=d.de$artment8idA ON?e.de$artment8id=d.de$artment8idA

JOIN LO!

JOIN LO!TIONS lTIONS l 4SINB

(4)

(5)

JOIN !O4NT,I&S c JOIN !O4NT,I&S c

4SINB

4SINB ?countr28idA7?countr28idA7 &.g.D-6 4se N: !L4S& or

&.g.D-6 4se N: !L4S& or <>&,& !L4S& to <>&,& !L4S& to a$$l2 dditioa$$l2 dditional conditionsnal conditions SQL6

SQL6 select select e.Crst8name9 e.Crst8name9 e.last8name9 e.last8name9 e.salar29 e.salar29 d.de$artment8name9d.de$artment8name9 d.location8id +rom em$lo2ees e

d.location8id +rom em$lo2ees e (oin

(oin de$artments de$artments dd on

on ?e.de$artment8id ?e.de$artment8id = = d.de$artment8idAd.de$artment8idA and

and e.manager8id e.manager8id = = 1)E71)E7 or

or SQL6

SQL6 select select e.Crst8name9 e.Crst8name9 e.last8name9 e.last8name9 e.salar29 e.salar29 d.de$artment8name9d.de$artment8name9 d.location8id +rom em$lo2ees e

d.location8id +rom em$lo2ees e (oin de$artments d

(oin de$artments d on

on ?e.de$artment8id ?e.de$artment8id = = d.de$artment8idAd.de$artment8idA where

where e.manager8id e.manager8id = = 1)E71)E7

---2. Inner Join / Simple Join: 2. Inner Join / Simple Join: ---Inner (oin is the

Inner (oin is the sim$lest and most common Tsim$lest and most common T2$e O+ (oin. It is also 2$e O+ (oin. It is also called as sim$lecalled as sim$le (oin.

(oin.

It returns all ,ows rom multi$le Tables where the Join condition is Met. It returns all ,ows rom multi$le Tables where the Join condition is Met.

&.g.1-6Join More than Two Tables. in Fellow command i tr2ed to (oin our Tables &.g.1-6Join More than Two Tables. in Fellow command i tr2ed to (oin our Tables T

Togetherogether..

Note  <e can use

Note  <e can use ONclasue and 4SINB ONclasue and 4SINB clause in one clause in one 'uer2'uer2.. SQL6desc em$lo2ees7 SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7 SQL6desc locations7 SQL6desc locations7 SQL6desc countries7 SQL6desc countries7

SQL6select e.em$lo2ee8id9 e.(ob8id9e.salar29 e.de$artment8id9

SQL6select e.em$lo2ee8id9 e.(ob8id9e.salar29 e.de$artment8id9 d.de$artment8id9d.de$artment8id9 e.manager8id9 d.manager8id9 d.de$artment8name9

e.manager8id9 d.manager8id9 d.de$artment8name9 l.street8addrl.street8address9 ess9 l.cit29l.cit29 c.countr28name

c.countr28name

+rom em$lo2ees e +rom em$lo2ees e

INN&, JOIN de$artments d INN&, JOIN de$artments d

ON?e.de$artment8id=d.de$artment8idA ON?e.de$artment8id=d.de$artment8idA INN&, JOIN locations l

INN&, JOIN locations l 4SINB?location8idA 4SINB?location8idA INN&, JOIN countries c INN&, JOIN countries c 4SINB?countr28idA7 4SINB?countr28idA7 ---3.EQIJOINS: 3.EQIJOINS:

(6)

(7)

---&'ui (oion is a (oin condition containing an &'ualit2 o$eretor ?=A. &'ui (oion is a (oin condition containing an &'ualit2 o$eretor ?=A. &.g.1-6 Two Tables Joins

&.g.1-6 Two Tables Joins SQL6

SQL6 select select e.em$lo2ee8id9 e.em$lo2ee8id9 e.(ob8id9 e.(ob8id9 e.salar29 e.salar29 e.manager8id9 e.manager8id9 d.manager8id9d.manager8id9 e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 d.location8id +rom

e.de$artment8id9 d.de$artment8id9 d.de$artment8name9 d.location8id +rom em$lo2ees

em$lo2ees e9 e9 de$artments de$artments d d where where ?e.de$artment8id ?e.de$artment8id == d.de$artment8idA7

d.de$artment8idA7

&.g."-6 More Than T<O Tables &.g."-6 More Than T<O Tables SQL6

SQL6 select e.Crst8name9 e.last8name9 e.salar29 d.de$select e.Crst8name9 e.last8name9 e.salar29 d.de$artment8name9 d.location8id9artment8name9 d.location8id9 l.cit2 +rom em$lo2ees e9 de$artments d9 locations l

l.cit2 +rom em$lo2ees e9 de$artments d9 locations l where

where e.de$artment8id=d.e.de$artment8id=d.de$artment8idde$artment8id N:

N: d.location8id=l.d.location8id=l.location8id7location8id7

---!.NONEQIJOINS: !.NONEQIJOINS:

--- NON&Q4IJOINS is a Join !ondition containing somethig other than

 NON&Q4IJOINS is a Join !ondition containing somethig other than &'ualit2&'ualit2 O$eretor.

O$eretor. &Gam$le  &Gam$le 

Su$ose we ha3e &M;LO0&&S TFL& and JOF8B,:&S table li5e bellow Structure. Su$ose we ha3e &M;LO0&&S TFL& and JOF8B,:&S table li5e bellow Structure. SQL6 desc em$lo2ees7

SQL6 desc em$lo2ees7 Name

Name NullH NullH TT2$e2$e ---

--- --- --- --- ---&M;LO0&&8I:

&M;LO0&&8I: N4MF&,?AN4MF&,?A I,ST8NM&

I,ST8NM& ,,!>,"?"@!>,"?"@AA LST8NM

LST8NM& & NOT NOT N4LL N4LL ,,!>,"?"*!>,"?"*AA &MIL

&MIL NOT NOT N4LL N4LL ,,!>,"?"*A!>,"?"*A ;>ON&8N4

;>ON&8N4MF&, MF&, ,,!>,"?"@!>,"?"@AA >I,&8:T&

>I,&8:T& NOT NOT N4LL N4LL ::T&T& JOF8I:

JOF8I: NOT NOT N4LL N4LL ,,!>,"?1@A!>,"?1@A SL,0

SL,0 N4MF&,?D9"AN4MF&,?D9"A !OMMISSION8;!T

!OMMISSION8;!T N4MF&,?"9"AN4MF&,?"9"A MNB&,8I:

MNB&,8I: N4MF&,?AN4MF&,?A :&;,

:&;,TM&NT8I: TM&NT8I: N4MF&,?N4MF&,?)A)A

SQL6 desc (ob8grades7 SQL6 desc (ob8grades7

Name

Name NullH NullH TT2$e2$e

--- --- --- --- --- ---B,:&8L&&L

B,:&8L&&L ,!>,"?%A,!>,"?%A LO<&ST8SL

LO<&ST8SL N4MF&,N4MF&, >IB>&ST8SL

>IB>&ST8SL N4MF&,N4MF&,

:ata O+ Job Brades :ata O+ Job Brades -B,

(8)

(9)

--- --- --- --- --- ---: : *@@ *@@ *@@@*@@@ ! ! *@@1 *@@1 1@@@@1@@@@ F F 1@@@1 1@@@1 1*@@@1*@@@   1*@@1 1*@@1 %@@@@%@@@@

SQL6select e.last8name9 e.salar29 (.grade8le3el +rom em$lo2ees e JOIN

SQL6select e.last8name9 e.salar29 (.grade8le3el +rom em$lo2ees e JOIN (ob8grades ((ob8grades ( ON e.salar2 F&T<&&N (.lowest8sal N: (.highest8sal7

ON e.salar2 F&T<&&N (.lowest8sal N: (.highest8sal7 ---". Outer Join: ". Outer Join: ---i.

i. Le+t OLe+t Outer Join  ruter Join  returns all roeturns all rows +rom the ws +rom the le+t table+t table9 e3en i+ le9 e3en i+ there arethere are no matches in the right table.

no matches in the right table. ii.

ii. ,ight ,ight Outer Outer Join Join returns returns all all rows rows +rom +rom the the ,ight ,ight table9 table9 e3en e3en i+ i+ therethere are no matches in the le+t table.

are no matches in the le+t table.

iii. ull Outer Join  returns rows when there is a match in

iii. ull Outer Join  returns rows when there is a match in one o+ theone o+ the tables.

tables.

Outer Join- Outer Join is the Similier to &'ui(oins. but it gets also the non match Outer Join- Outer Join is the Similier to &'ui(oins. but it gets also the non match ,ows rom tha table.

,ows rom tha table. It

It ic ic !ategoried !ategoried in Lin Le+t e+t Outer (oOuter (oin9 ,igin9 ,ight Outer ht Outer Join9 Join9 ull ull Outer JoOuter Join b2in b2 Oracle EI NSI#ISO 1EEE standreds.

Oracle EI NSI#ISO 1EEE standreds. i. #e$t Outer Join:

i. #e$t Outer Join: ---SQL6desc em$lo2ees7 SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7 SQL6desc locations7 SQL6desc locations7 SQL6desc countries7 SQL6desc countries7

c.countr28name

L&T O4T&, JOIN de$artments d L&T O4T&, JOIN de$artments d ON?e.manager8id=d.manager8idA ON?e.manager8id=d.manager8idA L&T O4T&, JOIN locations l

L&T O4T&, JOIN locations l 4SINB?location8idA

4SINB?location8idA

L&T O4T&, JOIN countries c L&T O4T&, JOIN countries c 4SINB?countr28idA7

4SINB?countr28idA7 ii. %i&'t Outer Join: ii. %i&'t Outer Join: ---SQL6desc em$lo2ees7 SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7 SQL6desc locations7 SQL6desc locations7 SQL6desc countries7 SQL6desc countries7

(10)

(11)

,IB>T O4T&, JOIN de$artments d ,IB>T O4T&, JOIN de$artments d ON?e.manager8id=d.manager8idA ON?e.manager8id=d.manager8idA ,IB>T O4T&, JOIN locations l ,IB>T O4T&, JOIN locations l 4SINB?location8idA

4SINB?location8idA

,IB>T O4T&, JOIN countries c ,IB>T O4T&, JOIN countries c 4SINB?countr28idA7 4SINB?countr28idA7 F## OTE% JOIN: F## OTE% JOIN: ---SQL6desc em$lo2ees7 SQL6desc em$lo2ees7 SQL6desc de$artments7 SQL6desc de$artments7 SQL6desc locations7 SQL6desc locations7 SQL6desc countries7 SQL6desc countries7

c.countr28name

4LL O4T&, JOIN de$artments d 4LL O4T&, JOIN de$artments d ON?e.manager8id=d.manager8idA ON?e.manager8id=d.manager8idA 4LL O4T&, JOIN locations l

4LL O4T&, JOIN locations l 4SINB?location8idA

4SINB?location8idA

4LL O4T&, JOIN countries c 4LL O4T&, JOIN countries c 4SINB?countr28idA7 4SINB?countr28idA7 ---(.Sel$ Joins. (.Sel$ Joins.

---Sel+ Join is a (oin o+ a Table Itsel+. Sel+ Join is a (oin o+ a Table Itsel+. is used to (oin a

is used to (oin a table to itsel+ as i+ the table were two tables9 tem$oraril2 renamingtable to itsel+ as i+ the table were two tables9 tem$oraril2 renaming at least one table in the

at least one table in the SQL statement.SQL statement.

SQL6 select e.em$lo2ee8id &M;K9 e.last8name &M;NM&9

SQL6 select e.em$lo2ee8id &M;K9 e.last8name &M;NM&9 m.em$lo2ee8id MB,K9m.em$lo2ee8id MB,K9 m.last8name MNB&, +rom em$lo2ees e (oin

m.last8name MNB&, +rom em$lo2ees e (oin em$lo2ees mem$lo2ees m ON

ON ?m.em$lo2ee8id=e.mana?m.em$lo2ee8id=e.manager8idA7ger8idA7

---).*ross Join: ).*ross Join: ---!artision ;roducts !artision ;roducts

<hen a (oin condition is In3alid or

<hen a (oin condition is In3alid or Omitted com$letel29 The result is !artisionOmitted com$letel29 The result is !artision ;roduct in which all combinition o+ all

;roduct in which all combinition o+ all rows are dis$la2ed.rows are dis$la2ed.

ll ,ows In the irst Table are (oined to all ,ows In the Second Table. ll ,ows In the irst Table are (oined to all ,ows In the Second Table. SQL6 select Crst8name9 last8name9

SQL6 select Crst8name9 last8name9 salar29 de$artment8name +rom em$lo2ees crosssalar29 de$artment8name +rom em$lo2ees cross (oin de$artments7

(oin de$artments7



(12)