Database outer join ppt

dbms join queries and different joins in sql and explain joins with example
Prof.EvanBaros Profile Pic
Prof.EvanBaros,United Kingdom,Teacher
Published Date:26-07-2017
Your Website URL(Optional)
Comment
Lecture 14 Lecture 14: Joins Lecture 14 Announcements: Two Hints • You may want to do Trigger ac)vity for project 2. •  We’ve no7ced those who do it have less trouble with project •  Seems like we’re good here J Exci7ng for us • We posted an ac7vity for you to do on your own… it may overlap heavily with a ps 3 problem... (this is not necessary but helpful). •  The solu7ons will not be posted. • Sorry the Google lecture was not recorded Last minute thing... Lecture 14 Sec-on 2 1. Nested Loop Joins 3 Lecture 14 Sec-on 2 What you will learn about in this secAon 1.  RECAP: Joins 2.  Nested Loop Join (NLJ) 3.  Block Nested Loop Join (BNLJ) 4.  Index Nested Loop Join (INLJ) 4 Lecture 14 Sec-on 2 Joins RECAP: Joins Lecture 14 Sec-on 2 Joins Joins: Example SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C A D A B C D 1 0 1 3 7 2 3 4 2 2 3 4 2 2 2 5 2 2 3 3 1 1 6 Lecture 14 Sec-on 2 Joins Joins: Example SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C A D A B C D 1 0 1 3 7 2 3 4 2 2 3 4 2 2 2 3 4 3 2 5 2 2 3 3 1 1 7 Lecture 14 Sec-on 2 Joins Joins: Example SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C A D A B C D 1 0 1 3 7 2 3 4 2 2 3 4 2 2 2 3 4 3 2 5 2 2 3 2 5 2 2 3 1 1 8 Lecture 14 Sec-on 2 Joins Joins: Example SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C A D A B C D 1 0 1 3 7 2 3 4 2 2 3 4 2 2 2 3 4 3 2 5 2 2 3 2 5 2 2 3 1 1 2 5 2 3 9 Lecture 14 Sec-on 2 Joins Joins: Example SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C A D A B C D 1 0 1 3 7 2 3 4 2 2 3 4 2 2 2 3 4 3 2 5 2 2 3 2 5 2 2 3 1 1 2 5 2 3 3 1 1 7 10 Lecture 14 Sec-on 2 Joins SemanAcally: A Subset of the Cross Product SELECT R.A,B,C,D Example: Returns all pairs of 𝐑⋈𝑺 FROM R, S tuples r∈𝑅, 𝑠∈𝑆 such that 𝑟.𝐴 = 𝑠.𝐴 WHERE R.A = S.A R S A B C D A B C A D 2 3 4 2 1 0 1 3 7 × Can we actually … 2 3 4 3 2 3 4 2 2 implement a join 2 5 2 2 2 5 2 2 3 Cross Filter by in this way? 2 5 2 3 3 1 1 Product condiAons 3 1 1 7 (r.A = s.A) 11 Lecture 14 Sec-on 2 Joins Notes • We write 𝐑⋈𝑺 to to m me ean an jjo oiin n R R a an nd d S S b by y rre etu turrn niin ng g a allll tu tup plle e p pa aiirrss w wh he erre e all shared a)ributes are equal • We write 𝐑⋈𝑺 o on n A A to to m me ean an jjo oiin n R R a an nd d S S b by y rre etu turrn niin ng g a allll tu tup plle e p pa aiirrss where a)ribute(s) A are equal • For simplicity, we’ll consider joins on two tables and with equality constraints (“equijoins”) However joins can merge 2 tables, and some algorithms do support non- equality constraints Lecture 14 Sec-on 2 NLJ Nested Loop Joins 13 Lecture 14 Sec-on 2 NLJ Notes • We are again considering “IO aware” algorithms: care about disk IO • Given a rela7on R, let: Recall that we read / write • T(R) = of tuples in R enAre pages with disk IO • P(R) = of pages in R • Note also that we omit ceilings in calcula7ons… good exercise to put back in Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Compute R⋈𝑆 𝑜𝑛 𝐴: for r in R: for s in S: if rA == sA: yield (r,s)Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Cost: Compute R⋈𝑆 𝑜𝑛 𝐴: P(R) for r in R: 1. Loop over the tuples in R for s in S: if rA == sA: Note that our IO cost is based yield (r,s) on the number of pages loaded, not the number of tuples Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Cost: Compute R⋈𝑆 𝑜𝑛 𝐴: P(R) + T(R)P(S) for r in R: 1. Loop over the tuples in R for s in S: 2. For every tuple in R, loop if rA == sA: over all the tuples in S yield (r,s) Have to read all of S from disk for every tuple in R Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Cost: Compute R⋈𝑆 𝑜𝑛 𝐴: P(R) + T(R)P(S) for r in R: 1. Loop over the tuples in R for s in S: 2. For every tuple in R, loop if rA == sA: over all the tuples in S yield (r,s) 3. Check against join condiEons Note that NLJ can handle things other than equality constraints… just check in the if statement Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Cost: Compute R⋈𝑆 𝑜𝑛 𝐴: P(R) + T(R)P(S) + OUT for r in R: 1. Loop over the tuples in R for s in S: 2. For every tuple in R, loop if rA == sA: over all the tuples in S yield (r,s) 3. Check against join condi7ons What would OUT OUT could be bigger 4. Write out (to page, then be if our join than P(R)P(S)… but when page full, to disk) condiAon is trivial usually not that bad (if TRUE)? Lecture 14 Sec-on 2 NLJ Nested Loop Join (NLJ) Cost: Compute R⋈𝑆 𝑜𝑛 𝐴: P(R) + T(R)P(S) + OUT for r in R: What if R (“outer”) and S for s in S: (“inner”) switched? if rA == sA: yield (r,s) P(S) + T(S)P(R) + OUT Outer vs. inner selecAon makes a huge difference- DBMS needs to know which relaAon is smaller

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.