Đang chuẩn bị liên kết để tải về tài liệu:
The Language of SQL- P28

The Language of SQL- P28:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | The Outer Join 121 The line between the Customers and Orders tables is on the CustomerID columns because the CustomerID is the common link between these two tables. Similarly the line between the Orders and Refunds tables is on the OrderID columns because the OrderID is the common link between these two tables. In other words the Orders table is related to the Customers table by customer. There must be a customer for an order to exist. The Refunds table is related to the Orders table by the order. There must be an order before a refund is issued. Note that the Refunds table is not directly related to the Customers table. However by joining all three tables together we will be able to determine which customer a given refund was for. Let s now examine the contents of each table. The Customers table has these values CustomerID FirstName LastName 1 William Smith 2 Natalie Lopez 3 Brenda Harper 4 Adam Petrie The Orders table has this data OrderID CustomerID OrderDate OrderAmount 1 1 2009-09-01 10.00 2 2 2009-09-02 12.50 3 2 2009-10-03 18.00 4 3 2009-09-15 20.00 The Refunds table contains this data RefundID OrderID RefundDate RefundAmount 1 1 2009-09-02 5.00 2 3 2009-10-12 18.00 122 Chapter 12 Combining Tables with an Outer Join Notice that three out of the four customers have placed orders. Likewise only two refunds have been issued for the four orders placed. Left Joins Let s now create a SELECT statement that joins all three tables together using a LEFT JOIN SELECT Customers.FirstName AS First Name Customers.LastName AS Last Name Orders.OrderDate AS Order Date Orders.OrderAmount AS Order Amt Refunds.RefundDate AS Refund Date Refunds.RefundAmount AS Refund Amt FROM Customers LEFT JOIN Orders ON Customers.CustomerID Orders.CustomerID LEFT JOIN Refunds ON Orders.OrderID Refunds.OrderID ORDER BY Customers.CustomerID Orders.OrderID RefundID The resulting data looks like First Name Last Name Order Date Order Amt Refund Date Refund Amt William Smith 2009-09-01 10.00 .