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 |