![]() SELECT p.Name As ProductName, oh.SalesOrderNumber Replace the query with the following one, which uses outer joins to retrieve data from three tables. Run the query and review the results, which contain data for customers who have not placed any orders.SELECT c.FirstName, c.LastName, oh.SalesOrderNumber Modify the query as shown below to take advantage of the fact that it identifies non-matching rows and return only the customers who have not placed any orders. Using the LEFT (or RIGHT) keyword automatically identifies the join as an OUTER join. Run the query and review the results, which should be the same as before.Modify the query to remove the OUTER keyword, as shown here: SELECT c.FirstName, c.LastName, oh.SalesOrderNumber You can also use a FULL outer join to preserve unmatched rows from both sides of the join (all customers, including those who haven’t placed an order and all orders, including those with no matching customer), though in practice this is used less frequently. Had a RIGHT join been used, the query would have returned all records from the SalesOrderHeader table and only matching data from the Customer table (in other words, all orders including those for which there was no matching customer record). ![]() In this case, the join is between the Customer and SalesOrderHeader tables, so a LEFT join designates Customer as the outer table. This identifies which of the tables in the join is the outer table (the one from which all rows should be preserved). Customers who have registered but not placed an order are shown with a NULL order number. If a customer has placed an order, the order number is shown. Run the query and note that the results contain data for every customer. LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh Replace the existing query with the following code: SELECT c.FirstName, c.LastName, oh.SalesOrderNumber For example, suppose you want to retrieve a list of all customers and any orders they have placed, including customers who have registered but never placed an order. In cases where a row in the outer table has no corresponding rows in the related table, NULL values are returned for the related table fields.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |