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

15. Relationship View

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

17. 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.

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

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

20. 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

21. 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;

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

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

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

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

26. Joins Compared Outer Join Inner Join