1 / 11

JOINS cis 407

JOINS cis 407. Inner join Right and left outer join Full join Cross join. Cross Product. P<Pnum, Pname> PS <Pnum, Supplier> 101 nut 101 acme 102 bolt 101 wacky 102 acme

orinda
Download Presentation

JOINS cis 407

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JOINScis 407 Inner join Right and left outer join Full join Cross join

  2. Cross Product • P<Pnum, Pname> PS <Pnum, Supplier> 101 nut 101 acme 102 bolt 101 wacky 102 acme • PxPS <Pnum, Pname, Pnum, Supplier>101 nut 101 acme 101 nut 101 wacky 101 nut 102 acme 102 bolt 101 acme 102 bolt 101 wacky102 bolt 102 acme • SQL: select companyName, shipCity from customers, order • But only want result rows where part numbers match

  3. Inner Joins (typically what is meant by join) • Select *from customers c inner join [order details] od on c.customerID = od.customerID • Old methodSelect * from customers c, [order details] odwhere c.customerID = od.customerID

  4. Example #1 • Select s.companyName, p.productNamefrom products p join suppliers s on p.supplierid = s.supplieridwhere s.city = ‘city’ and p.discontinued = TRUE • ‘ON’ specifies how relations joined • ‘where’ constrains what is in result

  5. Example #2 • Select c.companyName, p.ProductNamefrom suppliers s join products p on s.supplierID = p.supplierID join [order details] odon p.productID = od.productID joinorders o on od.orderid = o.orderid joincustomers c on o.customerID = c.customerIDwhere c.city = s.city • Generate an equivalent English sentence

  6. OUTER JOIN • A left or right outerjoin is something like a combination of a join and a crossproduct. • (pg 90) a left outer join includes all the information from the table on the left. • There many be a lot of nulls in the result.

  7. Example • Select companyName, orderIDfrom company c inner join order o on c.customer = o.customer • Only customers that have ordered products will be included in the result. • Select companyName, orderIDfrom company c left outer join order o on c.customer = o.customer • Includes all customers will null orderIDs in nothing ordered. • Outer joins useful in finding orphan records

  8. Null does not match Null • A value of null does not join to a value of null. Why? • x = null will not work • X is null will work • Select c.customerid, companynamefrom customer c left outerjoin orders oon c.customerid = o.customeridwhere o.customerid is null

  9. Other joins • Full outerjoin combines left and right outerjoin • Cross join is cross product. • May be useful in populating test database

  10. Other examples • Select * from customers c, orders owhere c.customerid = o.customerid • Select * from customers c, orders owhere c.customerid != o.customerid

  11. UNION • Select companyNamefrom customerswhere city = ‘NY’unionselect companyNamefrom Customerswhere city = ‘Pocky’ • Union selects must have the same number of columns. Must be the same domain for results to make much sense. (pg 105) • See example pg 106

More Related