![]() 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. Run the modified query and note that it returns data from all three tables.Īn outer join is used to retrieve all rows from one table, and any corresponding rows from a related table.ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID Replace the query with the following code, which retrieves sales order data from the SalesLT.SalesOrderHeader, SalesLT.SalesOrderDetail, and SalesLT.Product tables: SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name AS ProductName, od.OrderQty, od.UnitPrice, od.LineTotal The use of table aliases can greatly simplify a query, particularly when multiple joins must be used. Run the modified query and confirm that it returns the same results as before. ON p.ProductCategoryID = c.ProductCategoryID Modify the query to assign aliases to the tables in the JOIN clause, as shown here: SELECT p.Name AS ProductName, c.Name AS Category INNER joins are the default kind of join. The results should be the same as before. Modify the query as follows to remove the INNER keyword, and re-run it. Because the query uses an INNER join, any products that do not have corresponding categories, and any categories that contain no products are omitted from the results. Use the ⏵Run button to run the query, and after a few seconds, review the results, which include the ProductName from the products table and the corresponding Category from the product category table. In the query editor, enter the following code: SELECT AS ProductName, AS Category In the new SQLQuery_… pane, use the Connect button to connect the query to the AdventureWorks saved connection.Start Azure Data Studio, and create a new query (you can do this from the File menu or on the welcome page).You can find the relevant product category record for a product based on its ProductCategoryID field which is a foreign-key in the product table that matches a primary key in the product category table. For example, suppose you need to retrieve data about a product and its category from the SalesLT.Product and SalesLT.ProductCategory tables. Use inner joinsĪn inner join is used to find related data in two tables. Note: If you’re familiar with the standard AdventureWorks sample database, you may notice that in this lab we are using a simplified version that makes it easier to focus on learning Transact-SQL syntax. For your reference, the following diagram shows the tables in the database (you may need to resize the pane to see them clearly). In this lab, you’ll use the Transact-SQL SELECT statement to query multiple tables in the adventureworks database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |