1 / 26

Inner Join vs. Outer Join

Inner Join vs. Outer Join. Information Retrieval from Relational Databases. Query Languages. Relational Algebra Three main operators: Select, Project, Join Provides the conceptual basis for SQL and QBE Structured Query Language (SQL)

sonora
Download Presentation

Inner Join vs. Outer Join

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. Inner Join vs. Outer Join Information Retrieval from Relational Databases

  2. Query Languages • Relational Algebra • Three main operators: Select, Project, Join • Provides the conceptual basis for SQL and QBE • Structured Query Language (SQL) • The user enters commands according to a pre-defined syntax to retrieve desired data. • Query By Example (QBE) • The user starts with a sample of the table(s) columns and marks the fields he or she wants to include in the answer. • Defaults are available for summarizing and manipulating the data.

  3. SQL (Structured Query Language) • Each query statement follows the same structure:SELECTattribute name(s)FROMtable name(s)WHERE criteria is met;

  4. Relational Algebra • Select • includes only certain rows from a database table in its “answer”. • Project • includes only certain columns from a database table in its “answer” • Join • combines two or more database tables on the basis of one or more common attributes

  5. Join Types • Inner join(default in access) • includes only the records from both tables that have the exact same values in the fields that are joined • I.e., • Outer join • includes all records from one table, and matches those records from the other table for which values in the joined fields are equal • I.e., Left Outer Join Right Outer Join

  6. Example Tables

  7. Relationship View

  8. Query to List ALL Employees and Description of any Training Courses Taken ( Inner Join)

  9. Example Tables Note: We have 6 employees

  10. Does this This Give Us What we Need? The above based upon the default join which is the inner join and will not give us employees that have not taken training classes SQL code created by access for QBE on previous slide

  11. Using Outer Joins Note change

  12. SQL code created by access for QBE on previous slide

  13. The Revenue Cycle of M&M M&M sells its agleclaps to customers through a network of company salespeople. Each type of agleclap is bought from a particular vendor and is given an initial list price. Each salesperson services a separate group of customers and is allowed to offer them various discounts from list to induce sales. Each sale can include one or more types of agleclaps and can be paid for in any one of three ways: (1) immediately in cash, (2) on the 15th of the following month, or (3) over the course of six months. When cash is received, a cashier deposits it into a company bank account. Sales are signaled by invoices; cash receipts by remittance advices

  14. Relationship View

  15. Example Tables (Incomplete Enterprise Database)from Dunn & McCarthy (2004) working paper

  16. SQL and Relational Algebra Inner Join vs. Outer Join • Find all details of all sales and the cash receipt number and amount applied of any cash receipts related to those sales • Data we need for this example is in 2 tables • Summary sales totals have been stored in sales table • If summary figure not stored, you would need also need the sale-inventory relationship table to calculate sales totals • Note: there have been no cash receipts related to sales transaction 6 & 7.

  17. Relational Algebra Inner Join in QBE Details of all sales, related cash receipts

  18. Relational Algebra Inner Join in QBE Details of all sales, related cash receipts

  19. Default join is inner Join If you double click on the join you will see the join properties box. Item 1, the inner join is the default

  20. Inner Join Results • Sales transactions 6 & 7 are not listed since there was no cash receipts associated with them. • Does this create a potential problem?? SELECT Sale.SaleNumber, Sale.Amount, Sale.Date, Sale.CustomerNumber, [Sale-CashRecDuality].RemittanceAdviceNumber, [Sale-CashRecDuality].Applied FROM Sale INNER JOIN [Sale-CashRecDuality] ON Sale.SaleNumber = [Sale-CashRecDuality].SaleNumber;

  21. Relational Algebra Outer Join in QBE Details of all sales, related cash receipts Double-click on the join line

  22. Relational Algebra Outer Join in QBE Details of all sales, relatedcash receipts Click on appropriate join type Click OK

  23. Relational Algebra Outer Join in QBE Details of all sales, related cash receipts Note change in Join line

  24. Relational Algebra Outer Join in QBE Details of all sales, related cash receipts Result

  25. Joins Compared Outer Join Inner Join

More Related